Re: Primary Key seems to be harmful for performance

  • From: Nuno Souto <dbvision@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 9 Mar 2006 08:39:53 +0800

Quoting genegurevich@xxxxxxxxxxxxxxxxxxxxx:

> in over 2 hours. After looking at the results of the tkprof, I decided to
> disable one of the primary keys. After that the

"one of the primary keys"?  That would be of one of the tables?

> same query completed in 10 seconds (!). I have reenabled the PK and query
> ran for much longer (I cancelled it after 10 minues).  I have disabled the
> PK and again the SQL finished in 10 sec.
> So it looks like PK is a problem performace-wise. How do I fix it? I don't
> want to drop that PK; I think that it is needed to make
> sure there is no duplicates and from the DM perspective as well. I was
> wondering whether this is a symptom of some problem with the statistics?
> Does anyone have any suggestions?

Without any specific data on execution plans, all I can reasonably
suggest is that you use a function in the query to disable use of the 
pk index by the optimizer.  Something like the traditional use of a 
nvl() around the leading column(s) of the index, or concatenating a 
null to a string or adding 0 (zero) to a number column.  

All of them classical ways of making the optimizer ignore a particular 
index.  You could of course use a hint as well.
That'd be a safer propposition than dropping a PK and losing uniqueness

Nuno Souto
from sunny Sydney

Other related posts: