I have a table whose size is 2.3 GB and there are two indexes on it. One index is based on a Date column whose size is 900 MB, and the Other index consists of 5 columns including the date column, and the size is almost 2GB. But when i query the table using the Date column, it is doing a range scan on the second index which is almost the same size as the table. My question is, why is it not using the first index ??? What steps should i take so that it uses the First index without passing hints.

Does the query need to go to the table at all? Or are all of the columns used present in the 5-column index? If Oracle does not need to lookup the table, the longer index will be preferred.

Are there any other WHERE clauses? The longer index may be preferred because it is performing additional filtering.

It also might be possible that Oracle is ignoring the smaller index because it is a complete subset of the longer index. The reasoning would be that there is no point using both indexes for different queries - that would be an inefficient use of space in the buffer cache. By making all queries use only the superset index, all queries would get a better cache-hit ratio. Note that this is just an untested theory.

You could use a hint to force using the index, but you may get worse performance on this query, or possibly worse performance on the database overall.Ross Leishman

I got the point. The longer index contains the columns which are in the select query. that is the reason why it was not using the single coulmn index as it was getting all the required data from the index itself.