8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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
- Online
CREATE
,REBUILD
andCOALESCE
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.
For more information see:
Hope this helps. Regards Tim...