List scan

 

The list scan operation is used when a portion of the query will be processed multiple times, but no key columns can be identified. In these cases, that portion of the query is processed once and its results are stored within the temporary list. The list can then be scanned for only those rows that satisfy any selection or processing contained within the temporary object.

Table 1. List scan attributes
Data access method List scan
Description Sequentially scan and process all of the rows in the temporary list.
Advantages

  • The temporary list and list scan can be used by the optimizer to minimize repetition of an operation or to simplify the optimizer's logic flow

  • Selection can be performed before generating the list to subset the number of rows in the temporary object
Considerations Generally used to prevent portions of the query from being processed multiple times when no key columns are required to satisfy the request.
Likely to be used

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

  • When Symmetric Multiprocessing will be used for the query
Example SQL statement 
SELECT * FROM Employee XXX, Department YYY WHERE XXX.LastName IN ('Smith', 'Jones', 'Peterson')
AND YYY.DeptNo BETWEEN 'A01' AND 'E01'
OPTIMIZE FOR ALL ROWS
Messages indicating use There are multiple ways in which a list scan can be indicated through the messages. The messages in this example illustrate how the SQL Query Engine will indicate a list scan was used.

  • Optimizer Debug: 
    CPI4325 -- Temporary result file built for query.
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.
SQL4001 -- Temporary result created SQL4010 -- Table scan access for table 2.
SMP parallel enabled Yes
Also referred to as List Scan, Preload
Visual Explain icon

List scan icon

Using the example above, the optimizer chose to create a temporary list to store the selected rows from the DEPARTMENT table. Since there is no join criteria, a cartesian product join is performed between the two tables. To prevent the join from scanning all of the rows of the DEPARTMENT table for each join possibility, the selection against the DEPARTMENT table is performed once and the results are stored in the temporary list. The temporary list is then scanned for the cartesian product join.

 

Parent topic:

Temporary list