Radix index probe

A radix index probe operation is used to retrieve the rows from a table in a keyed sequence. The main difference between the radix index probe and the scan is that the rows returned are first identified by a probe operation to subset them.

The optimizer attempts to match the columns used for some or all the selection against the leading keys of the index. It then rewrites the selection into a series of ranges that can be used to probe directly into the index key values. Only those keys from the series of ranges are paged into main memory.

The resulting row numbers generated by the probe can then be further processed by any remaining selection against the index keys or a table probe operation. This method provides for quick access to only the rows of the index that satisfy the selection.

The main function of a radix index probe is to provide quick selection against the index keys. In addition, the row sequencing can be used to satisfy other portions of the query, such as ordering or grouping. Since the index I/Os are only for rows that match the probe selection, no extraneous processing is performed on rows that do not match. This savings in I/Os against rows that are not a part of the result set is one of the primary advantages for this operation.

Table 1. Radix index probe attributes
Data access method	Radix 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 are 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 asking for or expecting only a few rows to be returned from the index When sequencing the rows is required the query (for example, ordering or grouping) When the selection columns match the leading key columns of the index
Example SQL statement	`CREATE INDEX X1 ON Employee (LastName, WorkDept) SELECT * FROM Employee WHERE WorkDept BETWEEN 'A01' AND 'E01' AND LastName IN ('Smith', 'Jones', 'Peterson') OPTIMIZE FOR ALL ROWS`
Database Monitor and Plan Cache record indicating use	QQRID 3001 Index Used where QQKP (Index_Probe_Used) set to 'Y' will indicate an index probe operation. Preload indicated by QVPARPL = 'Y' Distinct Probe indicated by QVC11 = 'Y'
SMP parallel enabled	Yes
Also referred to as	Index Probe Index Probe, Preload Index Probe, Distinct Index Probe Distinct, Preload Index Probe, Key Positioning Index Scan, Key Row Positioning
Visual Explain icon

The following example illustrates a query where the optimizer might choose the radix index probe access method:

     CREATE INDEX X1 ON Employee (LastName, WorkDept)
 
     SELECT * FROM Employee
     WHERE WorkDept BETWEEN 'A01' AND 'E01'
     AND LastName IN ('Smith', 'Jones', 'Peterson')
     OPTIMIZE FOR ALL ROWS

In this example, index X1 is used to position to the first index entry that matches the selection built over both columns LastName and WorkDept. The selection is rewritten into a series of ranges that match all the leading key columns used from the index X1. The probe is then based upon the composite concatenated values for all the leading keys. The pseudo-SQL for this rewritten SQL might look as follows:

     SELECT * FROM X1
     WHERE X1.LeadingKeys BETWEEN 'JonesA01' AND 'JonesE01'
        OR X1.LeadingKeys BETWEEN 'PetersonA01' AND 'PetersonE01'
        OR X1.LeadingKeys BETWEEN 'SmithA01' AND 'SmithE01'

All the key entries that satisfy the probe operation are used to generate a row number for the table associated with the index (for example, Employee). The row number is used by a Table Probe operation to perform random I/O on the table to produce the results for the query. This processing continues until all the rows that satisfy the index probe operation have been processed. In this example, all the index entries processed and rows retrieved met the index probe criteria.

Additional selection might be added that cannot use an index probe, such as selection against columns which are not leading key columns of the index. Then the optimizer performs an index scan operation within the range of probed values. This process still allows for selection to be performed before the Table Probe operation.