Re: index doesn't contain duplicates

From: Mathias Magnusson <mathias.magnusson_at_gmail.com>
Date: Tue, 9 Jun 2009 22:01:14 +0200
Message-ID: <8580d4110906091301i405c6c25k1a368dc1e7074626_at_mail.gmail.com>

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
> 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:01:14 CDT