Example Explanation of an Informix Statement
You can use the SQL Trace facility to view explanations of specific Informix statements. From within a trace file display, you use the Explain SQL function to display more information about a specific database request. The Explain function is available only for PREPARE and REOPEN operations. To analyze a statement:
The Explain screen shows you the database's strategy for carrying out the selected operation.
If you are working with an Informix database and you display the explanation for the following statement:
select owner from systables where tabname = 'atab'
The system provides the following explanation:
Execution plan of a select statement (Online Optimizer)
QUERY:
SELECT OWNER
FROM SYSTABLES
WHERE TABNAME = ‘ ATAB’
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) informix.systables: INDEX PATH
(1) Index Keys: tabname owner (Key-Only)
Lower Index Filter: informix.systables.tabname = ‘ ATAB’
The fields in the explanation have the following meanings:
QUERY |
Identifies the SQL statement that was traced. |
Estimated Cost |
Estimates the database expenditure required to execute the statement. The cost-based optimizer estimates this value in terms of the I/O and CPU required by the statement. The larger the Estimated Cost the greater the expenditure. |
Estimated # of Rows Returned: |
Estimates the number of table rows that the SQL statement will return. |
Immediately below the number of rows returned is the selected execution plan. In the above example, the execution plan is as follows:
1) informix.systables: INDEX PATH
The
1) indicates that the system processes the systables table as the first step of the execution plan. For queries that span several tables (views and joins), the numbering sequence indicates the order the system processes the tables. In this example, only a single step was needed.The execution plan specifies the type of table access. In the above example, the access was the
INDEX PATH . Access to the required data row is made using the index of the systables table. Normally, the execution plan uses the primary key as an index. Every transparent table in the ABAP Dictionary has a primary key and the system automatically creates an index for this key. 
When the system must read a large proportion of a table, the system does not use the primary key as an index.
For this example, the system did not need to read the row that corresponds to the index key. The information that was required was present in the key itself. The explanation indicates this using the phrase
Key-Only as follows:(1) Index Keys: tabname owner (Key-Only)
If a SELECT statement is specified without a fully-qualified key, the database may need to read the relevant rows with a FULL TABLE SCAN. In this case, you will not see an index in the SQL-Explain output but instead you will see something like the following:
1) informix.systables: SEQUENTIAL SCAN
This indicates that a read of the entire table is necessary (FULL TABLE SCAN).
With more complex operations, where the combination of results from several SELECTS on different tables is required, you will see further strategies mentioned (such as MERGE JOIN, DYNAMIC HASH JOIN). These refer to the join strategy chosen by the optimizer.
Ensuring Up-to-Date Information
The optimizer can compute an accurate value for each explanation field only if the statistical information for each table is up to date. To enable the optimizer to compute accurate values for the above fields, you must ensure that up-to-date statistical information about the contents of relevant tables is available.
To update your information, use the Update Statistics function. Since the execution plan selected by the optimizer (for example, the use of a table scan versus an index) depends crucially on this information, you should always ensure that it is kept as up to date as possible by regularly running Update Statistics.