Index not being used

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 - 05:47:53 CST