EXPLAIN (SQL)

Returns the query plan(s) for a specified query.

Synopsis

EXPLAIN [ALT] [STAT]  [INTO :host-variable] query

Arguments

Argument	Description
ALT	Optional — returns alternate query plans. The default is to return a single query plan.
STAT	Optional — (Dynamic SQL only): returns query plan runtime performance statistics. The default is to return query plan(s) without runtime statistics. This syntax is ignored for Embedded SQL.
INTO :host-variable	Optional — (Embedded SQL only): An output host variable into which the query plan(s) are placed. This syntax is ignored for Dynamic SQL.
query	A SELECT 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 query. EXPLAIN cannot be used for other SQL operations that create a query plan, such as an INSERT with a SELECT clause. Specifying a query that does not begin with the SELECT keyword results in an SQLCODE -51. You can use Show Plan to display a query plan for non-SELECT queries.

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. These 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.

The EXPLAIN command returns Show Plan results by invoking the $SYSTEM,SQL.Explain() 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() method with the all=1 qualifier, then formatting the result set as a single field containing an XML-tagged text string.

Result Set XML Structure

The following is the structure of an XML-tagged text string for EXPLAIN ALT STAT query. Line breaks, indents, and comment notes are provided here for explanatory purposes:

<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() 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

For further details, refer to Using the Explain() Method in the “Optimizing SQL Queries” chapter of the SQL Optimization Guide.

Examples

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

  SET myquery = "EXPLAIN SELECT Name,DOB FROM Sample.Person WHERE Name [ 'Q'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {
    DO rset.%Print("")
     }

The following Dynamic SQL example is identical to the previous one, except it uses %Display() to display the results. Note that %Display() adds to the start of the XML string the column name “Plan” and adds to the end of the XML string “1 Rows(s) Affected”:

  SET myquery = "EXPLAIN SELECT Name,DOB FROM Sample.Person WHERE Name [ 'Q'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

The following Dynamic SQL 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:

  SET myquery = "EXPLAIN STAT SELECT Name,DOB FROM Sample.Person WHERE Name [ 'Q'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {
    WRITE rset.Plan
     }

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

  SET myquery = "EXPLAIN ALT SELECT Name,DOB FROM Sample.Person WHERE Name [ 'Q'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {
    WRITE rset.Plan
     }

The following Dynamic SQL example returns a more complex query plan. Note how performance statistics appear both before and within the query plan:

  SET q1 = "EXPLAIN STAT SELECT p.Name AS Person, e.Name AS Employee "
  SET q2 = "FROM Sample.Person AS p,Sample.Employee AS e "
  SET q3 = "WHERE p.Name %STARTSWITH 'Q' GROUP BY e.Name ORDER BY p.Name"
  SET myquery = q1_q2_q3
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {
    WRITE rset.Plan
     }

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