We must remember that these "built in functions" will cause the SQL optimizer to perform a full-table scan unless a function-based index is created to match the BIF. The most common example of an Oracle BIF is the use of the
to_char function to translate a column with a DATE datatype. For example, the following query will display the number of STATSPACK snapshots that occurred in the month of March.
select count(*)
from
perfstat.stats$snapshot
where
to_char(snap_time,'MON') = 'Mar';
If we were using a generic index on the snap_time column, the to_char function would not be able to utilize the DATE index. However, with the use of built-in functions, an index can be built on
to_char(snap_time,'Mon')
and the SQL query could avoid a
full-table scan.
To get the "month" from a DATE datatype you simply: