WorktableN created for ORDER BY.
Queries that include an order by clause often require the use of a temporary worktable. When the optimizer cannot use an index to order the result rows, it creates a worktable to sort the result rows before returning them. This example shows an order by clause that creates a worktable because there is no index on the city column:
select * from authors order by city
QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is INSERT. The update mode is direct. Worktable1 created for ORDER BY. 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.
Certain queries using order by do not require a sorting step, depending on the type of index used to access the data.
See Chapter 8, “Indexing for Performance,” for more information.