Background documentationEXPLAIN Statement Locate this document in the navigation structure

 

For an SQL statement with certain search conditions, you can use the EXPLAIN statement to find out which search strategy the SQL Optimizer selects for this SQL statement and what costs this search strategy incurs.

Syntax

EXPLAIN <query_statement>

Variables

Description

<query_statement>

SQL statement

Result
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 TABLESTATISTICS and INDEXSTATISTICS system tables. When you discover substantial differences between the values in OPTIMIZERINFORMATION and TABLESTATISTICS, 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)

Example

This example uses the demo database DEMODB with the complete demo data in the schema HOTEL (see Concepts of the Database System, Objects in the Schema HOTEL

Example Example

EXPLAIN SELECT *

  FROM hotel.city

    WHERE zip > '99000'

End of the example.
Result of the EXPLAIN statement

OWNER

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

HOTEL

CITY

RANGE CONDITION FOR KEY

11

ZIP

(USED KEY COLUMN)

RESULT IS NOT COPIED, COSTVALUE IS

1

The CITY table consists of 11 pages and I/O costs for executing the SQL statement take up 1 page.

You can find additional simple example SQL statements in the SQL Tutorial.