Avoid Functions in Query Conditions

David Clement

September 2004

Oracle versions 7-8

If a query condition equates the output of a function on a column to any expression, that is, if it is written in a form similar to this,

          where f (x) = expression
the optimizer will not attempt to use any index that may exist on column x. This is logical, because an index on a value x is not likely to be useful for finding f(x).

Oracle 8 and higher supports creation of an index on a stable function value, as in the following example.

          create index emp_upper_idx on emp (upper (ename));
These indexes on deterministic functions work well except that for a volatile table, a function-based index has the drawback of considerable overhead on every insert statement. For non-volatile tables, function-based indexes are a good solution. For volatile ones, it is best to correct the original data problem and avoid using the function in the query condition.