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.

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 of the keys associated with the temporary index.
Advantages	Potential to extract all of the data from the index keys' 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 a large number of 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
Messages indicating use	Optimizer Debug: CPI4321 -- Access path built for file EMPLOYEE. PRTSQLINF: SQL4009 -- Index created for table 1.
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

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 will determine 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 taken into consideration when determining how selection will be performed.

 

Parent topic:

Temporary index