Temporary index

Additionally, the temporary index can be optimized for use by the optimizer to satisfy a specific query request. This optimization includes applying any selection to the index to speed up its use after creation.

The temporary index can be used to satisfy various query requests:

• Ordering
• Grouping/Distinct
• Joins
• Record selection

The optimizer will create a temporary index in two situations. First, using its regular cost estimating methods, the optimizer might determine that a temporary index can provide the most efficient implementation for a query request. If the estimated cost to build and use the temporary index is low enough, the optimizer will generate the temporary index and then use it to run the query. Second, when other kinds of temporary objects are not allowed by environmental parameters, such as ALWCPYDTA or cursor sensitivity, the optimizer might be required to create and use a temporary index to implement ordering or grouping if no suitable permanent index is available.

Generally a temporary index is a more expensive temporary object to build than other temporary objects. The optimizer will consider the cost to build the temporary index as well as the likelihood that it will be re-used when deciding whether to create it. It can be populated by a table scan, or by one or more index scans or probes. The optimizer considers all the methods available when determining which method to use to produce the rows for the index creation. This process is like the costing and selection of the other temporary objects used by the optimizer.

Unlike other temporary objects, a temporary index has the significant advantage of being maintained as the underlying table changes. Just as with a permanent index, any inserts or updates against the table are reflected immediately in the temporary index through normal index maintenance. This behavior is what allows the optimizer to use a temporary index even when the use of other temporary objects is restricted by environmental settings.

Because temporary indexes are maintained, they generally can provide more performance benefits than other temporary objects. Once the temporary index is created, the index can be reused by other instances of the same query within the same job or other instances of the same query running in a different job. In addition, the optimizer can often reuse the temporary index for a different query that references the table that the index is created over.

Usage of temporary indexes is tracked with the plans stored in the Plan Cache.

By default, a temporary index persists until the Plan Cache entry for the last referencing query plan is removed. Once the last referencing plan is removed from the Plan Cache, the index is deleted. Since the Plan Cache is cleared at IPL, all temporary indexes are also cleared at IPL. To avoid inconsistent performance due to temporary indexes being created or deleted, it can be helpful to create a permanent index to replace the temporary index. This is especially true for queries that are run frequently and that use a temporary index.

You can control the lifespan of temporary indexes by setting the CACHE_RESULTS QAQQINI value. The default for this INI value allows the optimizer to keep temporary indexes around for reuse.

Changing the INI value to '*JOB' prevents the temporary index from being saved in the Plan Cache; the index does not survive a hard close. The *JOB option causes the SQE optimizer use of temporary indexes to behave more like the CQE optimizer. The temporary index has a shorter life, but is still shared as long as there are active queries using it. This behavior can be desirable in the rare cases where there is concern about increased maintenance costs for temporary indexes that persist for reuse.

Like permanent indexes, a temporary index can also be used as a source of statistics to help guide the optimizer's decisions when planning a query.

A temporary index is an internal data structure and can only be created and dropped by the database manager.

When the optimizer creates a temporary index that is eligible for reuse by other queries, it also generates index advice corresponding to the index. This means that some amount of historical information about the creation and usage of temporary indexes is available by looking at index advice. The current state of temporary indexes on a system can be obtained with the MTI_INFO table function SQL service.

Visual explain icon: