> CREATE TABLE t2 (i INTEGER, j INTEGER NOT NULL);
> CREATE INDEX idx3 on t2(i);
> CREATE UNIQUE INDEX idx4 on t2(j);
>
> SELECT i FROM t2; -- use index idx3 (covers query)
> SELECT j FROM t2; -- Do a table scan
Shouldn't the last SELECT use idx4 since the index is UNIQUE *and* j
is NOT NULL?
--
Bernt Marius Johnsen, Database Technology Group,
Yes, you're right. I should have said:
CREATE TABLE t2 (i INTEGER, j INTEGER);
CREATE INDEX idx3 on t2(i);
CREATE UNIQUE INDEX idx4 on t2(j);
SELECT i FROM t2; -- use index idx3 (covers query)
SELECT j FROM t2; -- Do a table scan
I've done a little more thinking about this problem. I suspect that
the store code uses the results of the same compare() method
invocation to determine where to insert a row into an index and also
to determine whether the row is a duplicate in a unique index. If
Derby is to allow multiple nulls in a unique index, the store must
use the two different null semantics for the two cases. For the
question of where to insert the row, nulls must be considered to be
equal to each other, and for the question of whether they are
duplicates, they must be considered not equal to each other.
If I'm right, it should be easy to change Derby to allow multiple
nulls in unique indexes. We still haven't heard from any of the store
experts, though. Mike?
- Jeff Lichtman
[EMAIL PROTECTED]
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
