Databases Reference
In-Depth Information
because the table data is stored as part of the B-tree index. Therefore, all access to data within an index-
organized table is based on the primary key.
Instead of the normal physical
ROWID
s to locate table rows, index-organized tables use a logical
ROWID
, which is used by any secondary indexes on the IOT in order to retrieve data. The logical
ROWID
is
the equivalent to a physical guess of the row location based on the
ROWID
when the index entry was first
created. Based on the physical guess, Oracle will scan through leaf blocks searching for a match. The
physical guess doesn't change over time, even if a row's physical location changes. For instance, leaf
block splits can occur over time, which can fragment the index and change a row's physical location.
Because the physical guess is not updated even if a row location changes, the physical guesses can
become outdated or stale over time.
You can get information from the data dictionary to determine if the physical guesses for an IOT are
stale by querying the
PCT_DIRECT_ACCESS
column of
USER_INDEXES
. For example,
SQL> select index_name, index_type, pct_direct_access
2 from user_indexes;
INDEX_NAME INDEX_TYPE PCT_DIRECT_ACCESS
------------------------------ --------------------------- -----------------
EMPLOYEES_IOT_PK IOT - TOP 0
EMPLOYEES_PART_1I NORMAL 100
If the
PCT_DIRECT_ACCESS
value falls below 100, it means the secondary index entries are becoming
migrated, and the physical guess can start to be inaccurate enough that extra I/O operations will start
occurring and performance will start to degrade. Once the
PCT_DIRECT_ACCESS
falls below 80,
performance degradation will start becoming more noticeable and the index may be a good candidate
for a rebuild operation.
In order to refresh the logical
ROWID
s over time, there are two primary ways to address the issue.
•
Rebuild the secondary index.
Update the block references for the index.
The first way to refresh the logical
ROWID
s within secondary indexes is simply by rebuilding the
index(es). Rebuilding secondary indexes built on index-organized tables is no different than rebuilding
indexes on heap organized tables.
SQL> ALTER INDEX employees_1i REBUILD;
•
Of course, depending on the size of the table, rebuilding one or more secondary indexes can take
time, and with shrinking maintenance windows and ever increasing availability windows on databases,
it can be problematic to rebuild indexes on large tables on a regular basis.
An alternative to rebuilding your secondary indexes and a quick way to fix stale physical guesses
within your secondary indexes is by using the
ALTER INDEX...UPDATE BLOCK REFERENCES
command,
which is a fast way to realign stale physical guesses without having to rebuild an entire index.
SQL> ALTER INDEX employees_part_1i UPDATE BLOCK REFERENCES;
You can also place bitmap indexes on IOTs as secondary indexes. Refer to Chapter 3 for examples of
creating bitmap indexes on an IOT. Within the bitmap index, since there is an entry for each row in a
given table, there is normally a
ROWID
, along with the bitmap and data value corresponding to the
indexed column. Since there are no physical
ROWID
values with an index-organized table, a bitmap index
