Unique index+null columns

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Wed, 28 Feb 2001 15:54:05 -0800
Message-ID: <F001.002C034F.20010228155524@fatcity.com>

If we have :

1- Table T1 (c1 char, c2 char) &
2- Unique index I1 on T1 (c1,c2)
3- Insert into T1 values (null,'A');

Oracle stores the above values in the index.

4- If we try to insert the same values (null,'A')

   we get an error that duplicates were detected.

The question is if Oracle can detect duplicates and enforce uniqueness using the index and the above values,
why can't it use the index if need to query for the above values:

 select /*+ index(T1 I1) */ c2 from T1 where c1 is null and c2 = 'A';

I know all the confusion about nulls in the indexes but was looking for a quick answer before i have to dump the indexes and spend hours on this.

Regards

Waleed

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Feb 28 2001 - 17:54:05 CST