Logical Rowid

Like indexes, IOTs are subject to inserts, deletes, block splits and coalesces, all of which affect the rowid. This means
a standard rowid cannot always be used to retrieve an IOT record from a secondary index. For this reason secondary indexes
on IOTs use a logical rowid which is made up of the original rowid and the primary key of the row.
When a secondary index is referenced the rowid is used to find the block. If the block is not present at that disk address
the primary key is used to find the block. This initial rowid access is know as a guess, since the block may not be at
its origninal disk address anymore. Physical movements of the row do not affect the logical rowid, so long as the primary
key is not updated.

With time the percentage of hits using the guess rowid will drop. When the hitrate gets sufficiently low the index should
be dropped and recreated to refesh the guess rowids. The guess hitrate can be monitored using the PCT_DIRECT_ACCESS
column of DBA_INDEXES, ALL_INDEXES and USER_INDEXES.

SELECT index_name, index_type, pct_direct_access
FROM user_indexes
WHERE pct_direct_access IS NOT NULL;

Bitmap Secondary Indexes

A bitmap index contains a series of bitmaps that represent row locations corresponding to each key value. The bitmap
can locate the row because it assumes the rows are contiguous within the block. Since this is not the case in IOTs
Oracle 8i did not allow secondary bitmap indexes on IOTs.

Oracle 9i removes this restriction by introducing a mapping table which literally maps the bit position to the row
location within the IOT. The mapping table is created as part of the CREATE TABLE statement.

Maintenance of the mapping tables causes a performance overhead so they should only be created for IOTs that need
to support secondary bitmap indexes. A single mapping table can support multiple bitmap indexes on the same table.

Additional Enhancements

Online CREATE, REBUILD and COALESCE of secondary indexes. The base IOT is no longer locked during these DDL operations.

Parallel DML on IOTs.

Online MOVE of IOTs with overflow segments. In Oracel 8i an online move was only possible if all data was stored inline.