Reference Manual
   CHAPTER 9. SQL Statements     

EXPLAIN statement [ESQL]

Function 

To retrieve a text specification of the optimization strategy used for a particular cursor.

Syntax 

EXPLAIN PLAN FOR CURSOR cursor-name INTO host-variable
... INTO host-variable
| USING DESCRIPTOR sqlda-name

Parameters 

cursor-name: identifier or host-variable

sqlda-name: identifier

Permissions 

Must have opened the named cursor.

Side effects 

None.

See also 

DECLARE CURSOR statement

PREPARE statement

FETCH statement

CLOSE statement

OPEN statement

Cursors in Embedded SQL

The SQL Communication Area

Description 

The EXPLAIN statement retrieves a text representation of the optimization strategy for the named cursor. The cursor must be previously declared and opened.

The host-variable or SQLDA variable must be of string type. The optimization string specifies in what order the tables are searched, and also which indexes are being used for the searches if any. This string can be quite long, but most optimization strings will fit into 300 characters.

The format of this string is, in general:

table (index), table (index), ...

If a table has been given a correlation name, the correlation name will appear instead of the table name. The order that the table names appear in the list is the order in which they will be accessed by the database server. After each table is a parenthesized index name. This is the index that will be used to access the table. If no index will be used (the table will be scanned sequentially) the letters "seq" will appear for the index name. If a particular SQL SELECT statement involves subqueries, a colon (:) will separate each subquery's optimization string. These subquery sections will appear in the order that the database server executes the queries.

After successful execution of the EXPLAIN statement, the sqlerrd[3] field of the SQLCA (SQLIOESTIMATE) will be filled in with an estimate of the number of input/output operations required to fetch all rows of the query.

A discussion with quite a few examples of the optimization string can be found in Monitoring and Improving Performance.

Standards and compatibility 

• SQL/92     Vendor extension.

• Sybase     Not supported in Adaptive Server Enterprise.

Example 

• The following example illustrates use of EXPLAIN:

  EXEC SQL BEGIN DECLARE SECTION;
  char plan[300];
  EXEC SQL END DECLARE SECTION;
  EXEC SQL DECLARE employee_cursor CURSOR FOR
     SELECT empnum, empname
     FROM employee
     WHERE name like :pattern;
  EXEC SQL OPEN employee_cursor;
  EXEC SQL EXPLAIN PLAN FOR CURSOR employee_cursor INTO :plan;
  printf( "Optimization Strategy: '%s'.n", plan );