Show TOC

Displaying the Search Strategies Used by SQL Statements (EXPLAIN)Locate this document in the navigation structure

Use

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

Note

To select the best search strategy, the SQL Optimizer uses statistical table information. Refresh the statistical information regularly (see Updating the SQL Optimizer Statistics).

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 EXPLAIN SQL statement (see SQL Reference Manual, EXPLAIN Statement (explain_statement)).

Procedure

  1. In the context menu of the database user, choose SQL Editor.

  2. Enter the SQL statement (see Entering and Executing SQL Statements).

  3. In the context menu of the SQL editor, choose Explain SQL.

Result
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)
More Information