I was refering to the fact that i had multiple tables in my query and they all have PKs. I should have been clearer though and say that I have disabled a PK on one of the tables. I can't modify the SQL in any way because it is generated by a reporting tool (Cognos) it won't add hints or +0 etc. What I was mostly wondering is whether this situation (PK createing a performance degradation) is a symptom of some other problem which I need to look at. My first suspect was statistics, but I ruled that out by analyzing all tables involved. thank you Gene Gurevich Oracle Engineering 224-405-4079 Nuno Souto <dbvision@xxxxxxx et.au> To Sent by: oracle-l@xxxxxxxxxxxxx oracle-l-bounce@f cc reelists.org Subject Re: Primary Key seems to be harmful 03/08/2006 06:39 for performance PM Please respond to dbvision@xxxxxxxx t.au Quoting genegurevich@xxxxxxxxxxxxxxxxxxxxx: > 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 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l