EXPLAIN Statement 

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>

The result of an EXPLAIN statement is a table.

●     The STRATEGY column shows you which search strategy the SQL Optimizer selected for this SQL statement and whether a result table was created or not.

●     The PAGECOUNT column shows you the result of the cost determination for the chosen search strategy.

Examples

The examples used to explain the SQL Optimizer are based on the demo database DEMODB and its complete demo data in the schema HOTEL.

Concepts of the Database System, Objects in the Schema HOTEL

EXPLAIN
  SELECT * FROM hotel.city
    WHERE zip > '99000'

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

This means that the table CITY has 11 pages and the I/O costs for executing this SQL statement amount to 1 page.

Some special features of the EXPLAIN statement are explained in the following sections:

EXPLAIN Statement for Joins

EXPLAIN Statement for Complex SELECT Statements

EXPLAIN Statement for SELECT Statements with Subqueries

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

See also:

List of All Search Strategies

Postponement of the Search to the FETCH Time

Problem Analysis

SQL Reference Manual, EXPLAIN Statement (explain_statement)