Examples of Processing XML Documents Produced by the XMLPLAN Option | Vantage - 17.10 - Examples of Processing XML Documents Produced by the XMLPLAN Option - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

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       ?           ?