db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Anurag Shekhar (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
Date Thu, 12 Jul 2007 17:33:04 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12512186

Anurag Shekhar commented on DERBY-2212:

One of the approach suggested in the previous discussion was to
eliminate nulls from the index and use full table scan while
performing a search on null index (or part of it). This approach will
be much cleaner (will not required null to be treated as special
values in B+ tree searching for inserts), but it will result in two cases

1. When where clause has is null criterion for a index field
2. When search is performed on part of the multi field index. 
        Suppose there is an index on field i,j,k and a query is perfomed on
          i = val and j = val, then its possible perform index scan  for i and j    
          ignoring third part and then performing a leaf scan after finding 
          the first leaf node with (i,j).
         But once we eliminate null value from index it will be required to 
         perform a full table scan as there may be one or more null k 
         corresponding  to the search criterion.

This I think will cause a major performance drop and I think it will 
be better to go for an implementation where duplicate null values 
are allowed in the index.

> Add "Unique where not null" to create index
> -------------------------------------------
>                 Key: DERBY-2212
>                 URL: https://issues.apache.org/jira/browse/DERBY-2212
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions:
>            Reporter: Oleksandr Alesinskyy
> Derby prohibits creation of unique constraints on nullable colums (as well if only some
columns in the constraint list are nullable) and treat nulls in unique indexes as normal values
(i.e. only one row with null values in indexed columns may be inserted into the table). This
bahavior is very restrictive, does not completely comply with SQL standards (both letter and
intent) as well as with business needs and intending meaning of NULL values (2 null values
are not considered as equal, this comparision shall return NULL, and for selection criteria
boolean null is treated as FALSE).
> This behavior, as far as I can see, is modelled after DB2 (and differs from behavior
of most other major databases, like SyBase, Oracle, etc.).
> But even DB2 provide some means to alleviate these restrictions, namely "UNIQUE WHERE
NOT NULL" clause for CREATE INDEX statement.
> It will be very good if such "UNIQUE WHERE NOT NULL" clause will be introduced in Derby.
> Regards,
> Oleksandr Alesinskyy

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message