Monitoring and Tuning Database Performance › Items to Monitor and Tune › Tuning an Index

Tuning an Index

Tuning of an index should be done when any of the following conditions exist:

• Index usage performance degradation
• Index needs redimensioning

One way to tune an index is to rebuild it: for a non-SQL defined database, use MAINTAIN INDEX, for an SQL-defined database, drop the index and recreate it.

An alternative way of index tuning is to use the TUNE INDEX utility statement. TUNE INDEX compares to rebuilding the index as follows:

You can run the TUNE INDEX command to do the following:

• Enhance performance
  • Eliminate orphans at all levels of the index. This improves performance if the index is accessed at the bottom level.
  • Rebalance SR8's. In an unbalanced index, more records have to be accessed when traversing the index from the top to the bottom.
  • Resequence SR8's. Optimum performance is obtained if the top level SR8 resides on the same page as the index owner and if the bottom level SR8's are in physical sequence.
• Rebuild the index to accommodate future growth
  • Specify TEMPORARY PAGE RESERVE and TEMPORARY INDEX UTILIZATION to define the attributes with which to tune the index.

Run TUNE INDEX online or in batch through central version mode if the affected area or areas must remain online while the index is tuned.