| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Index not being used
Michel Gauthier wrote:
> Hi
> The use of optimizer hints will probably help (as posted earlier). However,
> it does not explain why. As far as I know, when the optimizer is choosing
> between the use how an index and a full table scan, it relies on how many
> different values the index has. Considering the information you provided,
> there might have less than 20 different values of CLT_TYPE, which means an
> average of 25000 rows per CLT_TYPE. So, if the optimizer does not
> considerate the actual value of CLT_TYPE (or it doesn't know it at all), it
> is normal it chooses a full table scan.
> I'm not enough familiar with the improvement of the Oracle optimizer along
> with the different releases. On the release we use (7.2.3), I'd guess it'd
> choose the full table scan! Maybe with 7.3 (or 8) the optimizer is more
> intelligent...
>
> learmog_at_my-dejanews.com wrote in message
> <72uc56$q83$1_at_nnrp1.dejanews.com>...
> >Problem :
> >Selecting a column from a table, driving the query by a non-unique indexed
> >column as follows :
> >
> >select CLIENT_ID
> >from CLIENTS
> >where CLT_TYPE= 'HAC'
> >
> >Table clients (and associated indexes) has been fully analyzed.
> >
> >Explain plan reports that a full scan will be performed on the table when I
> >would expect and index range scan (using the non-unique index on CLT_TYPE)
> to
> >be performed followed by rowid access into clients.
> >
> >There is about half a million rows in total in CLIENTS and only nine with
> >CLT_TYPE = 'HAC'.
> >
> >When I delete the stats for the index, explain plan reports that it DOES
> use
> >the index range scan followed by rowid table access, and the cost reported
> is
> >less than prior to deleting the stats.
> >
> >Could somebody please tell me why the hell it isn't using the index when
> the
> >stats are there ?
The "why" answer is correct, that column is skewed therefor misleading the
optimizer.
Starting with 7.3 you can create a histogram on such columns with the ANALYZE
TABLE statement.
It is recommended to do this on frequently used (in WHERE clauses)
highly-skewed columns.
Saar.
--
__ _ _ __ _ _ _ _ ___ ______________________________ (( /\\ /\\ ||) |\V/| /\\ /\\ >/ Principal Performance Engineer_))//-\\//-\\||\ |||||//-\\\\//<_ Oracle Corporation Digital SBU
////////////////// Drop x's in email (spam) //////////////////////Received on Wed Nov 18 1998 - 15:29:43 CST
 |
 |