Favoring index access

You can increase the likelihood that Db2 chooses matching index access when other access methods might result in suboptimal performance.

Before you begin

Index access might sometimes be more costly than a table space scan or non-matching index access. Use these methods only when you are certain that index access is preferable.

About this task

Begin program-specific programming interface information. Db2 often scans a table space or nonmatching index when statistics indicate that a table is small, even though matching index access is possible. This choice might become a problem in certain cases. For example, a table might be small or empty when statistics are collected. Later, after data is inserted in the table, statistics such as the value of the NPAGES column in the SYSIBM.SYSTABLES catalog table might no longer represent the actual volume of data in the table. The result might be that Db2 chooses an inefficient access path because of the inaccurate cost estimate.

Another reason to favor index access might be a database design in which tables contain groups of rows that logically belong together. The rows within each group are accessed in the same sequence every time. The access sequence is based on the primary key. Lock contention can occur when Db2 chooses different access paths for other applications that operate on tables that use this design. Index access can reduce contention and preserve the access sequence that the primary key provides.

Procedure

To favor the use of index access over other access methods, use any of the following approaches:

  • Specify the VOLATILE option when you create or alter a table.
    A table that is defined with the VOLATILE keyword is considered a volatile table. When Db2 executes queries that access volatile tables, Db2 chooses index access whenever possible.

    This approach is best for when you want to favor matching index access only for specific tables. It specifically targets queries that access tables that are defined as volatile.

    VOLATILE can favor index usage for single table queries or for the leading table of join queries. However, be aware that VOLATILE might not address the index choice for multi-table joins, or might not adequately influence the table join sequence.

  • Specify a non-zero value for the NPGTHRSH subsystem parameter.
    When NPGTHRSH is set to a value greater than 0, Db2 compares the NPGTHRSH value to the NPAGESF column in the SYSIBM.SYSTABLES catalog table for table-level comparisons and the NPAGES column of the SYSIBM.SYSTABSTATS table for partition-level comparisons. Db2 uses index access for any query that accesses a table or partition that has fewer data pages than the NPGTHRSH value, regardless of the cost estimates. When NPGTHRSH is set to 0, the NPGTHRSH comparison is disabled, and Db2 selects the access path based on estimated costs, using available statistics.

Results

At access path selection for bind or prepare, Db2 uses real-time statistics values and favors index access for tables defined as VOLATILE or that qualify for the value of the NPGTHRSH subsystem parameter. Db2 uses the index that has the most matching columns.