StatsRecs - Advanced SQL Engine - Teradata Database

SQL Request and Transaction Processing
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
ykx1561500561173.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1142
lifecycle
previous
Product Category
Teradata Vantage™

Function of StatsRecs

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.

StatsRecs Table Definition

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);

Attribute Definitions for StatsRecs

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.