Background documentationSTRATEGY Column Locate this document in the navigation structure

 

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

  • Search strategy used by the SQL optimizer for the SQL statement examined.

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

    See Postponement of the Search to the FETCH Time

  • Further information from SQL Optimizer:

    EXPLAIN statement: Further information from SQL Optimizer

    Information from SQL Optimizer

    Meaning

    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.