Sorted list scan

During a sorted list scan operation, the entire temporary sorted list is scanned and all the entries contained within the sorted list are processed.

A sorted list scan is considered when the data values need to be sequenced. A sorted list scan allows the optimizer to generate a plan that can take advantage of any non-join selection while creating the temporary sorted list.

An additional benefit is that the data structure will usually cause the table data within the sorted list to remain resident within main memory after creation. This resident data reduces paging on the subsequent sorted list scan operation.

Table 1. Sorted list scan attributes
Data access method Sorted list scan
Description Read all the entries in a temporary sorted list. The sorted list can perform distinct processing to eliminate duplicate values or take advantage of the temporary sorted list to sequence all the rows.
Advantages
  • Reduces the random I/O to the table associated with longer running queries that would otherwise use an index to sequence the data.
  • Selection can be performed prior to generating the sorted list to subset the number of rows in the temporary object
Considerations Used to process ordering or distinct processing. 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 ordered based upon a column or columns for ordering or distinct processing
Example SQL statement 
CREATE INDEX X1 ON Employee (LastName, WorkDept)

SELECT * FROM Employee
WHERE WorkDept BETWEEN 'A01' AND 'E01'
ORDER BY FirstNme
OPTIMIZE FOR ALL ROWS
Database Monitor and Plan Cache record indicating use

QQRID 3003 Query Sort. There is no specific field that indicates whether or not the sorted list was used for a scan or a probe. Refer to Visual Explain diagram for query implementation details.
SMP parallel enabled No
Also referred to as Sorted List Scan, Preload

Sorted List Scan Distinct

Sorted List Scan Distinct, Preload
Visual Explain icon
Sorted list scan icon