On Windows, SET EXPLAIN ON writes the plan that the optimizer chooses for each subsequent query to file %INFORMIXDIR%\sqexpln\username.out where username is the user login.
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 lists terms that can appear in the output file and their significance.
| 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. |
| 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 contains the subquery cost. The subquery cost is shown only so you can see the cost that is associated with 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 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. 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 is 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 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. |
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.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]