Procedure documentationAnalyzing SQL Statements (EXPLAIN) Locate this document in the navigation structure

 

You can use the EXPLAIN statement to find out which search strategy the SQL Optimizer selected for an SQL statement in the database system.

Note Note

To select the best search strategy, the SQL Optimizer uses statistical table information. Refresh the statistical information regularly.

More information: Updating the SQL Optimizer Statistics

End of the note.

Prerequisites

  • You are logged on to the database as a database user.

  • You have the necessary privileges for the tables you are accessing with the SQL statement.

    More information: SQL Reference Manual, Privileges: Overview

Procedure

  1. Choose SQL Editor in the context menu of the database user who is to execute the SQL statement.

  2. Enter the SQL statement.

    More information: Entering and Executing SQL Statements

  3. To analyze the SQL statement, choose Explain SQL in the context menu of the SQL editor.

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