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 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:
- Temporary index scan
A temporary index scan operation is identical to the index scan operation that is performed upon the permanent radix index. It is still used to retrieve the rows from a table in a keyed sequence; however, the temporary index object must first be created. All of the rows in the index will be sequentially processed, but the resulting row numbers will be sequenced based upon the key columns. - Temporary index probe
A temporary index probe operation is identical to the index probe operation that is performed upon the permanent radix index. Its main function is to provide a form of quick access against the index keys of the temporary index; however it can still used to retrieve the rows from a table in a keyed sequence.