db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Derby, SQL 2003, UNIQUE constraints, unique indices and NULL (long)
Date Wed, 02 Nov 2005 22:55:24 GMT
>>>>>>>>>>>> Mike Matrigali wrote (2005-11-02 09:06:21):
> 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?

I mean columns with null values in nullable columns used in a unique
index. In the example below

(NULL, 1, 1) would not be in idx1
(1, 1, NULL) would not be in idx2
(NULL, 1, NULL) would not be in any of them

> 
> 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.
> 

-- 
Bernt Marius Johnsen, Database Technology Group, 
Sun Microsystems, Trondheim, Norway

Mime
View raw message