Reorganizing Indexes

There are many ways to reorganize indexes. This section discusses the ones that will be both covered in the exam and will also be a big part of your life as a DBA.

Rebuilding Indexes

It is often necessary to rebuild indexes. This can happen for many reasons. The existing index may need to be moved to a different tablespace (it may have inadvertently gotten built in the wrong tablespace originally). The index may contain many deleted entries as can be the case if data older than the current month is deleted, and the date is one of the index columns. These deletions may leave holes in the index. Or an existing normal index needs to be converted to a reverse key index as might be the case when migrating applications from an older version of Oracle on which reverse key indexes were not supported. Finally, one of the main reasons that an index might need to be rebuilt is when the table on which the index was built has been moved to a different tablespace.

Online index rebuilds share the following characteristics:

• The new index is built using the existing index as its data source rather than the underlying table.

• Sorts are not needed in the rebuild of an existing index. This can mean far better performance than dropping and rebuilding the index.

• The old index is automatically deleted after the new index is built. During the rebuild, it is necessary to have sufficient space allocated to accommodate both the old index and the new index in the respective tablespaces.

• The new index is more efficient because it does not have any deleted entries. Not only is the index likely more efficient, but the space is used more efficiently and effectively.

• Queries can continue using the existing index while the new index is being built.

The ALTER INDEX statement that allows for the rebuilding of indexes includes the same parameters as the original creation command does as far as the storage parameters, and LOGGING or NOLOGGING. The keywords REVERSE or NOREVERSE can be specified only if the index is a B-tree index.

The following is an example of the code that could be used to rebuild an index online:

 ALTER INDEX address_last_name_idx REBUILD ONLINE; 

It is important to remember that building indexes and rebuilding them can be time consuming, particularly if the table is large. Before Oracle 8i, tables had to be locked any time you created or rebuilt indexes, preventing DML from occurring simultaneously with the rebuild.

You cannot perform other DDL operations on a table during an online rebuild of any of its indexes.

Rebuilding in Oracle 9i can be done with minimal table locking; however, there are some restrictions as follows:

• You cannot rebuild an index on a temporary table.

• You cannot rebuild an entire partitioned index, although you may rebuild each partition or subpartition individually.

• You cannot deallocate unused space in the rebuild.

• You cannot change the value of PCTFREE parameter for the index in the rebuild.

Coalescing Indexes

Whenever you encounter index fragmentation, you can choose to rebuild the index, or you can simply coalesce the index. Before you opt for one choice over the other, carefully consider the ramifications of each and choose the option that works best in your given situation.

If you have a B-tree index with leaf blocks that can be freed up for reuse, merging those leaf blocks may provide the most elegant and simple solution as follows:

 ALTER INDEX address_last_name_idx COALESCE; 

This can have the desired effect of reducing fragmentation.

It is often necessary to determine the validity of an index. The following section discusses this process.

Validating Indexes

You can analyze an index to check all the index blocks for corruption. This does, however, not verify that any of the index values actually correspond to any data in the underlying table.

This analysis has the added effect of populating the INDEX_STATS view with information about the index.

The following code could be used to validate the structure of an index:

 ANALYZE INDEX address_last_name_idx VALIDATE STRUCTURE; 

After running this command, a query against INDEX_STATS allows you to obtain information about the index, such as the number of blocks, the percent used, number of leaf rows, and number of deleted leaf rows. Oracle suggests that, if the ratio of the number of deleted leaf rows versus the number of leaf rows exceeds 30%, reorganization would be beneficial.

Drop Indexes

It is often advantageous to drop an index right before a bulk load and re-create it afterward or when the index becomes invalid. It is also often necessary to drop indexes that are either used infrequently or never used and to rebuild them whenever the need arises. Often a drop is necessary if the index has become corrupt.

The following code could be used to drop an index:

 DROP INDEX addresses_first_name_idx; 

Monitor the Usage of Indexes

Beginning in Oracle 9i, one of the most useful new features is the capability to gather statistics about the usage of an index. This information can be gathered and displayed using the V$OBJECT_USAGE view. This allows you to determine that an index is never used and can therefore be dropped. Further, elimination of unused indexes can cut down on the overhead that has to occur whenever DML occurs in the database, and performance will therefore improve. Each time that you specify the MONITORING USAGE clause, the V$OBJECT_USAGE view will be reset for the specified index, and the previous information will be reset or cleared out.

To start monitoring, issue the following command:

 ALTER INDEX address_first_name_idx MONITORING USAGE; 

To stop monitoring usage, the command is as follows:

 ALTER INDEX address_first_name_idx NOMONITORING USAGE; 

A query against the V$OBJECT_USAGE view provides the index name (INDEX_NAME), the corresponding table name (TABLE_NAME), the status of monitoring as either on or off (MONITORING), whether the index was used (USED), and the start and end times of the monitoring session (START_MONITORING and END_MONITORING).

But this is just one of the valuable data dictionary views available to assist you with your dealings with indexes. The following section provides information on more.