Today a person from development team told me that created an index on the Test environment and it is no used in his query. He does not understand that is not used. Actually one of the axioms of Oracle and in general for any RDBMS such as Sybase, MySQL, SQL Server, is “there is an index on the columns of selectivity does not imply that use it”. Some of the reasons may be;
- A query asking for all rows in the table. Traditionally in Oracle when a query is performed that returns more 20% of the rows, do a FULL SCAN,Oracle supposed to be less expensive FTS than access via index.
- For parameters, there is parameters like OPTIMIZER_INDEX_COST_ADJ that can force more using indexes Vs full table scan or more FTS Vs Index.
- There are other better indexes, or at least it may seem that you have more current statistics than index we hope use.
If it don’t take the index in a natural way, we would have to use a hint. If it gets naturally, maybe the next day may change, there is a new index, Statistics change …
HTH – Antonio NAVARRO