Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Primary Key seems to be harmful for performance

Re: Primary Key seems to be harmful for performance

From: Kevin Lidh <kevin.lidh_at_gmail.com>
Date: Thu, 9 Mar 2006 07:33:04 -0700
Message-ID: <cb4807f0603090633r52d3dc23oe26ac9f3a9585b6c@mail.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_at_iinet.net.au> wrote:
>
> Quoting genegurevich_at_discoverfinancial.com:
>
> > 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 09 2006 - 08:33:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US