RE: index doesn't contain duplicates
- From: Josh Collier <Josh.Collier@xxxxxxxxxxxx>
- To: Mathias Magnusson <mathias.magnusson@xxxxxxxxx>, "nigel.cl.thomas@xxxxxxxxxxxxxx" <nigel.cl.thomas@xxxxxxxxxxxxxx>
- Date: Tue, 9 Jun 2009 13:25:01 -0700
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@xxxxxxxxx [mathiasmag@xxxxxxxxx] On Behalf Of Mathias
Magnusson [mathias.magnusson@xxxxxxxxx]
Sent: Tuesday, June 09, 2009 1:01 PM
To: nigel.cl.thomas@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxx<mailto:nigel.cl.thomas@xxxxxxxxxxxxxx>> 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
Other related posts:
