SET EXPLAIN output

View the SET EXPLAIN output file to analyze information on an executed query, including the directives set for the query, an estimate of the cost of the query, an estimate of the number of returned rows, the order in which the server accessed tables, index keys, join methods, and query statistics.

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

Table 1. Output file terms
Term	Significance
Query	Displays the executed query and indicates whether SET OPTIMIZATION was set to HIGH or LOW. If you SET OPTIMIZATION to LOW, the output 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 query If the syntax for a directive is incorrect, the query is processed without the directive. In that case, the output shows `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 statement. If a DELETE or UPDATE statement specifies an uncorrelated subquery in the WHERE clause, the set of qualifying rows returned by the subquery is materialized as a temporary table, and the output of SET EXPLAIN displays within parentheses the following message: `(Temp Table For Subquery)`
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 estimate is a number the optimizer assigns to the selected access method. This number does not translate directly into time and cannot be used to compare different queries. It can be used, however, 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 includes the subquery cost. The subquery cost is shown so that you can see the cost that is associated with only the subquery.
Estimated number of rows returned	An estimate of the number of rows to be returned This number is based on 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 the tables are listed under the supertable in the order in which they were accessed.
Index name	The name of the index For example, `idx1` is the name of the following index: `Index Name: informix.idx1` `FOT` in the index name identifies the index as a forest of trees index: For example, the following index is a forest of trees index: Index Name: informix.fot_idx (FOT)
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 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. In databases that have the NLSCASE INSENSITIVE property, all index scan methods (except key-only scans) allow the query execution plan to map all case-sensitive values to a single value for NCHAR and NVARCHAR columns. For more on NLSCASE INSENSITIVE databases, see Duplicate rows in NLSCASE INSENSITIVE databases. The Lower Index Filter shows the key value where the index read begins; and the Upper Index Filter is shown for the key value where the index read stops. The Index Key Filters show filters that will be applied on retrieved index key values. If the query uses an index self-join path, the Index Self Join Keys shows the leading index key columns used as self-join keys, and the Lower bound and Upper bound show the boundaries of the leading index key columns.
Join method	When the query involves a join between two tables, the join method that the optimizer used (Nested Loop or Dynamic 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.
Query statistics	When the EXPLAIN_STAT configuration parameter is set to 1, this section shows the number of rows returned, the number of rows estimated in the query plan, the time required, calls to iterator functions, and the estimated cost of scan and join operations on table objects.
Time	When the output displays the elapsed time for a query execution plan or for a component of that plan, the value is formatted as `minutes:seconds.fraction` to display the minutes, seconds, and fractional part of a second.

If the query uses a collating order other than the default for the DB_LOCALE setting, then the DB_LOCALE setting and the name of the other locale that is the basis for the collation in the query (as specified by the SET COLLATION statement) are both included in the output file. Similarly, if an index is not used because of its collation, the output file indicates this.