RE: Index help

If we go back to the original post with the Query then there are 3 columns
referenced in the WHERE clause.  It may still be possible to improve the
query by building an index on those other columns.  So far all the
discussion has been on IDA3A5 which because it is badly skewed is not a good
overall index choice.

HTH -- Mark D Powell --

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Daniel Fink
Sent: Monday, July 26, 2004 11:08 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Index help

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 He explains this issue very well.

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:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: