Temporary sorted list
The temporary sorted list is a temporary object that allows the optimizer to sequence rows based upon a column or set of columns. The sorted list can be either scanned or probed by the optimizer to satisfy different operations of the query.
A temporary sorted list is a data structure where the rows are organized for quick and easy retrieval after population has occurred. During population, the rows are copied into the temporary object and then a second pass is made through the temporary object to perform the sort. In order to optimize the creation of this temporary object, minimal data movement is performed while the sort is processed. It is generally not as efficient to probe a temporary sorted list as it is to probe a temporary hash table.
Additionally, the sorted list can be populated with all of the necessary columns to satisfy any further processing, avoiding any random I/Os associated with a Table Probe operation. However, the optimizer does have the ability to selectively include columns in the sorted list when the calculated size will exceed the memory pool storage available for this query. In those cases, a Table Probe operation is required to recollect the missing columns from the sorted list before the selected rows can be processed.
A temporary sorted list is an internal data structure and can only be created by the database manager.
Visual explain icon:
- Sorted list scan
During a sorted list scan operation, the entire temporary sorted list is scanned and all of the entries contained within the sorted list will be processed. - Sorted list probe
A sorted list probe operation is used to retrieve rows from a temporary sorted list based upon a probe lookup operation.