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 first results can be accessed quickly.

Results Table is Created

However, you cannot always postpone the search to the FETCH time, since some situations always require a results table to be created.

●     SELECTfor multiple tables (Join)

●     SELECT ... FOR REUSE

A results table usually needs to be created in the following cases:

●     SELECT DISTINCT ...

●     SELECT ... ORDER BY ...

Results Table is not Created

If all of the following conditions apply, no results table needs to be created:

●     Neither a DISTINCT specification (with a few exceptions), nor the syntax element FOR REUSE, are specified.

●     The columns that you are sorting by constitute an index in the specified order and with the specified sort order (ascending or descending).

Examples

The examples used to explain the SQL Optimizer are based on the demo database DEMODB and its demo data in the schema HOTEL.

Concepts of the Database System, Objects in the Schema HOTEL

You can use the EXPLAIN statement to find out whether a results table is created (entry in the column STRATEGY RESULT IS COPIED) or not (entry in the column STRATEGY: RESULT IS NOT COPIED).

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 the table CITY: zip
Indexes of qualified columns: none
Search strategy used: TABLE SCAN

Result of the EXPLAIN Statement

TABLENAME	COLMUN_OR_INDEX	STRATEGY	PAGECOUNT
CITY		TABLE SCAN	11
		RESULT IS COPIED COSTVALUE IS	3

A results table is created (RESULT IS COPIED).

You will find additional simple example SQL statements in the SQL Tutorial.

See also:

Search Strategies

Search strategies for Joins

List of All Search Strategies

SQL Reference Manual, FETCH Statement (fetch_statement), DISTINCT Specification (distinct_spec), SELECT Statement (select_statement)