EXPLAIN (SQL)

Synopsis

EXPLAIN [ALT | ALL] [STAT | STATS] [INTO :host-variable] query

Description

The EXPLAIN command returns the query plan for a specified query as an XML-tagged text string. This query plan is returned as a result set consisting of a single field named Plan.

The query must be a SELECT, DELETE, or UPDATE query. Specifying an INSERT query results in an SQLCODE -481; using EXPLAIN with any other keyword results in an SQLCODE -51. You can use Show Plan to display a query plan for other queries, such as for INSERT queries which contain a SELECT clause. All errors are processed and thrown when the query reference by the EXPLAIN command is executed.

The ALT and STAT keywords can be specified in any order. The INTO keyword must be specified after these keywords. The optional ALT keyword generates alternate query plans. All of the alternate query plans are returned in the same XML-tagged text string. The normalized query text (tagged as <sql>) is listed before each query plan. The optional STAT keyword generates runtime performance statistics for each module in the query plan. The STAT keyword is only supported for SELECT queries. Runtime statistics are included in the same XML-tagged text string that contains the query plan. The following statistics are collected for each module:

• <ModuleName>: module name.

• <TimeSpent>: total execution time for the module, in seconds.

• <GlobalRefs>: a count of global references.

• <LinesOfCode>: a count of lines of code executed.

• <DiskWait>: disk wait time in seconds.

• <RowCount>: number of rows in result set.

• <ModuleCount>: number of times this module was executed.

• <Counter>: number of times this program was executed.

These statistics are returned within the text of the query plan(s) in the XML-tagged text string. Performance statistics for all modules in a query plan are returned before the associated query plan. Embedded SQL cannot generate or return runtime performance statistics; the STAT keyword is ignored and no error is issued.

To EXPLAIN a query, you must have execute privileges on the query.

The EXPLAIN command returns Show Plan results by invoking the $SYSTEM,SQL.Explain()Opens in a new tab method, then formatting the result set as a single field containing an XML-tagged text string. The EXPLAIN ALT command returns the alternate show plans results by invoking the $SYSTEM,SQL.Explain()Opens in a new tab method with the all=1 qualifier, then formatting the result set as a single field containing an XML-tagged text string.

<plans> /* tag included even if there is only one plan */
   <plan> /* the first query plan */
      <sql> /* the normalzed SELECT statement text */  </sql>
      <cost value="1147000"/>
         /* if STAT, include the following <stats> tags */
      <stats> <ModuleName>MAIN</ModuleName> /* XML-tagged list of stats (above) for MAIN module */ </stats>
      <stats> <ModuleName>FIRST</ModuleName> /* XML-tagged list of stats (above) for FIRST module */ </stats>
      <stats>  /* additional modules */ </stats>
   /* text of query plan */
   </plan>
   <plan> /* if ALT, same info for first alternate plan */
      ...
   </plan>
</plans>

The Explain() Method

You can return the same query plan information from ObjectScript using the $SYSTEM.SQL.Explain()Opens in a new tab method, as shown in the following example:

  SET myquery=2
  SET myquery(1)="SELECT Name,Age FROM Sample.Person WHERE Name %STARTSWITH 'Q' "
  SET myquery(2)="ORDER BY Age"
  SET status=$SYSTEM.SQL.Explain(.myquery,{"all":0},,.plan)
    IF status'=1 {WRITE "Explain() failed:" DO $System.Status.DisplayError(status) QUIT}
  ZWRITE plan

Arguments

Examples

This example returns the query plan as an XML string. It first returns the SQL query text, then the query plan:

EXPLAIN SELECT Name,DOB FROM Sample.Person WHERE Name [ 'Q'

This example returns the query plan and performance statistics as an XML string. It first returns the SQL query text, then the performance statistics (by module), then the query plan:

EXPLAIN STAT SELECT Name,DOB FROM Sample.Person WHERE Name [ 'Q'

This example returns alternate query plans as an XML string. It returns SQL query text before each query plan:

EXPLAIN ALT SELECT Name,DOB FROM Sample.Person WHERE Name [ 'Q'

This example returns a more complex query plan. Performance statistics appear both before and within the query plan:

EXPLAIN STAT SELECT p.Name AS Person, e.Name AS Employee
FROM Sample.Person AS p, Sample.Employee AS e
WHERE p.Name %STARTSWITH 'Q' GROUP BY e.Name ORDER BY p.Name

The following Embedded SQL example returns the query plan as an XML string. It first returns the SQL query text, then the query plan:

   #sqlcompile select=Runtime
   &sql(EXPLAIN INTO :qplan SELECT Name,DOB FROM Sample.Person WHERE Name [ 'Q')
   WRITE qplan

The following Embedded SQL example returns alternative query plans as an XML string. It first returns the SQL query text, then the first query plan, then the SQL query text, then the second query plan, and so forth:

   #sqlcompile select=Runtime
   &sql(EXPLAIN ALT INTO :qplans SELECT Name,DOB FROM Sample.Person WHERE Name [ 'Q')
   WRITE qplans

The following Embedded SQL example returns the query plan. The STAT keyword is ignored:

   #sqlcompile select=Runtime
   &sql(EXPLAIN STAT INTO :qplan SELECT Name,DOB FROM Sample.Person WHERE Name [ 'Q')
   WRITE qplan

See Also

• SELECT

• JOIN

• Show Plan

• Runtime Performance Statistics

• Querying the Database