15.10 - StatsRecs - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname

Teradata Database

vrm_release

15.10

category

Programming Reference

User Guide

featnum

B035-1142-151K

Each row set contains a COLLECT STATISTICS request and related information for collecting the recommended statistics generated by a DUMP EXPLAIN or INSERT EXPLAIN request specified with a CHECK STATISTICS clause.

The following CREATE TABLE request defines the StatsRecs table.

   CREATE SET TABLE StatsRecs (

     QueryID      INTEGER,

     StatsID      INTEGER,

     DatabaseName VARCHAR128   CHARACTER SET UNICODE NOT CASESPECIFIC,

     TableName    VARCHAR128   CHARACTER SET UNICODE NOT CASESPECIFIC,

     FieldID      INTEGER,

     FieldName    VARCHAR128   CHARACTER SET UNICODE NOT CASESPECIFIC,

     Level        INTEGER,

     StatsDDL     VARCHAR(2500) CHARACTER SETUNICODE NOT CASESPECIFIC)

   PRIMARY INDEX (QueryID, StatsID);

The following table defines the StatsRecs table attributes.

Attribute	Description
QueryID	Unique identifier for the query to which the recommendation applies. Partial NUPI for the table.
StatsID	Uniquely identifies a statistics collection recommendation for QueryID. There are multiple StatsID values corresponding to a single QueryID for multicolumn statistics recommendations. In other words, a set of rows having the same value in StatsId represents all the columns for a given recommendation. Partial NUPI for the table.
DatabaseName	Name of the containing database for TableName.
TableName	Name of the table in which FieldName is defined.
FieldID	Unique identifier for FieldName within TableName.
FieldName	Name of the column in the statistics recommendation.
Level	A representation of the confidence the Optimizer has in the usefulness of the statistics recommendations it has generated for this query‑column set combination. The level is determined by a number of factors, including the following: The number of columns in the recommendation. Whether the recommendations are for collecting single column statistics or multicolumn statistics. This measure is designed to help you prioritize which statistics you want to collect, particularly in situations where you cannot afford to collect statistics on a long list of multicolumn recommendations or for different combinations of multicolumn recommendations. You can also aggregate levels to rank different recommendations. Level 1 is the primary recommendation, also referred to as a recommendation made with High Confidence. This recommendation is more likely to help the Optimizer to generate the least costly plan possible than any others, and you should always implement them unless you have very good reasons not to. Recommendations to collect single‑column and multicolumn statistics with all the columns are considered to be primary. Level 2 is an optional or alternative recommendation for the multicolumn statistics, also referred to as a recommendation made with Low Confidence. Optional recommendations provide an alternative to collecting statistics on multiple unindexed columns. These recommendations can be useful when their columns are infrequently specified together as an equality condition within the given workload or when your site determines that it would be too costly to collect statistics on several different combinations of multicolumn recommendations, but unlike the case for Level 1 recommendations, you should not feel compelled to implement them. Optional recommendations are provided based on information collected from usable indexes and usable columns from the table described by the TableName column.
StatsDDL	The DDL text of the COLLECT STATISTICS statement used to populate this row set of the table. For multicolumn statistics, the text of the DDL statement is stored in the row having the lowest FieldID value. In this case, the content of this column for the other rows is null.