The SortOp (Distinct) operator is a unary operator. It 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.
1> select distinct au_lname, au_fname 2> from authors 3> where city = "Oakland" 2 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |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 SortOp (Distinct) operator be used rather than a GroupSorted (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.
