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 the rows in the index are sequentially processed, but the resulting row numbers are sequenced based upon the key columns.

The sequenced rows can be used by the optimizer to satisfy a portion of the query request (such as ordering or grouping).

Table 1. Temporary index scan attributes
Data access method Temporary index scan
Description Sequentially scan and process all the keys associated with the temporary index.
Advantages
  • Potential to extract all the data from the index key values, thus eliminating the need for a Table Probe
  • Returns the rows back in a sequence based upon the keys of the index
Considerations Generally requires a Table Probe to be performed to extract any remaining columns required to satisfy the query. Can perform poorly when many rows are selected because of the random I/O associated with the Table Probe.
Likely to be used
  • When sequencing the rows is required for the query (for example, ordering or grouping)
  • When the selection columns cannot be matched against the leading key columns of the index
  • When the overhead cost associated with the creation of the temporary index can be justified against other alternative methods to implement this query
Example SQL statement 
SELECT * FROM Employee
WHERE WorkDept BETWEEN 'A01' AND 'E01'
ORDER BY LastName
OPTIMIZE FOR ALL ROWS
Database Monitor and Plan Cache record indicating use

QQRID 3002 record and QQRID 3001

where QQKP(Index_Probe_Used) = 'N'.
SMP parallel enabled Yes
Also referred to as Index Scan

Index Scan, Preload

Index Scan, Distinct

Index Scan Distinct, Preload

Index Scan, Key Selection
Visual Explain icon
Temporary radix index scan

Using the example above, the optimizer chose to create a temporary index to sequence the rows based upon the LastName column. A temporary index scan might then be performed to satisfy the ORDER BY clause in this query.

The optimizer determines where the selection against the WorkDept column best belongs. It can be performed as the temporary index itself is being created or it can be performed as a part of the temporary index scan. Adding the selection to the temporary index creation has the possibility of making the open data path (ODP) for this query non-reusable. This ODP reuse is considered when determining how selection is performed.