16.10 - Examples of Processing XML Documents Produced by the XMLPLAN Option - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

These examples show how to use XML data type functions and methods to extract information from XML documents like those produced by the XMLPLAN option for BEGIN QUERY LOGGING and REPLACE QUERY LOGGING.

For more information, see Teradata XML.

You can also create an XML plan document by using the INSERT EXPLAIN statement with the IN XML option or by using BEGIN QUERY CAPTURE, and then extract information from it.

Using XMLEXTRACT to extract information from an XML plan

This example uses the XMLEXTRACT method to extract query execution plan information from an XML plan. The IPEEligibility attribute indicates whether or not the plan is eligible for Incremental Planning and Execution optimization. In this case, the IPEEligibility value that the query returns indicates that the request is not eligible.

select
    createxml(xmltextinfo).xmlextract(
    '//Plan/@IPEEligibility',
    'default=http://schemas.teradata.com/queryplan') as IPEEligibility
from
    dbc.qrylogv l,
    dbc.qrylogxmlv x
where
    l.queryid = x.queryid and
    l.statementtype = 'Select';
 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.
IPEEligibility
-----------------------------------------------------------------------
 IPEEligibility="NotEligible"

Using XMLTABLE to Extract Step Details

This example demonstrates how to use the XMLTABLE table function to extract step details, including join information, from an XML document.

select
     steplev1num,
     stepname,
     jointype,
     joinkind
from (
select
     l.queryid,
     createxml(xmltextinfo) as plan
from
     dbc.qrylogv l,
     dbc.qrylogxmlv x1
where
     l.queryid = x1.queryid and
     l.statementtype = 'Select') as x2,
     xmltable(
     xmlnamespaces(default 'http://schemas.teradata.com/queryplan'),
     '//PlanStep' passing by value x2.plan
     columns
          "QueryID" decimal(18,0) path '../../@QueryID',
          "StepLev1Num" integer path './@StepLev1Num',
          "StepName" char(4) path './@StepName',
          "JoinType" char(10) path './StepDetails/JIN/@JoinType',
          "JoinKind" char(10) path './StepDetails/JIN/@JoinKind')
     as t (
          "QueryID",
          "StepLev1Num",
          "StepName",
          "JoinType",
          "JoinKind")
where x2.queryid = t.queryid
order by t.queryid, steplev1num;
*** Query completed. 6 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.
StepLev1Num  StepName  JoinType    JoinKind
-----------  --------  ----------  ----------
          1  MLK       ?           ?
          2  MLK       ?           ?
          3  MLK       ?           ?
          4  RET       ?           ?
          5  JIN       Inner       Merge Join
          6  Edt       ?           ?