Re: Primary Key seems to be harmful for performance
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 9 Mar 2006 09:23:31 -0600
Kevin,
What is a stored outline (I'm probably showing my ignorance here)?
thank you
Gene Gurevich
Oracle Engineering
224-405-4079
"Kevin Lidh"
<kevin.lidh@gmail
.com> To
Sent by: dbvision@xxxxxxxxxxxx
oracle-l-bounce@f cc
reelists.org oracle-l@xxxxxxxxxxxxx
Subject
Re: Primary Key seems to be harmful
03/09/2006 08:33 for performance
AM
Please respond to
kevin.lidh@gmail.
com
We've used stored outlines in situations where we couldn't modify SQL
because it was owned by a third party who threatened to not support the
product if any "anauthorized" changes were made and changing statistics or
dropping/adding indexes wasn't an option. Fortunately it's not a
wide-spread problem (11 SQL out of tens of thousands). Not the ideal
solution but it works for us.
On 3/8/06, Nuno Souto <dbvision@xxxxxxxxxxxx> wrote:
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
checks?
--
Cheers
Nuno Souto
from sunny Sydney
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
