You can use the EXPLAIN directives to display the query
plan that the optimizer chooses, and you can specify to display the
query plan without running the query.
You can use these directives:
EXPLAIN
Displays the query plan that the optimizer chooses.
EXPLAIN AVOID_EXECUTE
Displays the query plan that the
optimizer chooses, but does not run the query.
When you want to display the query plan for one SQL statement
only, use these EXPLAIN directives instead of the SET EXPLAIN ON or
SET EXPLAIN ON AVOID_EXECUTE statements.
When you use AVOID_EXECUTE (either the directive or in the SET
EXPLAIN statement), the query does not execute but displays the following
message:
No rows returned.
Result
of EXPLAIN AVOID_EXECUTE directives shows sample
output for a query that uses the EXPLAIN AVOID_EXECUTE directive.
Figure 1: Result
of EXPLAIN AVOID_EXECUTE directives
QUERY:
------
select --+ explain avoid_execute
l.customer_num, l.lname, l.company,
l.phone, r.call_dtime, r.call_descr
from customer l, cust_calls r
where l.customer_num = r.customer_num
DIRECTIVES FOLLOWED:
EXPLAIN
AVOID_EXECUTE
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 7
Estimated # of Rows Returned: 7
1) informix.r: SEQUENTIAL SCAN
2) informix.l: INDEX PATH
(1) Index Keys: customer_num (Serial, fragments: ALL)
Lower Index Filter: informix.l.customer_num = informix.r.customer_num
NESTED LOOP JOIN