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.
