The SET EXPLAIN Statement
Informix's optimisation analysis pf a query can be stored in an output file for analysis. This; is extremely useful, not only to understand what resources the DBMS uses to process the query but also to compare queries that produce similar output, but in different ways.
To obtain the analysis of a query, or set of queries, issue to following statement before running them:
SET EXPLAIN ON;
The analysis of each query will be written (in append mode) to the file sqexplain.out in the working directory. Because the analysis of all further queries will be appended to such file, it is advisable to remove the file when it is of no further use and issue the following statement:
SET EXPLAIN OFF;
The analysis of a query consists of:
A copy of the query.
An estimate of the amount of work to be done.
The estimate of the amount of work done is in arbitrary units. A single access to read data is one unit. Other actions are scaled around However, the estimated value does not translate directly into time and thc: is no direct correlation between the work involved in a query and resources used. Nevertheless, generally it is true that a query with a estimate is likely to take longer to run than one with a smaller Therefore, the estimate of the amount of work done is a good co for the efficiency of two similar queries.
An estimate of the number of rows returned by the query.
A plan of execution as selected by the optimiser.
The plan of execution itself consists of the order in which the accesses tables, the column or columns that serve as a filter, if any, whether the filtering is through an index and one of the following mby which the optimiser reads each table:
Method What it means Sequential Scan Reads rows in sequence, generally this is a bad thing. The expection is when it is the first method used by the optimiser Index Path Scans one or more indexes Autoindex Path Creates a temporary index Sort Scan Sorts the result of the preceding join or tables Merge Join Uses a sort-merge join instead of nested-loop join
If the query consists of UNIONed queries or sub-queries, each will be analysed separately. For example, the following is the output for a query that lists which film titles are out on loan and when they were rented:
QUERY:
SELECT c0002title, c0003dt_rented
FROM t0002film t0002, t0003rental t0003
WHERE c0002id = c0002id
AND c0003returned = 'N'
Estimated Cost: 17
Estimated # of Rows Returned: 6
1) informix.rental: SEQUENTIAL SCAN
Filters: informix.rental. returned = 'N'
2) informix.film: INDEX PATH
(1) Index Keys: c0002id
Lower Index Filter: informix.t0002film.c0002id = informix.t0003rental.c0002id