EXPLAIN directives

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

The following table describes the pertinent output lines in Result of EXPLAIN AVOID_EXECUTE directives that describe the chosen query plan.


Output Line in Result of EXPLAIN AVOID_EXECUTE directives	Chosen Query Plan Description
`DIRECTIVES FOLLOWED: EXPLAIN AVOID_EXECUTE`	Use the directives EXPLAIN and AVOID_EXECUTE to display the query plan and do not execute the query.
`Estimated # of Rows Returned: 7`	Estimate that this query returns seven rows.
`Estimated Cost: 7`	This estimated cost of `7` is a value that the optimizer uses to compare different query plans and select the one with the lowest cost.
`1) informix.r: SEQUENTIAL SCAN`	Use the cust_calls r table as the outer table and scan it to obtain each row.
`2) informix.l: INDEX PATH`	For each row in the outer table, use an index to obtain the matching row(s) in the inner table customer l.
`(1) Index Keys: customer_num (Serial, fragments: ALL)`	Use the index on the customer_num column, scan it serially, and scan all fragments (the customer l table consists of only one fragment).
`Lower Index Filter: informix.l.customer_num = informix.r.customer_num`	Start the index scan at the customer_num value from the outer table.