Index not being used on selecting non-indexed column

[parult_2k]

Hi,

I have following two queries from one of our application, table Nes_address has a index on State_Id column. When I check the explain plan of first query it shows a full table scan on this table. Where the second one uses the index on State_id column. Question is WHY?

(1)
select listing_id
FROM rating.nes_address ad
where ad.state_id = 5

(2)
select ad.state_id
FROM rating.nes_address ad
where ad.state_id = 5

WHY this query does not use the index when I have a non-indexed column in the SELECT list? I have already re-analyzed this index.

What could be the possible reason of this behavior?
Please help.

Thanks,
Parul

[skhanal]

How big is your nes_address table?. For the first query the optimizer has an option of full scan or index scan on state_id then bookmark lookup on table to get listing_id. If the optimizer thinks the second option is more expensive then it will go for full scan.

In a simple scenario, assuming your table and index occupying a block each, the first option neends one read where as the second option needs two reads.

So the table size may be the deciding factor.