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_namefrom employeewhere 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 automobilesgroup 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.
You can also force a fast full-index scan by specifying the index_ffs hint, and this is commonly combined with the parallel_index hint to improve performance. For example, the following query forces the use of a fast full-index scan with parallelism:
select distinct /*+ index_ffs(c,pk_auto) parallel_index(automobile, pk_auto) color, count(*)from automobilesgroup 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 “Creating a Self-tuning Oracle Database” by Rampant TechPress. It’s only $9.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:
Not all Indexes are used in Range Scans
When deciding to segregate indexes into larger blocksizes it is important to understand that those indexes that are subject to frequent index range scans XE "index range scans" and fast-full scans will benefit the most from a larger blocksize.

When Oracle joins two tables together with a nested loop, only one of the indexes may be accessed as a range. The optimizer always performs an index range scan on one index, gathers the rowid values, and does fetch by rowid on the matching rows in the other table. For example:

select
customer_name,
order_date
from
customer
orders
where
customer.cust_key = orders.cust_key;

The Oracle documentation notes “In a nested loop join, for every row in the outer row set, the inner row set is accessed to find all the matching rows to join. Therefore, in a nested loop join, the inner row set is accessed as many times as the number of rows in the outer row set.”

Oracle will only scan one index, build a set of keys, and then probe the rows from the other table (Figure 13.1).
So, if this nested loop never uses the customer index, why is it there? The answer is, for index unique scans. In an index unique scan, a single row is accessed within the index, as seen in this query:

select
customer_last_name,
customer_address
from
customer
where
cust_key = 123;

In sum, the DBA must find out “how” their indexes are being used by the SQL. Remember, an index that never experiences range scans would not benefit from a larger blocksize. The question becomes one of finding those indexes that experience lots of range scans, and AWR can help.

It is possible to identify those indexes with the most index range scans XE "index range scans" with the following simple AWR script.