WHERE clauses that modify the value of an indexed column will not use an index. Similarly, WHERE clauses that modify the value of a cluster key column will not perform a hash cluster scan.
WHERE monthly_sal*12 > 100000
WHERE SUBSTR(key,1,3) = 'FOO'
WHERE initial || ' ' || surname = 'J BLOGGS'
In order to use an index or hash cluster scan, the indexed column / cluster key must appear unaltered in the WHERE clause. Often it is possible to overcome this problem by restructuring your WHERE clause
WHERE monthly_sal > 100000 / 12
WHERE key LIKE 'FOO%'
WHERE initial = 'J' and surname = 'BLOGGS'
If your query cannot be restructured and the query is critical to your system, you may be able to use a function based index. A function based index allows you to create an index over a function of column instead of the column itself.
CREATE INDEX my_table_i1 ON my_table(substr(my_col,2,5))See the CREATE INDEX command in the Oracle SQL Reference documentation for more information on Function Based Indexes.