INSERT EXPLAIN Examples | SQL Statements | Teradata Vantage - 17.05 - Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

Example: INSERT EXPLAIN

The following examples, with some exceptions, use the same SELECT request to show how the different syntax options of INSERT EXPLAIN produce different results.

Each INSERT EXPLAIN request inserts the data for the query plan produced by the Optimizer for the SELECT request that it modifies directly into the specified QCD database (see the information about the Query Capture Facility in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 for documentation of the QCD tables).

Example: Query Plan Name Stated, QCD Name Is TLE_Queries

The output of this INSERT EXPLAIN request is referenced under the query plan name EmployeeSmithQuery in the TLE_Queries database. The system collects neither statistics nor demographics for this request.

     INSERT EXPLAIN
     INTO TLE_Queries AS EmployeeSmithQuery
     SELECT emp_id, emp_address
     FROM employee
     WHERE emp_name = 'Smith';

Example: No Query Plan Stated, So Name in TLE_Queries QCD Is Null

The output of this INSERT EXPLAIN request has a null query plan name in the TLE_Queries database because there is no query plan naming clause. The system collects neither statistics nor demographics for this request.

     INSERT EXPLAIN
     INTO TLE_Queries
     SELECT emp_id, emp_address
     FROM employee
     WHERE emp_name = 'Smith';

Example: Query Plan Name Stated, QCD Name Is QCD

The output of this INSERT EXPLAIN request is referenced under the query plan name Employee Smith Query in the QCD database. The system collects neither statistics nor demographics for this request.

     INSERT EXPLAIN
     INTO QCD AS "Employee Smith Query"
     SELECT emp_id, emp_address
     FROM employee
     WHERE emp_name = 'Smith';

Example: Workload Execution Frequency Clause Specified

The output of this INSERT EXPLAIN request is referenced under the query plan name Wizard Test in the Wizard_QCD database. Statistics are collected for table_1 and column statistics are collected for column_2 and column_3 in table_1. The frequency of performance of the specified SQL SELECT request is 10 times per workload. No demographics are collected.

     INSERT EXPLAIN WITH STATISTICS FOR table_1
     INTO Wizard_QCD AS "Wizard Test" FOR 10
     SELECT * 
     FROM table_1 
     WHERE table_1.column_2 = 10 
     AND table_1.column_3 = 20 
     FOR 10;

Example: Statistics on Single Table With Two Tables Selected

The output of this INSERT EXPLAIN request is saved under the query plan name WizardTest2 in the Wizard_QCD database. Statistics are collected for the employee table and column statistics are collected for the emp_lastname column. No statistics are captured for the department table because the request specifies they are to be collected only for the employee table in the FOR table_name clause.

     INSERT EXPLAIN WITH STATISTICS FOR employee
     INTO Wizard_QCD AS WizardTest2
     SELECT employee.first_name, department.dept_num
     FROM employee, department
     WHERE employee.emp_lastname = 'Smith'
     AND   department.dept_name IN ('Engineering', 'Support');

Example: SQL Query Does Not Reference The Tables Named In The Table List

This INSERT EXPLAIN request returns an error because none of the tables specified in the table list are referenced in the specified query.

     INSERT EXPLAIN WITH STATISTICS FOR employee1
     INTO Wizard_QCD AS WizardTest2
     SELECT employee.first_name, department.dept_num
     FROM employee, department
     WHERE employee.emp_lastname = 'Smith'
     AND department.dept_name IN ('Engineering', 'Support');

    ****Failure 5644: No match found for specified tables in the request.

Example: Specifying Statistical Sampling

This example uses an explicit sampling percentage of 80 to capture QCD statistics for the specified SELECT request:

     INSERT EXPLAIN WITH STATISTICS
     USING SAMPLE 80 PERCENT
     INTO MyQCD AS query1
     SELECT t2.y3, t1.x3  
     FROM t1, t2
     WHERE  t1.pi = t2.y2;

Example: Generating Single-Column Statistics

Single-column statistics recommendations are represented by a single row in the StatsRecs table. In this example there are two separate recommendations, one for the primary index on column a1 and one for the nonindexed column c1.

After you run this request, you can then apply the generated recommendations for collecting statistics by running the two COLLECT STATISTICS requests that the system stores in the StatsDDL column of the StatsRec table in the QCD database.

The definition for the only table referenced by the SQL request to be analyzed is as follows:

     CREATE TABLE t1 (
       a1 INTEGER,
       b1 INTEGER,
       c1 INTEGER);

The following INSERT EXPLAIN request generates the single-column statistics recommendations for the SQL request SELECT * FROM t1 WHERE c1=5.

     INSERT EXPLAIN INTO QCD
     CHECK STATISTICS
     SELECT * FROM t1 WHERE c1 = 5;

The following SELECT request selects all the columns that contain the statistics recommendations for QueryID 1, which is the system-assigned unique identifier for the row set in the QCD StatsRecs table that contains the recommendations:

     SELECT *
     FROM QCD.StatsRecs
     WHERE QueryID = 1
     ORDER BY StatsID;

The query returns the following report:

     QueryID           1
     StatsID           0
DatabaseName D1
   TableName t1
     FieldID        1025
   FieldName A1
      Weight           3
    StatsDDL COLLECT STATISTICS D1.t1 COLUMN A1 ;
     QueryID           1
     StatsID           1
DatabaseName D1
   TableName t1
     FieldID        1027
   FieldName C1
      Weight           3
StatsDDL COLLECT STATISTICS D1.t1 COLUMN C1 ;

Example: Generating Multicolumn Statistics

Multicolumn statistics recommendations are represented by multiple rows in the StatsRecs table. Each column of the recommendation is stored in a separate row, sharing its StatsID value with the other columns in the set, each of which is represented by its own row.

Note how the COLLECT STATISTICS request text is stored in the row corresponding to the lowest FieldID value. The StatsDDL value for the other rows is null. In this example, there is one recommendation for the multicolumn primary index.

After you run this request, you can then apply the generated recommendations for collecting statistics by running the COLLECT STATISTICS request that the system stores in the StatsDDL column of the StatsRec table in the QCD database.

The definition for the only table referenced by the SQL request to be analyzed is as follows:

     CREATE TABLE t2 (
       a2 INTEGER,
       b2 INTEGER,
       c2 INTEGER,
       d2 INTEGER)
     PRIMARY INDEX (a2, b2, c2);

The following INSERT EXPLAIN request generates the multicolumn statistics recommendations for the SQL request SELECT * FROM t2.

     INSERT EXPLAIN INTO QCD
     CHECK STATISTICS
     SELECT * FROM t2;

The following SELECT request selects all the columns that contain statistics recommendations for QueryID 2, which is the system-assigned unique identifier for the row set in the QCD StatsRecs table that contains the recommendations:

     SELECT *
     FROM QCD.StatsRecs
     WHERE QueryID = 2
     ORDER BY StatsID, FieldID;

The query returns the following report:

     QueryID           2
     StatsID           0
DatabaseName D1
   TableName t2
     FieldID        1025
   FieldName A2
      Weight           3
    StatsDDL COLLECT STATISTICS D1.t2 COLUMN (A2 ,B2 ,C2 ) ;
     QueryID           2
     StatsID           0
DatabaseName D1
   TableName t2
     FieldID        1026
   FieldName B2
      Weight           3
    StatsDDL ?
     QueryID           2
     StatsID           0
DatabaseName D1
   TableName t2
     FieldID        1027
   FieldName C2
      Weight           3
    StatsDDL ?

There are three rows in the report: one for each of the columns that make up the primary index for the table. You can tell these are multicolumn statistics because all three rows share the same QueryId value (2) and the same StatsID value (0). Another sign that these are multicolumn statistics is that there is only one column of DDL text, and it is stored in the row with the lowest FieldID value (1025).

Example: Capturing Output Data as an XML Document

This example produces one row of query capture output and stores it in XML format in the QCD table myqcd.XMLQCD.

     INSERT EXPLAIN INTO myqcd IN XML
     SELECT *
     FROM DBC.DBCInfoV;

The following output shows a fragment of the row inserted into myqcd.XMLQCD table.

             Id            1
           Kind            Q
            Seq            1
         Length         4501
           Text  <?xml version="1.0" encoding="UTF-8"?><QCF>
<ElapsedSeconds>0.030<ElapsedSeconds><QueryID>8<QueryID>
<User_Database><UDB_Key>1</UDB_Key><UDB_ID>256</UDB_ID>
<MachineName>localhost</MachineName><UDB_Name>DBC</UDB_Name>
</User_Database><Query><UDB_Key>1</UDB_Key><MachName>localhost
</MachName><NumAMPs>2</NumAMPs><NumPEs>1<NumPEs><NumNodes>1
<NumNodes><ReleaseInfo>13w.00.00.00</ReleaseInfo><VersionInfo>
13w.00.00.00</VersionInfo>…

Example: Capturing Output Data as an XML Document With No Associated DDL Text

This example produces one row of query capture output and stores it in XML format in the QCD table myqcd.XMLQCD. Because you specified the NODDLTEXT option, the system does not store the DDL SQL text related to the request.

     INSERT EXPLAIN INTO myqcd IN XML NODDLTEXT
     SELECT *
     FROM DBC.DBCInfoV;