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,
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).
where f (x) = expression
Oracle 8 and higher supports creation of an index on a stable
function value, as in the following example.
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
create index emp_upper_idx on emp (upper (ename));
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.
|
|
|