Donald K. Burleson Oracle Tips

Evaluating Oracle index access methods

Oracle9i offers a variety of indexing methods including b-tree, bitmapped, and function-based indexes. Regardless of the index structure, an Oracle index can be thought of as a pair bond of a symbolic key, paired with a ROWID.

The goal of Oracle index access is to gather the ROWIDs required to quickly retrieve the desired rows from the table.  Within Oracle, we see the following types of index access.

Index Range Scan

The index range scan is one of the most common access methods. During an index range scan, Oracle accesses adjacent index entries and then uses the ROWID values in the index to retrieve the table rows.

An example of an index range scan would be the following query.

select
   employee_name
from
   employee
where
   home_city = ‘Rocky Ford’;

In practice, many Oracle SQL tuning professionals will resequence the table rows into the same physical order as the primary index. This technique can reduce disk I/O on index range scans by several orders of magnitude. For details, see "Turning the Tables on Disk I/O" in the January/February 2000 issue of Oracle Magazine online.

Fast Full-index Scan

Index full scans are sometimes called fast full-index scans, which were introduced in Oracle 7.3. There are some SQL queries that can be resolved by reading the index without touching the table data. For example, the following query does not need to access the table rows, and the index alone can satisfy the query.

select distinct
   color,
   count(*)
from
   automobiles
group by
   color;

Oracle enhanced the fast full-index scan to make it behave similar to a full-table scan. Just as Oracle has implemented the initialization parameter db_file_multiblock_read_count for full-table scans, Oracle allows this parameter to take effect when retrieving rows for a fast full-index scan. Since the whole index is accessed, Oracle allows multi-block reads.

There is a huge benefit to not reading the table rows, but there are some requirements for Oracle to invoke the fast full-index scan.

• All of the columns required must be specified in the index. That is, all columns in the select and where clauses must exist in the index.

• The query returns more than 10 percent of the rows within the index. This 10 percent figure depends on the degree of multi-block reads and the degree of parallelism.

• You are counting the number of rows in a table that meet a specific criterion. The fast full-index scan is almost always used for count(*) operations.

The cost-based optimizer will make the decision about whether to invoke the fast full-index scan in accordance with the table and index statistics.  You can also force a fast full-index scan by specifying the index_ffs hint:
 
select distinct /*+ index_ffs(c,pk_auto) /*
    color,
    count(*)
from
   automobiles
group
   by color;

It is not always intuitive whether a fast full-index scan is the fastest way to service a query, because of all of the variables involved. Hence, most expert SQL tuners will time any query that meets the fast full-index scan criteria and see if the response time improves.

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

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm