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 of the same attributes and benefits as a radix index that is created by a user 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 includes setting the logical page size and applying any selection to the creation to speed up the use of the temporary index after it has been created.
The temporary index can be used to satisfy a variety of 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 either performing a table scan to fetch the rows to be used for the index or by performing an index scan or probe against one or more indexes to produce the rows. The optimizer considers all of the methods available when determining which method to use to produce the rows for the index creation. This process is similar to the costing and selection of the other temporary objects used by the optimizer.
One significant advantage of the temporary index over the other forms of temporary objects is that the temporary index is the only form of a temporary object that is maintained if the underlying table changes. The temporary index is identical to a radix index in that as any inserts or updates are performed against the table, those changes are reflected immediately within the temporary index through the normal index maintenance processing.
SQE usage of temporary indexes is different than 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, a SQE temporary index persists until the Plan Cache entry for
the last referencing query plan is removed. 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 SQE optimizer use of temporary indexes to behave more like CQE optimizer;
it becomes shorter lived, but 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 temporary index is an internal data structure and can only be created by the database manager.
Visual explain icon:
