EXPLAIN Statement (explain_statement) 

The EXPLAIN statement (explain_statement) describes the search strategy used internally by the database system for a QUERY statement or SINGLE SELECT statement (statements for searching for certain rows in specific tables). This statement indicates in particular whether and in which form key columns or indexes are used for the search.

Syntax

<explain_statement> ::=
  EXPLAIN [(<result_table_name>)] <query_statement>
| EXPLAIN [(<result_table_name>)] <single_select_statement>

Examples

Concepts of the Database System, EXPLAIN Statement

Explanation

The explain statement can be used to check the effect that creating or deleting indexes has on the choice of search strategy for the specified SQL statement. It is also possible to estimate the time needed by the database system to process the specified SQL statement. The specified QUERY or SINGLE SELECT statement is not executed while the EXPLAIN statement is being executed.

The EXPLAIN statement generates a result table. This result table may be named. If the optional name specification is missing, the result table is given the name SHOW.

Structure of the EXPLAIN Result Table

SCHEMANAME	CHAR (64)
TABLENAME	CHAR(64)
COLUMN_OR_INDEX	CHAR(64)
STRATEGY	CHAR(40)
PAGECOUNT	CHAR(10)

The sequence in which the SELECT is processed is described by the order of the rows in the result table.

COLUMN_OR_INDEX

The COLUMN_OR_INDEX column shows which key column or inverted column or which index is used for the strategy.

STRATEGY

The STRATEGY column shows which search strategy/ies is/are used and whether a result table is generated. A result table is generated physically if the STRATEGY column contains RESULT IS COPIED in the last result row.

See also:

Concepts of the Database System, STRATEGY Column, List of all Search Strategies

PAGECOUNT

The PAGECOUNT column shows which sizes are assumed for the tables or, in the case of certain strategies, for the indexes. These sizes influence the choice of the search strategy.

The assumed sizes are updated using the UPDATE STATISTICS statement and can be requested by selecting the system table OPTIMIZERINFORMATION. You can check the current sizes of tables or indexes by selecting the TABLESTATISTICS and INDEXSTATISTICS system tables. If there are large discrepancies between the values contained in the OPTIMIZERINFORMATION and TABLESTATISTICS, the update statistics statement should be performed for this table.

If the system discovers during a search in a table that the values determined by the last UPDATE STATISTICS statement are extremely low, a row is entered in the SYSUPDSTATWANTED system table that contains the table name. In all other cases, rows are entered in this system table that describe columns in tables. The UPDATE STATISTICS statement should be executed for tables and columns in tables that are described in the SYSUPDSTATWANTED system table.

The last row contains the estimated SELECT cost value in the PAGECOUNT column. This estimated SELECT cost value forms the basis of the COSTLIMIT and COSTWARNING information in the SQL statements for creating and changing users (such as the CREATE USER statement, ALTER USER statement).

See also:

Concepts of the Database System, SQL Optimizer

Database Administration in CCMS, SQL Optimizer in CCMS