Show TOC

EXPLAIN Statement (explain_statement)Locate this document in the navigation structure

Definition

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.

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

Examples

SQL Optimizer, Displaying Search Strategies Used for SQL Statements (EXPLAIN)

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. The sequence in which the SELECT is processed is described by the order of the rows in the result table.

Table 1: Structure of the EXPLAIN Result Table

Column

Description

Data Type

SCHEMANAME

Schema name/owner

CHAR (64)

TABLENAME

Table Name

CHAR(64)

COLUMN_OR_INDEX

Shows which key column, inverted column or index is used by the system for the strategy

CHAR(64)

STRATEGY

Shows which search strategy or strategies the system uses and whether the system generates a result table.

The system generates a result table when RESULT IS COPIED is contained in the last result line of the STRATEGY column.

More information: SQL Optimizer,

CHAR(40)

PAGECOUNT

Cost determination result for the selected search strategy

This column shows which sizes are assumed for the tables or, in the case of certain strategies, for the indexes.

These sizes affect which search strategy is selected by the system.

To update the defined sizes, use the UPDATE STATISTICS statement or the relevant database tool function.

You can query the defined sizes by selecting the system table OPTIMIZERINFORMATION. You can query the current sizes of tables or indexes by selecting the TABLESTORAGEDETAILS and INDEXSTORAGEDETAILS system tables. When you discover substantial differences between the values in OPTIMIZERINFORMATION and TABLESTORAGEDETAILS, then execute the UPDATE STATISTICS statement for these tables.

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 table columns 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 and ALTER USER statements).

CHAR(10)

EXTERNAL

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

An EXPLAIN EXTERNAL statement returns the following result set:

Column

Description

Data Type

STATEMENTNUMBER

Number n indicates that the SQL statement is the n-th entry in the external SQL statement information. This number corresponds to the number n found in EXPLAIN result table in STRATEGY column

FIXED(10)

STATEMENT

SQL statement that will be executed at the data source

CLOB UNICODE

EXECUTIONPLAN

Result of the EXPLAIN EXTERNAL statement for the SQL statement at the data source

CLOB UNICODE

The EXECUTIONPLAN content is formatted as described in Database Manager CLI, sql_execute.

More Information