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';
Result:
*** 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;
Result:
*** 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 ? ?