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.
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.
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
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.
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.
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:
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: