 |
|
BIFs and Function-Based Indexes
Oracle Tips by Burleson Consulting |
In almost all cases, the use of a BIF in a
SQL query will cause a full-table scan of the target table. To avoid
this problem, many Oracle Remote DBAs will create corresponding indexes
that make use of function-based indexes. If a corresponding
function-based index matches the built-in function of the query,
Oracle will be able to service the query with an index range scan
thereby avoiding a potentially expensive full-table scan.
To illustrate, let’s take a simple example.
We start by running the access.sql script to explain all of the SQL
in our library cache. The first report from access.sql shows all
full-table scans and indicates the table size in rows as well as
data blocks.
OWNER
NAME NUM_ROWS C K BLOCKS NBR_FTS
-------------- ------------------------ ------------ - - --------
--------
SYS DUAL N
2 412
SYSTEM SQLPLUS_PRODUCT_PROFILE N
2 344
DONALD CUSTOMER 461,232 N
71,192 89
From this report we see that the customer table
is quite large (71,192 blocks) and has experienced 89 full-table
scans. To be pragmatic, we will assume that the Oracle Remote DBA has no
knowledge of the SQL and must locate the statement from the library
cache. Our next step is to see if these full-table scans are
legitimate or if the query speed could be improved by using a
function-based index.
We do this by running get_sql.sql and checking
for SQL that references the subscription table and has a value of 89
for executions.
Get_sql.sql
set lines 2000;
select
sql_text,
disk_reads,
executions,
parse_calls
from
v$sqlarea
where
lower(sql_text) like '% customer %'
order by
disk_reads desc
;
From the output of this script, we can easily
identify the SQL that has 89 executions and we can cut-and-paste
this SQL to get the execution plan. Here is the SQL statement that
we extracted from the output of the get_sql.sql script. After
examining the SQL we clearly see that it is accessing a customer by
converting the customer name to uppercase using the upper BIF.
select
c.customer_name,
o.order_date
from
customer c,
order o
where
upper(c.customer_name) = upper(:v1)
and
c.cust_nbr = o.cust_nbr
;
Running the explain plan utility
confirms our suspicion that this query is responsible for the
full-table scans. Below is the output from running the plan.sql
script after explaining the statement.
OPTIONS OBJECT_NAME
POSITION
------------------------------ ------------------------------
----------
SELECT STATEMENT
4
NESTED LOOPS
1
TABLE ACCESS
FULL
CUSTOMER 1
TABLE ACCESS
BY INDEX ROWID
ORDER 2
INDEX
RANGE SCAN CUST_NBR_IDX
1
The table access full customer option confirms
our suspicion that the BIF has caused a full-table scan. Since we
know that a matching function-based index may change the execution
plan, we add a function-based index on upper(customer_name).
Note that it is often dangerous to add indexes to table because the
execution plans of many queries may change. However, we do not have
this problem with a function-based index because Oracle will only
use this type of index when the query uses a matching BIF.
create index
upper_cust_name_idx
on
customer
(upper(customer_name))
tablespace customer
pctfree 10
storage (initial 128k next 128k maxextents 2147483645 pctincrease
0);
Now we can re-explain the SQL and see that
the full-table scan has been replaced by a index range scan on our
new function-based index. For this query, we have changed the
execution time from 45 seconds to less than 2 seconds.
OPERATION
--------------------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------ ------------------------------
----------
SELECT STATEMENT
5
NESTED LOOPS
1
TABLE ACCESS
BY INDEX ROWID
CUSTOMER 1
INDEX
RANGE SCAN CUST_NBR_IDX
1
TABLE ACCESS
BY INDEX ROWID
ORDER 2
INDEX
RANGE SCAN UPPER_CUST_NAME_IDX
1
This simple example serves to illustrate the foremost SQL tuning
rule for BIFs. Whenever a BIF is used in an SQL statement, a
function-based index must be created.
Next let’s look at another popular extension that
allows Oracle to support some object-oriented constructs. Starting
with Oracle8, Oracle has made a commitment to adding object-oriented
extension to the database, and Oracle also was required to make
corresponding changes to Oracle SQL.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
