StatsRecs - Analytics Database - Teradata Vantage

SQL Request and Transaction Processing
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
zfm1628111633230.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
evd1472255317510
lifecycle
latest
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 generated for this query-column set combination.
The level is determined by 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. Implement these recommendations unless you have good reasons not to do so.

    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 to collect statistics on multiple combinations of multicolumn recommendations is too costly. Do not feel compelled to implement these recommendations.

    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.