Row number list scan

Row number list scan

During a row number list scan operation, the entire temporary row number list is scanned and all of the row addresses contained within the row number list will be processed. A row number list scan is generally considered when the optimizer is considering a plan that involves an encoded vector index or if the cost of the random I/O associated with an index probe or scan operation can be reduced by first preprocessing and sorting the row numbers associated with the Table Probe operation.
The use of a row number list scan allows the optimizer to generate a plan that can take advantage of multiple indexes to match up to different portions of the query.
An additional benefit of using a row number list scan is that the data structure of the temporary row number list guarantees that the row numbers are sorted, it closely mirrors the row number layout of the table data ensuring that the paging on the table will never revisit the same page of data twice. This results in increased I/O savings for the query.
A row number list scan is identical to a bitmap scan operation. The only difference between the two operations is that a row number list scan is performed over a list of row addresses while the bitmap scan is performed over a bitmap that represents the row addresses.
Table 1. Row number list scan
Data access method Row number list scan
Description Sequentially scan and process all of the row numbers in the temporary row number list. The sorted row numbers can be merged with other temporary row number lists or can be used as input into a Table Probe operation.
Advantages

The temporary row number list only contains address, no data, so the temporary can be efficiently scanned within memory

The row numbers contained within the temporary object are sorted to provide efficient I/O processing to access the underlying table

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

Considerations Since the row number list only contains the addresses of the selected row 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 sorting of the row number is justified by the more efficient I/O that can be performed during the Table Probe operation

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
OPTIMIZE FOR 99999 ROWS
Messages indicating use
There are multiple ways in which a row number list scan can be indicated through the messages. The messages in this example illustrate how the SQL Query Engine will indicate a row number list scan was used.
Optimizer Debug:
CPI4329 -- Arrival sequence was used for file            EMPLOYEE.
CPI4338 -– 3 Access path(s) used for bitmap            processing of file EMPLOYEE.
PRTSQLINF:
SQL4010 -- Table scan access for table 1.
SQL4032 -- Index X1 used for bitmap            processing of 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 Scan, Preload
Visual Explain icon
Using the example above, the optimizer created a temporary row number list for each of the indexes used by this query. This query used a combination of a radix index and two encoded vector indexes to create the row number lists. The temporary row number lists for each index was scanned and merged into a final composite row number list that represents the intersection of the rows represented by all of the temporary row number lists. The final row number list is then used by the Table Probe operation to determine what rows are selected and need to be processed for the query results.

Parent topic:
Temporary row number list

Data access method	Row number list scan
Description	Sequentially scan and process all of the row numbers in the temporary row number list. The sorted row numbers can be merged with other temporary row number lists or can be used as input into a Table Probe operation.
Advantages	The temporary row number list only contains address, no data, so the temporary can be efficiently scanned within memory The row numbers contained within the temporary object are sorted to provide efficient I/O processing to access the underlying table Selection is performed as the row number list is generated to subset the number of rows in the temporary object
Considerations	Since the row number list only contains the addresses of the selected row 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 sorting of the row number is justified by the more efficient I/O that can be performed during the Table Probe operation 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 OPTIMIZE FOR 99999 ROWS
Messages indicating use	There are multiple ways in which a row number list scan can be indicated through the messages. The messages in this example illustrate how the SQL Query Engine will indicate a row number list scan was used. Optimizer Debug: CPI4329 -- Arrival sequence was used for file EMPLOYEE. CPI4338 -– 3 Access path(s) used for bitmap processing of file EMPLOYEE. PRTSQLINF: SQL4010 -- Table scan access for table 1. SQL4032 -- Index X1 used for bitmap processing of 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 Scan, Preload
Visual Explain icon