BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










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.


set lines 2000;

   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.

   customer c,
   order    o 
upper(c.customer_name) = upper(:v1) 

   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
------------------------------ ------------------------------ ----------
FULL                           CUSTOMER                                1
BY INDEX ROWID                 ORDER                                   2
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

  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.

OPTIONS                        OBJECT_NAME                      POSITION
------------------------------ ------------------------------ ----------
BY INDEX ROWID                 CUSTOMER                                1
RANGE SCAN                     CUST_NBR_IDX                            1
BY INDEX ROWID                 ORDER                                   2
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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter