 |
|
Oracle Index Access Methods
Oracle Tips by Burleson Consulting |
As you may know, Oracle 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 and a ROWID. The goal of
index access is to gather the ROWIDs required to quickly retrieve
the desired table rows. Within Oracle, we see the following types of
index access.
-
Index range scan This
is the retrieval of one or more ROWIDs from an index. Indexed
values are generally scanned in ascending order.
-
Index unique scan This
is the retrieval of a single ROWID from an index.
-
Descending index range scan This
is the retrieval of one or more ROWIDs from an index. Indexed
values are returned in descending order.
-
And-equal filter This
is an operation that gathers multiple sets of ROWIDs from the
where clause of a query (e.g., select customer_name from
customer where status = ‘OPEN’ and age > 35;). The
and-equal operation compares the sets of ROWIDs and returns
the intersection of these sets, thereby eliminating duplicates and
satisfying the and conditions in the where clause.
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 (see Figure 3-5).
Figure 5: An index
range scan
An example of an index range scan would be the
following query.
select
employee_name
from
employee
where
home_city = ‘Rocky Ford’;
Tip: Because an index range scan fetches
the ROWID list from the index, each ROWID will most likely point to
a different data block causing a disk I/O for each block in the
index range scan. 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" by Don Burleson in the January/February 2000
issue of Oracle Magazine online.
Oracle provides a column called
clustering_factor in the Remote DBA_indexes view that tells you
how synchronized the table rows are with your index. When the
clustering factor is close to the number of data blocks, the table
rows are synchronized with the index. As the clustering_factor
approaches the number of rows in the table, the rows are out of sync
with the index.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
