Built-in functions and the CBO
Oracle created function-based indexes (FBI’s)
to ensure that indexes are used when you must include a built-in
function on an indexed column in your SQL. For example, the
following query would not be able to use an index on the mydate
column:
. . .
where to_char(mydate) = ’10-1-07’
If it’s necessary to change an indexed column
with a function, you can create a function-based index on the
column:
create
index
mydate_to_char
on
mytab (to_char(mydate));
It’s always a good idea to re-work your SQL to
remove built-in function from any where clause predicates:
Bad:
where
to_num(char_column) = 123
where
to_char(date_column) = ’10-1-07’
Good:
where
char_column = ‘123’
where
date_column = to_date(’10-1-07’);
The “index invalidation also extends to
partitioned table where you use a built-in function on a partitioned
table key.
Partition pruning and predicate functions
Partitioning pruning refers to the ability of
the cost-based optimizer to exclude irrelevant table partitions when
servicing an SQL query.
This blogger has published some excellent advice on the issue
of using built-in functions on partition key columns. Essentially,
the use of a “predicate” built-in function (e.g. trunc, substr,
to_char, &c), will disable partition pruning unless a check
constraint is placed on the column:
“When you place a
predicate on a function of a partition key column then the optimizer
is prevented from pruning unless there is a check constraint to
indicate logical equivalence between the column and the function of
the column.”
“The partitioning
scheme acts effectively as a series of check constraints on the
individual partitions that allow table access to be avoided when it
can be deduced that a predicate does not resolve to any partitions
at all.”
This comment by Pete S. confirms that built-in
functions can be problematic when used on table partition keys:
“Elsewhere, this week
he has made an interesting observation on the use of
functions on partition keys in queries . . . TRUNC prevents the use
of partition elimination and therefore negates a major performance
enhancing feature in an Oracle DW.”
Also, see these insightful notes on
predicate pushing.
The moral of this story is that you should
avoid the use of built-in functions on partitioned table keys
whenever possible, but if you must use a function predicate on a
partition key, make sure to create a corresponding check constraint
|
