RE: index doesn't contain duplicates

From: Josh Collier <Josh.Collier_at_banfield.net>
Date: Tue, 9 Jun 2009 13:25:01 -0700
Message-ID: <C5671700C5F4EA47B08AB9E6DAE9539B357855CB3D_at_M1EXCHANGE01.mmi.local>

Hi,

it is true that when the query is used to retrieve the rows, only half of them return. The setup did indeed fail to protect against dups in the pk. I think they are part and parcle of the same problem. lets just focus on one part of it.

1. I have a column with a value of 'A' that appears two times
2. i have a non-unique index on that column
3. when I run a query that says "select col from table where col='A'" it uses an index fast full scan that only retrieves 1 rows
4. when i run a query that says "select /*+ full(table) */ from table where col = 'A' " it uses a full tablescan and retrieves 2 rows

i'll cook up a test case.

---

From: mathiasmag_at_gmail.com [mathiasmag_at_gmail.com] On Behalf Of Mathias Magnusson [mathias.magnusson_at_gmail.com] Sent: Tuesday, June 09, 2009 1:01 PM
To: nigel.cl.thomas_at_googlemail.com
Cc: Oracle-L Freelists; Josh Collier
Subject: Re: index doesn't contain duplicates

I think we need some clarifications from Josh. The way I read the first message was that when the query uses the index only half the rows are found. Later the thread seem to move towards the setup not protecting against duplicate values in the column used as PK. Those two things would at least be different and for the issue at hand probably also contradicting.

For Josh, can you reproduce the issue by copying the data from the current table into a new one (to make sure we're not removing the issue by operating on the original table)?

Mathias

On Tue, Jun 9, 2009 at 9:50 PM, Nigel Thomas <nigel.cl.thomas_at_googlemail.com<mailto:nigel.cl.thomas_at_googlemail.com>> wrote: Just for fun, I tried to duplicate this in Oracle XE 10.2.0.1 and couldn't. OTOH, I only tried inserting 4 rows.

When I inserted the duplicate row, I got (as you would expect):

SQL> select uniqueness from user_indexes where index_name='TNU1'   2 /

UNIQUENES

---

NONUNIQUE SQL> insert into testnonunique values ('DD'); insert into testnonunique values ('DD')
*
ERROR at line 1:
ORA-00001: unique constraint (TESTUSER.TNU_PK) violated

(TNU_PK was created based on non-unique index TNU1)

Thinking on from Mark Powell's suggestions - how was the CTAS done?

Cheers Nigel

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 09 2009 - 15:25:01 CDT