INDEX Problem

[puneet]

Hi

I know it may sound weird but this is the SQl running in my application

SELECT ecrefnum, ecreqnum, ectype, ecmenbr, ecfield1
FROM eceout
WHERE ecstat = 1

and I have an index on ecstat and this table has 72000 rows but still when I do explain plan the TABLE_ACCESS is full

ANy ideas

[ramk_vv]

Hi,

The following reasons come to my mind :

a) Assuming that you are using Cost Based Optimizer, the statistics on the table and the index may not be the latest ones.Try analyzing them and then observe.

b) Assuming that your statistics are latest, the optimizer feels the cost involved for a full table scan is lesser than the cost involved for an index scan.This may be because there are many rows that satisfy the condition, so a full table scan is cheaper than an index scan.

c) The index key what you are using may not be the first field of the index, if it is a composite one.

d) If this column does not have many unique values, it is advisable to go in for a bitmap index instead of a B-tree.

e) Try rebuilding / analyzing the index or using a hint and see.

HTH.