Explain-Mode Directives

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

Explain-Mode Directives:
 
|--EXPLAIN--+----------------------+--+----------+--------------|
            '-+---+--AVOID_EXECUTE-'  '-comments-'
              '-,-'
 

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 sqexplain.out 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 sqexplain.out 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 file sqexplain.out:

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 sqexplain.out 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 sqexplain.out 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.