Index scans on Function-based indexes   Donald K. Burleson

The “index only” execution plan from Oracle8i has been enhanced in Oracle9i to provide support for function-based indexes (FBIs).  To improve execution speed, Oracle is always looking to see if the solution to a query can be answered exclusively within the index. Oracle’s index-organized tables (IOT’s), are an excellent example of how Oracle is able to bypass table access whenever possible. In short, whenever the Oracle SQL optimizer detects that the query can be serviced without touching the table rows, Oracle will invoke a full-index scan, quickly reading every blocks of the index, without touching the table itself.

The full-index execution plan is faster than accessing the table for several reasons:

• The db_file_multiblock_read_count will be invoked for faster pre-fetch of index blocks. The full-index scan can use Oracle parallel query.

• Prior to Oracle9i, “index only” scans were possible only when the index was created without any NULL values.  In other words, the index must be created with a NOT NULL clause for Oracle to be able to use the index.

As you may know, function-based indexes (FBIs) were a Godsend in Oracle8, and allowed for the virtual elimination of the unnecessary long-table full-scan. Because a FBI can exactly replicate any column in the WHERE clause of a query, FBI are generally used to ensure that an index is being used to service a query.

Let’s use a simple table to illustrate how an index-full scan would work with an FBI.  Here we have a simple student table:

create table

   student

(

   student_name,

   date_of_birth

);

From this table, we create a concatenated FBI on all columns of the table:

create index

   whole_student

on

   student

(

   initcap(student_name),

   to_char(date_of_birth,’MM-DD-YY’)

);

Oracle9i will recognize that any query that referenced these columns in an SQL statement will be able to use the index-full scan:

select * from student

where

   initcap(student_name) = ‘Jones’;

select * from student

where

   to_char(date_of_birth,’MM-DD=YY’) = ’04-07-85’;

So, when will Oracle choose this execution plan? In practice, the full-index scan is quite rare because of two factors:

1. Oracle9i requires that all of the predicates in the WHERE clause match those columns in the index. 

2. With dozens of built-in functions available on any given column, it is highly that an index will exist that exactly matches the query.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.