EXPLAIN Directives

You can use the EXPLAIN directives to display the query plan in the following ways:

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:

Figure 64 shows sample output for a query that uses the EXPLAIN AVOID_Execute directive.

Figure 64. 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 Figure 64 that describe the chosen query plan.

Output Line in Figure 64	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.

Output Line in Figure 64

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.