 |
|
Oracle Built-in Functions
Oracle Tips by Burleson Consulting |
Oracle offers a wealth of built-in functions
(BIFs) that are used to transform table column data. These functions
are extremely useful to the SQL developers and programmers because
they can avoid having to perform cumbersome translations of incoming
column values from Oracle. In many cases, Oracle BIFs are used to
translate column data into the appropriate datatype (e.g. update
customer set date_of_birth = to_date(‘03-35-1956’,’MM-DD-YYYY’);)
[invalid date], and BIFs are also used to re-format non-displayable
native datatypes such as DATE into readable formats. It is also
common to use Oracle BIFs inside SQL that queries for table values.
While there are many dozens of Oracle BIFs
for everything from date manipulation to string conversion, we will
focus on some of the most common BIFs used in SQL queries and see
how they simplify the query and affect the execution plan for the
SQL. We will also examine the impact of BIFs on the execution plans
for SQL statements and see how function-based indexing can improve
the speed of these queries.
Here is a list of some frequently used Oracle
SQL BIFs:
-
to_char – The to_char
function is especially useful for translating DATE datatypes and
converting numeric columns to character representations.
-
upper
– The upper function is often used in queries that search
text columns and ensure that retrievals of case-sensitive data are
properly serviced.
-
lower – The lower
function is used to convert text to a lowercase representation and
is quite useful when searching for strings in text.
-
substr – The substr
function is used to extract sub-strings in a large character
column. This is commonly used to extract subsets from large
character datatype columns such as subsets of telephone numbers.
-
decode – The decode
function is used to translate values in an SQL statement from a
cryptic abbreviation to a readable value. For example, the
decode function can translate two-digit State names into the
full name of the State.
From the perspective of Oracle SQL tuning, we
must remember that these BIFs will often 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 January.
select
count(*)
from
perfstat.stats$snapshot
where
to_char(snap_time,'Mon') = 'Jan'
;
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.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
