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: Index help

Re: Index help

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 26 Jul 2004 09:08:03 -0600
Message-id: <41051E53.6040405@sun.com>


If the value of :1 = 46847 a full table scan makes sense (96% of the rows meet the predicate condition). If the value is not 46847, then an index lookup is probably faster. This is really a catch-22. Bind variables are helping your parse times, but *may* be hurting your execution time (hard to tell without knowing how many executions use 46847 and how many do not). Without a histogram on the column, Oracle will assume an even distribution, which is incorrect.

You are trapped between a rock and a hard place. If you use hard-coded values instead of binds and have a histogram on the column, the CBO should pick the proper execution plan each time. This also means that you will have unique versions of the statement in your shared pool. Sorry there is not a simple, quick answer to this one.

I suggest reading Wolfgang's paper "Fallacies of the Cost-Based Optimizer" at www.centrexcc.com. He explains this issue very well.

Regards,
Daniel Fink

M.Godlewski wrote:
> Data distribution.
>
> IDA3A5 count(IDA3A5)
> 4104 44
> 4107 156
> 4110 1
> 4111 1
> 4137 1
> 46847 4905



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Mon Jul 26 2004 - 10:08:13 CDT

Original text of this message

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