Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Index Organized Table (IOT) Enhancements In Oracle 9i

The Index Organized Table (IOT) concept has matured in Oracle 9i to the point where the differences between IOTs and Heap Organized Tables (HOT) are less than the similarities.

Related articles.

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.

CREATE TABLE countries (
  country_id       CHAR(2) CONSTRAINT country_id_nn NOT NULL,
  country_name     VARCHAR2(40),
  currency_name    VARCHAR2(25),
  currency_symbol  VARCHAR2(3),
  region           VARCHAR2(15),
  CONSTRAINT country_c_id_pk PRIMARY KEY (country_id)
)
ORGANIZATION INDEX
MAPPING TABLE TABLESPACE tbs_1;

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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.