Re: Primary Key seems to be harmful for performance

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_at_gmail                                             
             .com>                                                      To 
             Sent by:                  dbvision_at_iinet.net.au               
             oracle-l-bounce_at_f                                          cc 
             reelists.org              oracle-l_at_freelists.org              
                                                                   Subject 
                                       Re: Primary Key seems to be harmful 
             03/09/2006 08:33          for performance                     
             AM                                                            
                                                                           
                                                                           
             Please respond to                                             
             kevin.lidh_at_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