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
> 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: