db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Derby, SQL 2003, UNIQUE constraints, unique indices and NULL (long)
Date Wed, 02 Nov 2005 17:06:21 GMT
Bernt, could you explain below a little more.  I don't understand
what it means for the columns with null values are not part of
the index?  Do you mean the rows with columns with null values?

Bernt M. Johnsen wrote:

> Appendix A: A note on implementation
> ================================================== 
> 
> For those wondering how unique indices with multiple null values may
> be implemented, it can be done pretty simple: The columns with null
> values are not part of the index. This will if course affect the
> execution plan.
> 
> Example:
> 
> CREATE TABLE t (i INTEGER, a INTEGER NOT NULL, b INTEGER);
> CREATE UNIQUE INDEX idx1 ON t(i);
> CREATE UNIQUE INDEX idx2 ON t(a, b);
> 
> SELECT i FROM t WHERE i=1;       -- Use index idx1
> SELECT i FROM t WHERE i IS NULL; -- Do a table scan
> SELECT a FROM t WHERE a=5;       -- Do a table scan
> SELECT a FROM t WHERE a=5 AND b IS NOT NULL;
>                                  -- Use index idx2
> 
> There is of course are other ways of implementing unique indices with
> multiple null values.


Mime
View raw message