Query Plan Report

Any user who runs a query can use the SET EXPLAIN statement or the EXPLAIN directive to display the query plan that the optimizer chooses. For information on how to specify the directives, see EXPLAIN Directives. The user enters the SET EXPLAIN ON statement or the SET EXPLAIN ON AVOID_EXECUTE statement before the SQL statement for the query, as the following example shows.

SET EXPLAIN ON AVOID_EXECUTE;
SELECT * FROM customer, orders
WHERE customer.customer_num = orders.customer_num
   AND customer.lname = "Higgins";

If a user does not have any access to SQL code source, the Database Administrator can set dynamically the SET EXPLAIN using the onmode -Y command running the SQL code. See information on SET EXPLAIN in the IBM Informix: Dynamic Server Administrator's Reference.

After the database server executes the SET EXPLAIN ON statement or sets dynamically the SET EXPLAIN with onmode -Y command, it writes an explanation of each query plan to a file for subsequent queries that the user enters. For a description of the output, see EXPLAIN Output Description.

EXPLAIN Output File

If the client application and the database server are on the same computer, the sqexplain.out file is stored in the current directory. If you are using a Version 5.x or earlier client application and the sqexplain.out file does not appear in the current directory, check your home directory for the file.

When the current database is on another computer, the sqexplain.out file is stored in your home directory on the remote host.

When you use the onmode -Y command to turn on SET EXPLAIN, the output is displayed in the sqexplain.out.sessionid file. If an sqexplain.out file already exists, the database server stops to use it until the administrator turns off the dynamic SET EXPLAIN for the session.

The sqexplain.out file tells you if external directives are in effect.

EXPLAIN Output Description

The SET EXPLAIN output contains the following information:

• The SELECT statement for the query
• An estimate of the query cost in units the optimizer uses to compare plans

  These units represent a relative time for query execution, with each unit assumed to be roughly equivalent to a typical disk access. The optimizer chose this query plan because the estimated cost for its execution was the lowest among all the evaluated plans.

• An estimate for the number of rows that the query is expected to produce
• The order to access the tables during execution
• The access plan by which the database server reads each table

  The following table shows the possible access plans.

Access Plan

Effect

SEQUENTIAL SCAN

Reads rows in sequence

INDEX PATH

Scans one or more indexes

AUTOINDEX PATH

Creates a temporary index

REMOTE PATH

Accesses another database (distributed query)

• The table column or columns that serve as a filter, if any, and whether the filtering occurs through an index
• The join plan for each pair of tables

  The following table shows the possible join plans.

Join Plan

Effect

DYNAMIC HASH

Use a hash join on the preceding join-table pair. The output includes a list of the filters used to join the tables. If DYNAMIC HASH JOIN is followed by (Build Outer) in the output, the build phase occurs on the first table. Otherwise, the build occurs on the second table, preceding the DYNAMIC HASH JOIN.

NESTED LOOP

Use a hash join on the preceding join-table pair. The output includes a list of the filters used to join the tables. The optimizer lists the outer table first for each join pair.