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.

The temporary index is used by the optimizer to satisfy the join portion of the query request.

Table 1. Temporary index probe attributes
Data access method	Temporary index probe
Description	The index is quickly probed based upon the selection criteria that were rewritten into a series of ranges. Only those keys that satisfy the selection will be used to generate a table row number.
Advantages	Only those index entries that match any selection continue to be processed. Provides very quick access to the selected rows 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 the ability to probe the rows required for the query (for example, joins) exists 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	SELET * FROM Employee XXX, Department YYY WHERE XXX.WorkDept = YYY.DeptNo OPTIMIZE FOR ALL ROWS
Messages indicating use	There are multiple ways in which a temporary index probe can be indicated through the messages. The messages in this example illustrate one example of how the Classic Query Engine will indicate a temporary index probe was used. Optimizer Debug: CPI4321 -- Access path built for file DEPARTMENT. CPI4327 -- File EMPLOYEE processed in join position 1. CPI4326 -- File DEPARTMENT processed in join position 2. PRTSQLINF: SQL4007 -- Query implementation for join position 1 table 1. SQL4010 -- Table scan access for table 1. SQL4007 -- Query implementation for join position 2 table 2. SQL4009 -- Index created for table 2.
SMP parallel enabled	Yes
Also referred to as	Index Probe Index Probe, Preload Index Probe, Distinct Index Probe Distinct, Preload Index Probe, Key Selection
Visual Explain icon

Using the example above, the optimizer chose to create a temporary index over the DeptNo column to help satisfy the join requirement against the DEPARTMENT table. A temporary index probe was then performed against the temporary index to process the join criteria between the two tables. In this particular case, there was no additional selection that might be applied against the DEPARTMENT table while the temporary index was being created.