Explain-Mode Directives

Use the explain-mode directives to test and debug query plans and to print information about the query plan to the explain output file.


Element	Description	Restrictions	Syntax
comments	Text documenting the directive	Must appear between comment symbols	Character string

The following table lists the effect of each explain-mode directive.

Keyword: Effect
EXPLAIN: Turns SET EXPLAIN ON for the specified query
AVOID_EXECUTE: Prevents the data manipulation statement from executing; instead, the query plan is printed to the explain output file

The EXPLAIN directive is primarily useful for testing and debugging query plans. It is redundant when SET EXPLAIN ON is already in effect. It is not valid in a view definition or in a subquery.

The next query executes and prints the query plan to the explain output file:

SELECT {+EXPLAIN}
   c.customer_num, c.lname, o.order_date
   FROM customer c, orders o WHERE c.customer_num = o.customer_num;

The AVOID_EXECUTE directive prevents execution of a query on either the local or remote site, if a remote table is part of the query. This directive does not prevent nonvariant functions in a query from being evaluated.

The next query does returns no data, but writes its query plan to the explain output file:

SELECT {+EXPLAIN, AVOID_EXECUTE}   c.customer_num, c.lname, o.order_date
   FROM customer c, orders o WHERE c.customer_num = o.customer_num;

You must use both the EXPLAIN and AVOID_EXECUTE directives to see the query plan of the optimizer (in the explain output file) without executing the query. The comma ( , ) separating these two directives is optional.

If you omit the EXPLAIN directive when you specify the AVOID_EXECUTE directive, no error is issued, but no query plan is written to the explain output file and no DML statement is executed.

You cannot use the explain-mode directives in the following contexts:

In a view definition
In a trigger
In a subquery

They are valid, however, in a SELECT statement within an INSERT statement.