Temporary index probe

A temporary index probe operation is identical to the index probe operation that is performed on the permanent radix index. Its main function is to provide quick access against the index keys of the temporary index. However, it can still be 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 is used to generate a table row number.
Advantages
  • Only those index entries that match any selection continue to be processed. Provides quick access to the selected rows
  • 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 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
Database Monitor and Plan Cache record indicating use

QQRID 3002 record and QQRID 3001

where QQKP(Index_Probe_Used) = 'Y'.
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
Temporary index probe

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.