From Waleed.Khedr@FMR.COM Wed, 28 Feb 2001 15:54:05 -0800 From: "Khedr, Waleed" Date: Wed, 28 Feb 2001 15:54:05 -0800 Subject: Unique index+null columns Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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@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@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).