Toad World® Forums

Table not utilizing the index created even after gathering stats

Hi All

I have a table A which is having indexes as well, however the index is not being utilized and going for full table scan. Whereas when I created its backup table A_BK, the index is being utilized thus going for index scan. My query is why my original online table A is not utilizing the index?

Note: I have gathered table stats, index stats also I tried rebuilding the index online. however still my online table A not utilizing the index.

Sometimes Oracle decides that it would be faster to do a full table scan. Sometimes it can’t because or something in your query. You’ll need to provide some details about your query and table and index if you want a detailed answer, but if you google “why is my index not being used in Oracle”, I’m sure you’ll get a ton of possible explanations.

Thanks a lot John for the quick reply. So full table scan if faster…cost should be lesser? I will check and get back to you with more input details.

For now: db version is 11.2.0.4

Environment: AIX

Think about it this way.

If Oracle happens to think (based on the stats gathered etc) that your query will return “most” of the rows from the table (how much exactly is “most” is again a decision made by the optimizer), it will resort to a full table scan because its faster to just get all the rows and reject the ones that are not required than to go through the index and find out the ones that are required. That way it can save the additional step of accessing the index which in itself is a data structure that needs to be traversed.

Regards,

Arijit

Ok great thanks for the response. Got you mate!