Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter