Worktable created for ORDER BY

As discussed previously, queries that include an ORDER BY clause often require the use of a temporary worktable. When the optimizer cannot use an available index for the ordering, it creates a worktable for use in sorting the result rows prior to returning them.

The following example shows the worktable being created for the ORDER BY clause:

Query:

SELECT *
FROM authors
ORDER BY city

SHOWPLAN:

STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for ORDER BY
FROM TABLE
authors
Nested iteration
Table Scan
TO TABLE
Worktable 1

STEP 2
The type of query is SELECT
This step involves sorting
FROM TABLE
Worktable 1
Using GETSORTED Table Scan