15.10 - Example: Logging an XML Plan with VERBOSE EXPLAIN and DETAILED STATSUSAGE Collections - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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;