Rebuilding Indexes Online

New Online Operations

Rebuilding Indexes Online

8i introduced the online building of indexes. 9i extends this feature to the online rebuilding of reverse key indexes, function-based indexes, and key-compressed indexes on tables and index-organized tables (IOTs). 9i does not support online index rebuilding for bitmap indexes or partitioned local and global indexes.

While the index is being rebuilt online, users can issue DML against the table but not DDL. Changes that occur during the index rebuild are internally documented by Oracle in an IOT. The syntax to rebuild an index online is:

ALTER INDEX my_index REBUILD ONLINE ;

Managing Index Organized Tables (IOTs) Online

You can now coalesce IOTs online. The coalesce operation defragments the index tree. Do this by:

ALTER TABLE my_iot_table COALESCE ;

You can now create secondary indexes for IOTs while they are in use:

CREATE INDEX my_iot_secondary_index
ON my_iot_table (column_to_index) ONLINE ;

You can also rebuild the IOT’s secondary index while it is online:

ALTER INDEX my_iot_secondary_index REBUILD ONLINE ;

As the index becomes stale, you may update the logical ROWIDs used in the IOT’s secondary index by this statement:

ALTER INDEX my_iot_secondary_index UPDATE BLOCK REFERENCES ;

If you store a non-key column in the primary key B-tree index of an IOT, it reduces the density of the index rows in the leaf blocks. Specify an overflow segment in a move operation to address this problem:

ALTER TABLE my_iot_table MOVE ONLINE
TABLESPACE my_ts
OVERFLOW TABLESPACE my_overflow_ts ;