SORT Distinct operator

The SORT Distinct operator does not require that its input stream is already sorted on the distinct key columns. It is a blocking operator that drains its child operator’s stream and sorts the rows as they are read. A distinct row is returned to the parent operator after all rows have been sorted. Rows are returned sorted on the distinct key columns. An internal worktable is used as a backing store in case the input set does not fit entirely in memory.

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 
	| Using Worktable1 for internal storage.
	|
	|   |SCAN Operator
	|   |  FROM TABLE
	|   |  authors
	|   |  Table Scan.
	|   |  Forward Scan.
	|   |  Positioning at start of table.
	|   |  Using I/O Size 2 Kbytes for data pages.
	|   |  With LRU Buffer Replacement Strategy for data pages.

The scan of the authors table does not return rows sorted on the distinct key columns. This requires that a SORT Distinct operator be used rather than a GROUP SORTED Distinct operator. The SORT operator’s distinct key columns are au_lname and au_fname. The showplan output indicates that Worktable1 is used for disk storage in case the input set does not fit entirely in memory.