Monday, 10 March 2014

How to make sure Optimizer chooses NUSI over Full Table Scan (FTS)?

Answer:

A optimizer would prefer FTS over NUSI, when there are no Statistics defined on NUSI columns.
It would prefer scanning for full table instead of going for Subtables in NUSI  since optimized does not have information about subtables of NUSI
It is always suggested to collect statistics whenever NUSI columns are defined on the table.
Verify whether index is being used by checking in Explain plan.

No comments:

Post a Comment