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

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

Re: Primary Key seems to be harmful for performance

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Thu, 09 Mar 2006 19:40:52 -0500
Message-Id: <1141951252l.4291l.1l@medo.noip.com>

On 03/09/2006 09:12:05 AM, genegurevich_at_discoverfinancial.com wrote:
> 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.

Gene, execution plan would be helpful. What you wrote sounds like a full table scan, murdered by using index. That usually happens if parameters with the goal of forcing index use and emulating RBO are in effect (OPTIMIZER_INDEX_CACHING and cost adjust). What happens is that Oracle reads every index key and then reads the corresponding table block, performing, in effect, several times as many I/O requests as without using an index. Two other things you can do are to make sure that users that use Cognos have optimizer goal set to ALL_ROWS and you can fake statistics and make the PK index look bad to CBO (look at my home page, I describe the way of doing that there). Third option is to try throwing Cognos out and then getting something that always generates an optimal execution plan, like Business Objects or Crystal Reports (duck).

-- 
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 09 2006 - 18:40:52 CST

Original text of this message

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