WorktableN created for DISTINCT.
A query that includes the distinct keyword excludes all duplicate rows from the results so that only unique rows are returned. When there is no useful index, Adaptive Server performs these steps to process queries that include distinct:
It creates a worktable to store all of the results of the query, including duplicates.
It sorts the rows in the worktable, discards the duplicate rows, and then returns the rows.
Subqueries with existence joins sometimes create a worktable and sort it to remove duplicate rows.
See “Flattening in, any, and exists subqueries” on page 145 for more information.
The “WorktableN created for DISTINCT” message appears as part of “Step 1” in showplan output. “Step 2” for distinct queries includes the messages “This step involves sorting” and “Using GETSORTED”. See “Sorting messages” on page 812.
select distinct city from authors
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable1 created for DISTINCT.
FROM TABLE
authors
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 2
The type of query is SELECT.
This step involves sorting.
FROM TABLE
Worktable1.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
