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