Sorted list probe

 

A sorted list probe operation is used to retrieve rows from a temporary sorted list based upon a probe lookup operation.

The optimizer initially identifies the keys of the temporary sorted list from the join criteria specified in the query. This is done so that when the sorted list probe is performed, the values used to probe into the temporary sorted list will be extracted from the join-from criteria specified in the selection. Those values will be used to position within the sorted list in order to determine if any rows have a matching value. All of the matching join rows are then returned to be further processed by the query.

Table 1. Sorted list probe attributes
Data access method Sorted list probe
Description The temporary sorted list is quickly probed based upon the join criteria.
Advantages

  • Provides very quick access to the selected rows that match probe criteria

  • Reduces the random I/O to the table generally associated with longer running queries that otherwise use an index to collate the data

  • Selection can be performed before generating the sorted list to subset the number of rows in the temporary object
Considerations Generally used to process non-equal join criteria. Can perform poorly when the entire sorted list does not stay resident in memory as it is being populated and processed.
Likely to be used

  • When the use of temporary results is allowed by the query environmental parameter (ALWCPYDTA)

  • When the data is required to be collated based upon a column or columns for join processing

  • The join criteria was specified using a non-equals operator
Example SQL statement 
SELECT * FROM Employee XXX, Department YYY WHERE XXX.WorkDept > YYY.DeptNbr OPTIMIZE FOR ALL ROWS
Messages indicating use There are multiple ways in which a sorted list probe can be indicated through the messages. The messages in this example illustrate how the SQL Query Engine will indicate a sorted list probe was used.

  • Optimizer Debug: 
    CPI4327 -- File EMPLOYEE processed in join position 1.
CPI4327 -- 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.
SQL4010 -- Table scan access for table 2.
SMP parallel enabled Yes
Also referred to as Sorted List Probe, Preload

Sorted List Probe Distinct

Sorted List Probe Distinct, Preload

Visual Explain icon

Sorted list probe icon

The sorted list probe access method is generally considered when determining the implementation for a secondary table of a join. The sorted list is created with the key columns that match the non-equal join criteria for the underlying table. The sorted list probe allows the optimizer to choose the most efficient implementation to select the rows from the underlying table without regard for any join criteria. This single pass through the underlying table can now choose to perform a Table Scan or use an existing index to select the rows needed for the sorted list population.

Since sorted lists are constructed so that the majority of the temporary object will remain resident within main memory, the I/O associated with a sorted list is minimal. Additionally, if the sorted list was populated with all necessary columns from the table, no additional Table Probe will be required in order to finish processing this table, once again causing further I/O savings.

 

Parent topic:

Temporary sorted list

 

Related concepts


Nested loop join implementation