Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Index not being used
Hi,
Try using a hint in your query.
eg select /*+ index(table_alias index_name) */ col_a ,
col_b
from table_name table_alias
where col_a = 'XYZ';
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 - 08:58:24 CST