Postponement of the Search to the FETCH Time 
The SQL optimizer always tries to create as few results tables as possible. If it does not need to create a results table for an SQL statement, it can postpone the search to the FETCH time. In this way, no memory is used for the results and the database system can access the first records in the result quickly.
The database system always needs to create a results table in the following cases:
SELECT for multiple tables (Join)
SELECT ... FOR REUSE
The database system usually needs to create a results table in the following cases:
SELECT DISTINCT ...
SELECT ... ORDER BY ...
If each of the following conditions is met, the database system does not need to create a results table:
Neither a DISTINCT (apart from a few exceptions) nor the syntax element FOR REUSE was specified.
The columns by which the result is to be sorted constitute an index in the specified order and specified sort order (ascending or descending).
The EXPLAIN statement allows you to find out whether the database system creates a results table (entry in the STRATEGY column):
RESULT IS COPIED (results table is created)
RESULT IS NOT COPIED (no results table is created)
Example
The following example uses the demo database DEMODB with the complete demo data in the schema HOTEL, see Concepts of the Database System, Objects in the Schema HOTEL.
SQL Statement for Which the System Creates a Result Table
EXPLAIN SELECT zip, name
FROM hotel.city
WHERE ROWNO < 5
ORDER BY name, zip DESC
Qualification: ROWNO < 5 ORDER BY name, zip DESC
Primary key of CITY table: zip
Indexes via qualified columns: None
Search strategy: TABLE SCAN
TABLENAME |
COLUMN_OR_INDEX |
STRATEGY |
PAGECOUNT |
|---|---|---|---|
CITY |
TABLE SCAN |
11 |
|
RESULT IS COPIED COSTVALUE IS |
3 |
The database system creates a results table (RESULT IS COPIED).