Re: Primary Key seems to be harmful for performance

  • From: "Kevin Lidh" <kevin.lidh@xxxxxxxxx>
  • To: dbvision@xxxxxxxxxxxx
  • Date: Thu, 9 Mar 2006 07:33:04 -0700

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

Other related posts: