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 -474; 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.
The user that issues the EXPLAIN command must have execute privileges for the %SYSTEM.QUERY_PLAN procedure.
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.
This command is fully supported for use in Embedded SQL, Dynamic SQL, the SQL Shell, the Management Portal, JDBC, and ODBC interfaces.
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()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
ALT
An optional argument that returns alternate query plans. The default is to return a single query plan.
STAT
(Dynamic SQL only): An optional argument that 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
(Embedded SQL only): An optional output host variable into which the query plan(s) are placed. This syntax is ignored for Dynamic SQL.
query
A SELECT, UPDATE, or DELETE query.
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