informix
Informix Guide to SQL: Syntax
SQL Statements

SET EXPLAIN

Use the SET EXPLAIN statement to display the query plan the optimizer chooses, an estimate of the number of rows returned, and a relative cost of the query.

Syntax

Element Purpose Restrictions Syntax
expr Expression that evaluates to a filename The filename that is derived from the expression must be usable. The same restrictions apply to the derived filename as to the filename parameter. Expression, p. 4-73
filename Quoted string that identifies the path and filename of the file that contains the output of the SET EXPLAIN FILE TO statement For information on the default actions that are taken if you omit the pathname, see Location of the Output File. You can specify a new or existing file. If you specify an existing file, you must include the WITH APPEND keywords if you want to preserve the current contents of the file intact. For further information, see Using the WITH APPEND Option. Quoted String, p. 4-260. Name must conform to the naming conventions of your operating system.
filename_var Host variable that holds the value of filename The host variable must be a character data type. Name must conform to language-specific rules for variable names.

Usage

The SET EXPLAIN statement provides various measurements of the work involved in performing a query.

Option Purpose
ON

Generates measurements for each subsequent query and writes the results to an output file in the current directory. If the file already exists, new explain output is appended to the existing file.

OFF

Terminates the SET EXPLAIN statement so that measurements for subsequent queries are no longer generated or written to the output file.

FILE TO

Generates measurements for each subsequent query and allows you to specify the location for the explain output file. If the file already exists, new explain output overwrites the contents of the file unless you use the WITH APPEND option.

Persistence and Default Behavior

When you issue a SET EXPLAIN ON statement, the output is directed to the appropriate file until you issue a SET EXPLAIN OFF statement or until the program ends. If you do not enter a SET EXPLAIN statement, the default behavior is OFF. The database server does not generate measurements for queries.

Execution of the SET EXPLAIN Statement

The SET EXPLAIN statement executes during the database server optimization phase, which occurs when you initiate a query. For queries that are associated with a cursor, if the query is prepared and does not have host variables, optimization occurs when you prepare it. Otherwise, optimization occurs when you open the cursor.

Using the FILE TO option

When you execute a SET EXPLAIN FILE TO statement, explain output is implicitly turned on. The default filename for the output is sqexplain.out until changed by a SET EXPLAIN FILE TO statement. Once changed, the filename remains set until the end of the session or until changed by another SET EXPLAIN FILE TO statement.

The filename may be any valid combination of optional path and filename. If no path component is specified, the file is placed in your current directory. The permissions for the file are owned by the current user.

Using the WITH APPEND Option

The output file that you specify in the SETEXPLAIN statement can be a new file or an existing file.

If you specify an existing file, the current contents of the file are purged when you issue the SET EXPLAIN FILE TO statement. The first execution of a FILE TO command sends output to the beginning of the file.

However, if you include the WITH APPEND option, the current contents of the file are preserved when you issue the SET EXPLAIN FILE TO statement. The execution of a WITH APPEND command appends output to the end of the file.

If you specify a new file in the SET EXPLAIN FILE TO statement, it makes no difference whether you include the WITH APPEND option. The first execution of the command sends output to the beginning of the new file.

Default Name and Location of the Output File

On UNIX, when you issue a SET EXPLAIN ON statement, the plan that the optimizer chooses for each subsequent query is written to the sqexplain.out file by default.

If the output file does not exist when you issue the SET EXPLAIN ON statement, the database server creates the output file. If the output file already exists when you issue the SET EXPLAIN ON statement, subsequent output is appended to the file.

If the client application and the database server are on the same computer, the sqexplain.out file is stored in your current directory. If you are using a version 5.x or earlier client application and the sqexplain.out file does not appear in the current directory, check your home directory for the file.When the current database is on another computer, the sqexplain.out file is stored in your home directory on the remote host.

On Windows NT, when you issue a SET EXPLAIN ON statement, the plan that the optimizer chooses for each subsequent query is written to the file %INFORMIXDIR%\sqexpln\username.out where username is the user login.

SET EXPLAIN Output

By examining the SET EXPLAIN output file, you can determine if steps can be taken to improve the performance of the query.

The following table contains terms that can appear in the output file and their significance.

Term Significance
Query Displays the executed query. Indicates whether SET OPTIMIZATION was set to high. If you SET OPTIMIZATION to LOW, the output of SET EXPLAIN displays the following uppercase string as the first line: QUERY:{LOW} If you SET OPTIMIZATION to HIGH, the output of SET EXPLAIN displays the following uppercase string as the first line: QUERY:
Directives followed Lists the directives set for the executed query. If the syntax for a directive is incorrect, the query is processed without the directive. In that case, the output will show DIRECTIVES NOT FOLLOWED in addition to DIRECTIVES FOLLOWED. For more information on the directives specified after this term, see the Optimizer Directives or SET OPTIMIZATION.
Estimated Cost An estimate of the amount of work for the query. The optimizer uses an estimate to compare the cost of one path with another. The estimated cost is a number the optimizer assigns to the selected access method. The estimated cost does not translate directly into time, and cannot be used to compare different queries. However, it can be used to compare changes made for the same query. When data distributions are used, a query with a higher estimate generally takes longer to run than one with a smaller estimate. In the case of a query and a subquery, two estimated cost figures are returned; the query figure also contains the subquery cost. The subquery cost is shown only so you can see the cost that is associated with the subquery.
Estimated # of Rows Returned An estimate of the number of rows to be returned. This number is based on the information in the system catalog tables
Numbered List The order in which tables are accessed, followed by the access method used (index path or sequential scan). When a query involves table inheritance, all of the tables are listed under the supertable in the order they were accessed.
Index Keys The columns used as filters or indexes; the column name used for the index path or filter is indicated. The notation (Key Only) indicates that all of the desired columns are part of the index key, so a key-only read of the index could be substituted for a read of the actual table. The Lower Index Filter shows the key value where the index read begins. If the filter condition contains more than one value, an Upper Index Filter would be shown for the key value where the index read stops.
Join Method When the query involves a join between two tables, the join method the optimizer used (Nested Loop or Dymanic Hash) is shown at the bottom of the output for that query. When the query involves a dynamic join of two tables, if the output contains the words Build Outer, the hash table is built on the first table listed (called the build table). If the words Build Outer do not appear, the hash table is built on the second table listed.

Related Information

Related statements: SET OPTIMIZATION and UPDATE STATISTICS

For discussions of SET EXPLAIN and of analyzing the output of the optimizer, see your Performance Guide.

Informix Guide to SQL: Syntax, Version 9.2
Copyright © 1999, Informix Software, Inc. All rights reserved