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

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.

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

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.