Re: Primary key vs non unique index performance

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

"Rob Hale" <robbidog98_at_home.com> wrote in message news:3A7249A4.1ED61C9A_at_home.com...
> Due to a programming error during a bulk load, there are certain tables
> in the
> database I'm working on which have duplicate entries loaded. When the
> bulk load
> ends, duplicates sometimes exist in the table and the primary key index
> refuses to
> rebuild making the table unusable.
>
> A management decision has been made to replace the primary key index
> with a non
> unique index that uses the same columns. According to the person who
> made this
> decision, the cost in performance between a select using the primary key
> and a select
> using the non-unique index will be negligible. The number of duplicates
> inserted in the
> table is actually quite small and according to management the data error
> caused by
> the duplicates is acceptable. But, I have trouble justifying the
> existence of a table that
> does not have a primary key. Typical selects on this table will return
> less then 5% of
> the tables data.
>
> Does anyone have statistics or reports that demonstrate the difference
> in
> performance between a primary key index and a non unique index? Is this
> a cause
> for serious concern for database performance?
Received on Fri Jan 26 2001 - 23:20:33 CST