Show TOC

STRATEGY ColumnLocate this document in the navigation structure

Use

The result of the EXPLAIN statement is a table that consists of multiple columns. The STRATEGY column contains the following information:

  • Search strategy that is used by the SQL optimizer for the SQL statement

    For more information, see List of All Search Strategies.

  • Information on whether the database system creates a results table ( RESULT IS COPIED) or not ( RESULT IS NOT COPIED).

    For more information, see Postponement of the Search to the FETCH Time.

  • Further information from the SQL optimizer:

    Information from the SQL Optimizer

    Description

    ONLY INDEX ACCESSED

    The database system only uses the specified index for executing the SQL statement and does not access the data in the base table. This is possible only if, in the SQL statement, columns of this type have been specified 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) were specified in an arbitrary order in the SQL statement ( DISTINCT specification):

    SELECT DISTINCT <all_index_colums> FROM ...

    The database system only accesses the values of the corresponding index columns once and does not create a results table.

    DISTINCT OPTIMIZATION (P)

    Partial Secondary Key

    After the key word DISTINCT, the first k ( k < total number of columns of an index) columns of an index were specifed in an arbitrary order:

    SELECT DISTINCT <first_k_index_columns> FROM ...

    The database system only accesses the values of the corresponding index columns once and does not create a results table.

    DISTINCT OPTIMIZATION (K)

    Primary Key

    After the key word DISTINCT, all columns of an index and the first k ( k <= total number of columns of key) columns of the key were specified in an arbitrary order:

    SELECT DISTINCT <all_index_columns_+_first_k_key_columns> FROM ...

    The database system only accesses the values of the corresponding index and key columns once and does not create a results table.

    TEMPORARY INDEX CREATED

    The database system creates an internal temporary index in which the keys of the hit rows determined by the corresponding index columns are stored in ascending order. The database system then accesses the base table via this temporary index.

    ADDNL. QUALIFICATION ON INDEX

    In the SQL statement, search conditions were specified for index columns or key columns that cannot use the database system for direct restriction of the range for index access. The database system uses the search conditions in the corresponding index strategy in order to limit access to the base table.

  • The following information is only relevant for SAP MaxDB bridge functionality.

    For externally executed SQL statements, there is only the following information in the STRATEGY column: SELECT FOR EXTERNAL TABLE CALLED (n).

    An SQL statement is executed at a data source to produce the query result. The SQL statement is the n-th entry in the external statement information that can be retrieved by an EXPLAIN EXTERNAL statement.