RE: index doesn't contain duplicates

From: Josh Collier <>
Date: Tue, 9 Jun 2009 11:11:32 -0700
Message-ID: <C5671700C5F4EA47B08AB9E6DAE9539B35782B34D9_at_M1EXCHANGE01.mmi.local>

Version 9208

The way the table was created was this..

Table was created with a CTAS
Non-unique index was built
Primary key was built on a column, specifying index created in step2. Enable novalidate. The data in question was inserted after the primary was in place.

The plan that produces the incorrect results is a fast full scan on the non-unique index The plan that produces the correct result is a full tablescan on the table.


From: [] On Behalf Of Powell, Mark D Sent: Tuesday, June 09, 2009 10:22 AM
Subject: RE: index doesn't contain duplicates

Yes, the full Oracle version would be nice plus the select with plan the produces the proper results and one that does not.

Things you can try:

Run analyze index validate
Run analyze table validate cascade (depending on results from above) Drop and re-create index

  • Mark D Powell -- Phone (313) 592-5148

From: [] On Behalf Of Josh Collier Sent: Tuesday, June 09, 2009 12:11 PM
Subject: index doesn't contain duplicates All,

I have an table that has duplicate values for a column. I have a non-unique index on this column. When I run a query that uses a fast full scan to retrieve the rows, it only retrieves one of the two rows. When I force a full tablescan both of the rows come back. I have used all my sql tricks to try my best to determine if the value has trailining or leading non-printable characters and it doesn't appear so. I have also moved the values into a varchar 11 field in another table, the value is 11 chars long. Both of the duplicate rows where accepted. Does anyone know of a bug or a reason why both of the rows wouldn't appear in the index?

Thanks for your time,
Josh C.

Received on Tue Jun 09 2009 - 13:11:32 CDT

Original text of this message