SORT operator

The SORT operator has only one child operator within the query plan. Its role is to generate an output data stream from the input stream, using a specified sorting key.

The SORT operator may execute a streaming sort when possible, but may also have to store results temporarily into a worktable. The SORT operator displays the worktable’s name in this format:

Using Worktable<N>  for internal storage.

where <N> is a numeric identifier for the worktable within the showplan output.

Here is an example of a simple query plan using a SORT operator and a worktable:

select au_id from authors order by postalcode

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is SELECT.

2 operator(s) under root

ROOT:EMIT Operator (VA = 2)

    |SORT Operator (VA = 1)
    | Using Worktable1 for internal storage.
    |
    |    |SCAN Operator (VA = 0)
    |    | FROM TABLE
    |    | authors
    |    | Table Scan.
    |    | Forward Scan.
    |    | Positioning at start of table.
    |    | Using I/O Size 4 Kbytes for data pages.
    |    | With LRU Buffer Replacement Strategy for data pages.

The SORT operator drains its child operator and sorts the rows. In this case, it sorts each row fetched from the authors table using the postalcode attribute. If all of the rows fit into memory, then no data is spilled to disk. But, if the input data’s size exceeds the available buffer space, then sorted runs are spilled to disk. These runs are recursively merged into larger sorted runs until there are fewer runs than there are available buffers to read and merge the runs with.