docs.intersystems.com
Home  /  Application Development: Core Topics  /  InterSystems SQL Reference  /  SQL Commands  /  EXPLAIN


InterSystems SQL Reference
EXPLAIN
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


Returns the query plan(s) for a specified query.
Synopsis
EXPLAIN [ALT] [STAT]  [INTO :host-variable] query
Arguments
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.
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 query text is listed before each query plan.
The optional STAT keyword generates runtime performance statistics for each module in the query plan:
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 EXPLAIN command returns Show Plan results by invoking the ShowPlan() method, then formatting the results as an XML-tagged text string.
The EXPLAIN ALT command returns the alternate show plans results by invoking the ShowPlanAlt() methods, then formatting the results as an XML-tagged text string.
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:
  ZNSPACE "SAMPLES"
  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”:
  ZNSPACE "SAMPLES"
  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:
  ZNSPACE "SAMPLES"
  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() {
    DO rset.%Print("")
     }
The following Dynamic SQL example returns alternate query plans as an XML string. It returns SQL query text before each query plan:
  ZNSPACE "SAMPLES"
  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() {
    DO rset.%Print("")
     }
The following Dynamic SQL example returns a more complex query plan. Note how performance statistics appear both before and within the query plan:
  ZNSPACE "SAMPLES"
  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() {
    DO rset.%Print("")
     }
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