-
Index not being used on selecting non-indexed column
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|