ss

Oracle Tips by Burleson

Rebuilding Indexes

Occasionally, the DBA may be required to rebuild indexes. In late Oracle version 7.3 and in Oracle8,Oracle8i, and Oracle9i, the ALTER INDEX . . . REBUILD command can be used to rebuild indexes on the fly. In releases prior to 8i, the rebuild was done offline, restricting access to the table to read-only. The ONLINE clause new in Oracle8i allows online rebuild of indexes. In earlier versions (pre-7.3), the indexes have to be rebuilt using drop and re-create scripts. If the scripts used to initially create the system are available, this is a relatively simple matter. If the scripts used to build the indexes are not available, or were never created in the first place, the script IND_RCT9i.SQL (available on the Wiley Web site) can be run to create a script that will re-create existing, simple, nonpartitioned indexes when run. For more complex indexes, use the DBMS_METADATA package available in Oracle9i. The script will not re-create bitmapped or partition indexes, though it can be modified to create bitmapped indexes by utilizing the INDEX_TYPE column of the USER_INDEXES or DBA_INDEXES views. The re-create script can also flag partition indexes by utilization of the PARTITIONED column of the USER_INDEXES or DBA_INDEXES views. To also rebuild partitioned indexes, the USER_IND_PARTITIONS, USER_PART_KEY_COLUMNS, or DBA_IND_PARTITIONS and DBA_PART_KEY_ COLUMNS views would have to be queried.

The DBA can use the ANALYZE command to validate an index’s structures. The format of this command follows. This data can help the DBA determine if a specific index has become corrupted and must be rebuilt.

See Code Depot

The results are supplied to the DBA on-screen and are placed in a view called index_stats, which is dropped upon session exit.

In Oracle9i, the new package DBMS_METADATA can be used to get DML to rebuild complex indexes. The DBMS_METADATA function FETCH_DDL returns a CLOB data item that contains the text to rebuild any database object.