Tuning indexes

When a table is updated, its indexes are also updated to maintain database conformity. When an index has been defined, the following items must be evaluated.

Items to be evaluated

The number of times index update processing is executed increases proportionally to the number of indexes defined for the table, resulting in a commensurate increase in the SQL processing time.
When an index is updated, update information is collected in the system log file. Therefore, if the number of indexes increases, the amount of data to be output to the system log file also increases commensurately.
If a shortage occurs in an index page for storing updated key values, an index page split occurs. When index page split occurs, system log information on the split is collected, thereby increasing the amount of data to be stored in the system log file.

Information to be collected: Index statistical information is collected by the statistics analysis utility.

Organization of this section: (1) Splits count (SP_NM)

Purpose

This information is collected in order to determine how appropriate it is to use indexes. Indexes can affect performance adversely in the following case:

If an appropriate free space ratio (PCTFREE) is not specified in the index definition, index page splitting might occur.

In such a case, the index definition should be modified on the basis of the index splits count.

Evaluating the analysis results

If index page splitting occurs frequently, the index might have been expanded by an application that involves mainly data insertion (INSERT) into the table, resulting in frequent index page splits.
If a table is partitioned and index page splitting occurs frequently in a specific RDAREA, update processing might be concentrated on a specific key range due to poor table partitioning.

Actions to be taken

Take one of the following actions:

Increase the free space ratio (PCTFREE) specified in the index definition. If a large amount of data is to be added, use the database load utility.
Delete any unneeded indexes.
If a table is partitioned, check the key range in which the index page splits are concentrated and determine whether to partition further.

23.7 Tuning indexes

(1) Splits count (SP_NM)