[
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: 10.2.1.6
> 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.
