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.
Note: 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.
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"
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 ? ?