Re: Primary key vs non unique index performance

However,

If 8.1, you can declare a primary key to be deferrable, which means it is also supported by a non-unique index.

You can then disable the primary key, do the data load, enable the primary key in a 'novalidate' state to ensure that no further illegal entries appear, but with a 'rely' state to make the optimiser treat it like a primary key.

You can then validate the pk constraint, sending errors to a table for further investigation without locking the base table.

This may offer a cost-effective route to solving the problem properly.

(Chapter 18 - Integrity)

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Sybrand Bakker wrote in message ...


>First of all (and as you didn't make the decision I can and will say it):

>allowing duplicates for a primary key is just plain stupid.

>As the primary key has been dropped the problem will likely aggravate over

>time, and you will end up with a mess (I'm currently working at a customer,

>where there are no primary keys at all. You don't want to know what is

>happening)

>Secondly: You never should load your data *directly* into  a production

>table as you will end up with issues like this: someone who knows nothing

>about databases making a stupid decision.

>Thirdly: The optimizer is heavily biased in favor of using primary and

>unique indexes. If that index is not unique anymore, you can potentially

 run


>into situations where is it not anymore *at all*. Just imagine what is

 going


>to happen.

>So evidently that management decision is the 'smartest' decision in the

 21st


>century in your company.

>The problem should be resolved. Period.

>

>Regards,

>

>Sybrand Bakker, Oracle DBA

>