17.00 - Example: Logging an XML Plan with VERBOSE EXPLAIN and DETAILED STATSUSAGE Collections - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

This example uses multiple query logging requests to log and retrieve request information.

Create the following table in user_1:

     CREATE TABLE employee (
       emp_code  INTEGER, 
       emp_name  VARCHAR(30), 
       dept_code INTEGER, 
       salary    FLOAT);

Assume the employee table is populated with 30,000 rows and statistics have been collected on column dept_code using this request:

     COLLECT STATISTICS COLUMN (dept_code) ON employee;

Log queries in XML format:

     BEGIN QUERY LOGGING WITH VERBOSE XMLPLAN ON user_1;

Log Object, SQL, Step Details, and Verbose EXPLAIN text into the plan data for this request:

     SELECT * 
     FROM employee 
     WHERE dept_name = 'sys_mgt'; 

This request returns verbose and XMLPLAN rules as being TRUE:

     SHOW QUERY LOGGING ON user_1;

Replace the existing BEGIN QUERY LOGGING rule and collect detailed statistics usage data in XML format on user_1:

     REPLACE QUERY LOGGING WITH DETAILED STATSUSAGE ON user_1;

Log Object, SQL, Step Details, and Statistics Usage information into the plan data for this request:

     SELECT * 
     FROM employee 
     WHERE dept_name = 'testing';  

This request returns Detailed Statistics and Statistics Usage rules as being TRUE:

     SHOW QUERY LOGGING ON user_1;

Terminate the logging on user_1:

     END QUERY LOGGING ON user_1;