Re: Primary Key seems to be harmful for performance

Stephen,

Thanks for your reply. I have analyze the tables (including the indices)
manually before executing the SQL.
Normally we load the data into an exchange table, rebuild the PK and then
exchange with the reporting table
So effectively PK is rebuilt daily

thank you

Gene Gurevich
Oracle Engineering
224-405-4079


                                                                           
             "Stephen Andert"                                              
             <andert@xxxxxxxxx                                             
             >                                                          To 
                                       genegurevich@xxxxxxxxxxxxxxxxxxxxx  
             03/08/2006 05:48                                           cc 
             PM                        oracle-l@xxxxxxxxxxxxx              
                                                                   Subject 
                                       Re: Primary Key seems to be harmful 
                                       for performance                     
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           




When was the last time the PK has been rebuilt and how much data has been
added/deleted/changed since then?

Do you have statistics on the index and how recently gathered (in
comparison to rate of change)?

Stephen

On 3/8/06, genegurevich@xxxxxxxxxxxxxxxxxxxxx <
genegurevich@xxxxxxxxxxxxxxxxxxxxx> wrote:
  Everybody:

  I have been working to tune a SQL. It has been running OK until last
  week.
  Since that time its performance has degraded significantly.  I can't find

  any changes made to the database to explain that. When I ran it a day
  ago,
  the query completed
  in over 2 hours. After looking at the results of the tkprof, I decided to
  disable one of the primary keys. After that the
  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?

  thank you in advance

  Gene Gurevich



  --
  http://www.freelists.org/webpage/oracle-l





--
Stephen Andert
http://spaces.msn.com/andert-news/


--
http://www.freelists.org/webpage/oracle-l

Other related posts: