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 ?
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Wed Nov 18 1998 - 13:33:35 CST
