TableStatistics - 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 TableStatistics

Captures the statistics on a data sample for all the columns that are possible index candidates in the specified query.

TableStatistics also captures the detail statistics if you perform the SQL COLLECT STATISTICS (QCD Form) statement.

TableStatistics Table Definition

The following CREATE TABLE request defines the TableStatistics table:

   CREATE TABLE TableStatistics (
     MachineName    VARCHAR(30) CHARACTER SET UNICODE
                    UPPERCASE NOT CASESPECIFIC NOT NULL,
     TableName      VARCHAR128 CHARACTER SET UNICODE
                    UPPERCASE NOT CASESPECIFIC NOT NULL,
     DatabaseName   VARCHAR128 CHARACTER SET UNICODE
                    UPPERCASE NOT CASESPECIFIC NOT NULL,
     IndexName      VARCHAR128 CHARACTER SET UNICODE
                    UPPERCASE NOT CASESPECIFIC,
     ColumnName     VARCHAR128 CHARACTER SET UNICODE
                    UPPERCASE NOT CASESPECIFIC NOT NULL,
     CollectedTime  TIMESTAMP(6) NOT NULL,
     SamplePercent  FLOAT NOT NULL,
     IndexType      CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC                     NOT NULL,
     IndexID        INTEGER,
     StatisticsInfo VARBYTE(16383),
     DataType       SMALLINT,
     DataLength     INTEGER,
     Attributes     VARCHAR(256) CHARACTER SET LATIN NOT CASESPECIFIC,
     ModifiedTime   TIMESTAMP(6),
     ModifiedStats  VARBYTE(16383),
     QueryId        INTEGER,
     FieldPosition  BYTEINT,
     StatisticsID   INTEGER)
   PRIMARY INDEX (MachineName, DatabaseName, TableName);

Attribute Definitions for TableStatistics

The following table defines the TableStatistics table attributes:

Attribute Definition
MachineName The name of the system to which the table belongs.
TableName Name of the table for which the row defines statistics.
DatabaseName Name of the containing database or user for TableName.
IndexName Name of the index, in case the index is named. Otherwise, IndexName is NULL.
ColumnName Name of the column if the statistics are on a column.
CollectedTime The timestamp value when the statistics were collected.
SamplePercent The sample percentage of rows read to collect the statistics recorded in this row.
IndexType Flag indicating whether the statistics represented by this row pertain to an index.
  • D is a PARTITION column.
  • N is a non indexed column.
  • P is a pseudo-index.
  • Y is for the index specified by IndexID.
IndexID The unique identifier for the index to which the statistics represented by this row pertain.
  • If IndexType is N, IndexID is null.
  • If IndexType is P, IndexID is the ID value generated by scanning the existing index set for the table and incrementing the largest value.
  • If IndexType is Y, IndexID is the ID value assigned by Teradata Database.
StatisticsInfo Contains the column or index statistics.
DataType Indicates the data type of the column. The value is the same that is returned in the data type column in the PrepInfo CLIv2 parcel.
DataLength The maximum length of the column in bytes.
Attributes Set null and reserved for future use.
ModifiedTime If the statistics have been modified, indicates the timestamp value when the last modification occurred.
ModifiedStats Contains user-modified column or index statistics.

These statistics are used by the INITIATE INDEX ANALYSIS statement instead of the statistics in the StatisticsInfo column when you specify the USE MODIFIED STATISTICS option.

You create the statistics in ModifiedStats using the "what if" features of the Teradata Index Wizard utility.

QueryID The unique ID of the query.

Set null when statistics are collected using COLLECT STATISTICS (QCD Form).

FieldPosition Indicates the right-to-left position of the column within a composite index.

Statistics are recorded in the row corresponding to FieldPosition 1 only. The value for other column positions are set null.

StatisticsID Set to 1 if the demographics are captured by a COLLECT STATISTICS (QCD Form) or INSERT EXPLAIN statement.

StatisticsID has different values if the demographics are imported rather than captured directly.