Postponement of the Search to the FETCH Time 
As far as possible, the SQL Optimizer creates results tables only when it is absolutely necessary.
If no results table needs to be created, the search can be postponed to the FETCH time. In this way, no memory is used for results and the database can access the first results 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:
In the SQL statement, neither a DISTINCT (apart from a few exceptions) nor the syntax element FOR REUSE was specified.
The columns to be used for the search constitute an index in the specified order and with the specified sort order (ascending or descending).
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
The EXPLAIN statement allows you to find out whether the database system creates a results table (entry in the STRATEGY column: RESULT IS COPIED) or not (entry in column STRATEGY: RESULT IS NOT COPIED).
Example
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).
You can find additional simple example SQL statements in the SQL Tutorial.
More Information
Search Strategies for Joins
SQL Reference Manual, FETCH Statement (fetch_statement), DISTINCT Specification (distinct_spec), SELECT Statement (select_statement)