Temporary index
A temporary index is a temporary object that allows the optimizer to create and use a radix index for a specific query. The temporary index has all the same attributes and benefits as a radix index created through the CREATE INDEX SQL statement or Create Logical File (CRTLF) CL command.
Additionally, the temporary index is optimized for use by the optimizer to satisfy a specific query request. This optimization includes setting the logical page size and 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
Generally a temporary index is a more expensive temporary object to create than other temporary objects. 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.
One significant advantage of the temporary index over other temporary objects is that it is the only temporary object maintained if the underlying table changes. The temporary index is identical to a radix index in that any inserts or updates against the table are reflected immediately through normal index maintenance.
SQE usage of temporary indexes is different from CQE usage in that SQE allows reuse. References to temporary indexes created and used by the SQE optimizer are kept in the system Plan Cache. A temporary index is saved for reuse by other instances of the same query or other instances of the same query running in a different job. It is also saved for potential reuse by a different query that can benefit from the use of the same temporary index.
By default, an SQE temporary index persists until the Plan Cache entry for the last referencing query plan is removed. With the SQE Plan Cache auto sizing capability, there is the potential for SQE temporary indexes to persist longer. You can control this behavior 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 cases where there is concern about increased maintenance costs for temporary indexes that persist for reuse.
A SQE temporary index can also be used as a source of statistics.
A temporary index is an internal data structure and can only be created by the database manager.
Visual explain icon:
