Index not hitting while querying

indexindex-tuningoracleperformance

I have a query on a table tab and this table has a column a:

select * from tab where a = :a;

but the index is not hitting. Please let me know what could be possible reason (It was asked during an interview).

Best Answer

  • index on a is unusable
  • index on a is invisible and optimizer_use_invisible_indexes is FALSE (which is the default value)
  • implicit type conversion: type of :a is different from type of a
  • simply the cost of the plan that uses full table scan is lower than the cost of the plan that uses the index
  • NLS settings were changed in the session (nls_sort, nls_comp) and the original index can not be used anymore with those settings