 |
|
Tuning SQL with BIFs
Oracle Tips by
Burleson
|
Let’s begin with a review of the basic BIFs and
see how they are used. Oracle BIFs are especially useful for
retrieving Oracle table columns where a transformation is required. We
generally see a BIF under the following conditions:
-
Transforming characters Oracle provides
the to_number, to_date, upper, lower, and substr BIFs
for transforming character data at retrieval time.
-
Transforming dates The to_char
BIF is extremely useful for transforming Oracle date datatypes. The
to_char BIF is used with dates to extract specific days,
months, and years.
Of course, you know that the remedy for the
problem of non-index usage when a query contains a BIF is to
create a function-based index to match the predicate in the
where clause of the SQL. However, there are some subtle surprises
when using BIFs. Let’s take a closer look at each of these
transformation types and see how they change SQL execution.
Using BIFs with Character Datatypes
With character datatypes, we commonly see BIFs
used to transform character strings to remove case sensitivity. For
example, here we can query on the last_name column without
being concerned about case sensitivity:
select
customer_stuff
from
customer
where
upper(last_name) = ‘JONES’
;
Using BIFs with Date Datatypes
One of the most common uses of Oracle BIFs is
the transformation of the Oracle date datatype. As you know, the
Oracle date datatype stores both the date and the time down to the
hundredths of a second. Because of this high degree of precision, it
is difficult to convert the date datatype to a character. However, we
do have the nls_date_format session variable, which can be used
to change an Oracle SQL statement's display format for all dates.
Surprisingly, the nls_date_format also affects the execution
plan for SQL statements. To see, examine the following query to
display all employees hired in January.
select
ename
from
emp
where
to_char(hiredate,'MON') = 'JAN'
;
Here is the execution plan, and we see the expected
full-table scan.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
1
TABLE ACCESS
FULL
EMP
1
Now, if we change the nls_date_format, we can
rewrite the query to specify the numeric month.
SQL> alter
session set nls_date_format='MM';
Session altered.
Now we change the query from the character month (MON)
to the numeric month (MM), and we remove the to_char BIF.
select
ename
from
emp
where
hiredate = '01';
Here is the execution plan for the preceding SQL. Here
you see that by removing the to_char BIF, we are able to
utilize the index to service our query.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
64
TABLE ACCESS
BY INDEX ROWID
EMP
1
INDEX
RANGE SCAN
EMP_HIREDATE
1
However, please note that we are looking for
the character ‘01’. When we try to do a numeric comparison for a date,
we have other issues because of a datatype mismatch.
To illustrate, here is another query that uses
the to_number BIF to transform a date to pull all employees
hired after 1996.
select
ename
from
emp
where
to_number(to_char(hiredate,'YYYY')) > 1996
;
Here is the execution plan for this SQL. As we expect,
without a function-based index on hiredate, we see the
full_table scan.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
1
TABLE ACCESS
FULL
EMP
1
Now, it might be tempting to change the
nls_date_format to see if we can remove the full-table scan by
changing the date display to a four-digit numeric year value. Let’s
try it and watch what happens.
alter session
set nls_date_format='YYYY';
explain plan set statement_id='test3'
for
select
ename
from
emp
where
hiredate > 1956
;
Here you see an inconsistent datatypes message. This is
because the date datatype is not numeric, even if we manipulate the
nls_date_format to make the result look like a number.
hiredate > 1956
*
ERROR at line 7:
ORA-00932: inconsistent datatypes
Hence, we are forced to use the to_number BIF to
convert the date year to a number. Now, we should be comparing two
numeric values.
select
ename
from
emp
where
to_number(hiredate) > 1956
;
Here is the execution plan for this query, and as we
expect, adding the to_number BIF has make our date index
unusable, resulting in a full-table scan.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
1
TABLE ACCESS
FULL
EMP
1
Now, let’s add a function-based index to the emp
table, using the same nested BIFs that we used in our query.
create index
emp_hiredate_year
on
emp
(
to_number(to_char(hiredate,'YYYY'))
)
;
Now, there is no need to reset the nls_date_format,
and our original query should use our new function-based index:
select
ename
from
emp
where
to_number(to_char(hiredate,'YYYY')) > 1956
;
Here is the execution plan, and as we expected, our new
index has removed the full-table scan.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
1
TABLE ACCESS
BY INDEX ROWID
EMP
1
INDEX
RANGE SCAN
EMP_HIREDATE_YEAR
1
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
