Row number list probe

Row number list probe

A row number list probe operation is used to test row numbers generated by a separate operation against the selected rows of a temporary row number list. The row numbers can be generated by any operation that constructs a row number for a table. That row number is then used to probe into a temporary row number list to determine if that row number matches the selection used to generate the temporary row number list.
The use of a row number list probe operation allows the optimizer to generate a plan that can take advantage of any sequencing provided by an index, but still use the row number list to perform additional selection before any Table probe operations.
A row number list probe is identical to a bitmap probe operation. The only difference between the two operations is that a row number list probe is performed over a list of row addresses while the bitmap probe is performed over a bitmap that represents the row addresses.
Table 1. Row number list probe
Data access method Row number list probe
Description The temporary row number list is quickly probed based upon the row number generated by a separate operation.
Advantages

The temporary row number list only contains a rows' address, no data, so the temporary can be efficiently probed within memory

The row numbers represented within the row number list are sorted to provide efficient lookup processing to test the underlying table

Selection is performed as the row number list is generated to subset the number of selected rows in the temporary object

Considerations Since the row number list only contains the addresses of the selected rows in the table, a separate Table Probe operation must be performed in order to fetch the table rows
Likely to be used

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

When the cost of creating and probing the row number list is justified by reducing the number of Table Probe operations that must be performed

When multiple indexes over the same table need to be combined in order to minimize the number of selected rows
Example SQL statement
CREATE INDEX X1 ON Employee (WorkDept)
CREATE ENCODED VECTOR INDEX EVI2 ON      Employee (Salary)
CREATE ENCODED VECTOR INDEX EVI3 ON      Employee (Job)

SELECT * FROM Employee WHERE WorkDept = 'E01' AND Job = 'CLERK'
 AND Salary = 5000
ORDER BY WorkDept
Messages indicating use
There are multiple ways in which a row number list probe can be indicated through the messages. The messages in this example illustrate how the SQL Query Engine will indicate a row number list probe was used.
Optimizer Debug:
CPI4328 -- Access path of file X1 was used by query.
CPI4338 -– 2 Access path(s) used for bitmap            processing of file EMPLOYEE. 
PRTSQLINF:
SQL4008 -- Index X1 used for table 1.
SQL4011 -- Index scan-key row positioning            used on table 1.
SQL4032 -- Index EVI2 used for bitmap            processing of table 1.  
SQL4032 -- Index EVI3 used for bitmap            processing of table 1. 
SMP parallel enabled Yes
Also referred to as Row Number List Probe, Preload
Visual Explain icon
Using the example above, the optimizer created a temporary row number list for each of the encoded vector indexes. Additionally, an index probe operation was performed against the radix index X1 to satisfy the ordering requirement. Since the ORDER BY clause requires that the resulting rows be sequenced by the WorkDept column, the temporary row number list can no longer be scanned to process the selected rows. However, the temporary row number list can be probed using a row address extracted from the index X1 used to satisfy the ordering. By probing the temporary row number list with the row address extracted from index probe operation, the sequencing of the keys in the index X1 is preserved and the row can still be tested against the selected rows within the row number list.

Parent topic:
Temporary row number list

Data access method	Row number list probe
Description	The temporary row number list is quickly probed based upon the row number generated by a separate operation.
Advantages	The temporary row number list only contains a rows' address, no data, so the temporary can be efficiently probed within memory The row numbers represented within the row number list are sorted to provide efficient lookup processing to test the underlying table Selection is performed as the row number list is generated to subset the number of selected rows in the temporary object
Considerations	Since the row number list only contains the addresses of the selected rows in the table, a separate Table Probe operation must be performed in order to fetch the table rows
Likely to be used	When the use of temporary results is allowed by the query environmental parameter (ALWCPYDTA) When the cost of creating and probing the row number list is justified by reducing the number of Table Probe operations that must be performed When multiple indexes over the same table need to be combined in order to minimize the number of selected rows
Example SQL statement	CREATE INDEX X1 ON Employee (WorkDept) CREATE ENCODED VECTOR INDEX EVI2 ON Employee (Salary) CREATE ENCODED VECTOR INDEX EVI3 ON Employee (Job) SELECT * FROM Employee WHERE WorkDept = 'E01' AND Job = 'CLERK' AND Salary = 5000 ORDER BY WorkDept
Messages indicating use	There are multiple ways in which a row number list probe can be indicated through the messages. The messages in this example illustrate how the SQL Query Engine will indicate a row number list probe was used. Optimizer Debug: CPI4328 -- Access path of file X1 was used by query. CPI4338 -– 2 Access path(s) used for bitmap processing of file EMPLOYEE. PRTSQLINF: SQL4008 -- Index X1 used for table 1. SQL4011 -- Index scan-key row positioning used on table 1. SQL4032 -- Index EVI2 used for bitmap processing of table 1. SQL4032 -- Index EVI3 used for bitmap processing of table 1.
SMP parallel enabled	Yes
Also referred to as	Row Number List Probe, Preload
Visual Explain icon