Entering content frame

Background documentation STRATEGY Column Locate the document in the library structure

The result of the EXPLAIN statement is a table that consists of multiple columns. In the STRATEGY column, you will find the following:

The search strategy that the SQL Optimizer uses for the SQL statement being examined
See also: List of all Search Strategies

An entry as to whether a results table is created (RESULT IS COPIED) or not (RESULT IS NOT COPIED)
See also: Postponement of the Search to the FETCH Time

     Information of the SQL Optimizer

Information of the SQL Optimizer

Description

ONLY INDEX ACCESSED

Only the specified index is used to process the SQL statement; the data in the basis table is not accessed. This is possible only if the SQL statement addresses only those columns that are included in the index structure.

DISTINCT OPTIMIZATION (A)

The complete key is included in the SELECT list/output columns. This means that the result is automatically free from duplicates.

DISTINCT OPTIMIZATION (C)

Complete secondary key
After the key word DISTINCT, all columns of an index (and only these columns) are specified in a random order (DISTINCT specification). The system accesses the values for each index column only once. No results table is created.

SELECT DISTINCT <all_columns_of_index> FROM ...

DISTINCT OPTIMIZATION (P)

Partial secondary key
After the key word DISTINCT, the first k (where k < total number of index columns) columns of an index are specified in a random order. The system accesses the values for each index column only once. No results table is created.

SELECT DISTINCT <first_k_columns_of_index> FROM ...

DISTINCT OPTIMIZATION (K)

Primary key
After the key word DISTINCT, all columns of an index and the first k (where k < total number of index columns) columns of the key are specified in a random order. The system accesses the values for the corresponding index and key columns only once. No results table is created.

SELECT DISTINCT <all_columns_of_index_+_first_k_columns_of_key> FROM ...

TEMPORARY INDEX CREATED

A temporary index is created internally, in which the keys of the hit rows determined by the corresponding index columns are stored in ascending order. The system accesses the basis table using this temporary index.

ADDNL. QUALIFICATION ON INDEX

There are search conditions for index or key columns that cannot be used for the direct containment of the range for accessing an index. The usage of these search conditions for restricting access to the basis table makes them part of the corresponding index strategy

See also:

Search Strategies

SQL Reference Manual, EXPLAIN Statement (explain_statement), DISTINCT Specification (distinct_spec)

 

Leaving content frame