Background documentationPostponement of the Search to the FETCH Time Locate this document in the navigation structure

 

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

Result of the EXPLAIN Statement

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).