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

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

Primary Key seems to be harmful for performance

From: <genegurevich_at_discoverfinancial.com>
Date: Wed, 8 Mar 2006 17:12:23 -0600
Message-ID: <OF90496E03.0376058A-ON8625712B.007F0760-8625712B.007F7918@discoverfinancial.com>


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
Received on Wed Mar 08 2006 - 17:12:23 CST

Original text of this message

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