Re: Primary Key seems to be harmful for performance

From: <genegurevich_at_discoverfinancial.com>
Date: Thu, 9 Mar 2006 08:08:31 -0600
Message-ID: <OF33D91088.D7462417-ON8625712C.004D8FA4-8625712C.004DAE0C@discoverfinancial.com>

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_at_gmail.com                                             


             >                                                          To 

                                       genegurevich_at_discoverfinancial.com  
             03/08/2006 05:48                                           cc 
             PM                        oracle-l_at_freelists.org              
                                                                   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_at_discoverfinancial.com < genegurevich_at_discoverfinancial.com> 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

Received on Thu Mar 09 2006 - 08:08:31 CST