15.10 - ExpStatsV - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Optimizer Statistics

DBC

 

View Column

Data Type

Format

Source Table.Column

DatabaseName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

TableName

VARCHAR(128)

UNICODE

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(128)

TVM.TVMNameI (Composite UPI)

ColumnName

VARCHAR(10000)

UNICODE

NOT CASESPECIFIC

X(255)

TVFields.FieldName

StatsName

VARCHAR(128)

UNICODE

X(128)

StatsTbl.StatsName

StatsSource

CHAR(1) LATIN

X(1)

StatsTbl.StatsSource

ValidStats

CHAR(1) LATIN

X(1)

StatsTbl.ValidStats

DBSVersion

VARCHAR(32) LATIN

X(32)

StatsTbl.DBSVersion

IndexNumber

SMALLINT

---,--9

StatsTbl.IndexNumber

Note: DBC.Indexes.IndexNumber is the referenced table.column.

SampleSignature

VARCHAR(256) LATIN

NOT CASESPECIFIC

X(255)

StatsTbl.SampleSignature

SampleSizePct

DECIMAL(5, 2)

zz9.99

StatsTbl.SampleSizePct

ThresholdSignature

VARCHAR(512) LATIN

NOT CASESPECIFIC

X(255)

StatsTbl.ThresholdSignature

MaxIntervals

SMALLINT

---,--9

StatsTbl.MaxInterval

MaxValueLength

INTEGER

---,--9

StatsTbl.MaxValueLength

RowCount

FLOAT

----,---,---,---,--9

StatsTbl.RowCount

UniqueValueCount

FLOAT

----,---,---,---,--9

StatsTbl.UniqueValueCount

PNullUniqueValueCount

FLOAT

----,---,---,---,--9

StatsTbl.PNullUniqueValueCount

NullCount

FLOAT

----,---,---,---,--9

StatsTbl.NullCount

AllNullCount

FLOAT

----,---,---,---,--9

StatsTbl.AllNullCount

HighModeFreq

FLOAT

----,---,---,---,--9

StatsTbl.HighModeFreq

PNullHighModeFreq

FLOAT

----,---,---,---,--9

StatsTbl.PNullHighModeFreq

CreateTimeStamp

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS

StatsTbl.CreateTimeStamp

LastCollectTimeStamp

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS

StatsTbl.LastCollectTimeStamp

LastAlterTimeStamp

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS

StatsTbl.LastAlterTimeStamp

IndexNumber

The IndexNumber field contains the index number when statistics are collected on an index. Otherwise, it is NULL.

MaxInterval and MaxValueLength

If these statistics are collected with system determined maximum intervals and maximum value length, the MaxInterval and MaxValueLength columns are NULL.

SampleSizePct

If sampling is not used, the SampleSizePct column is set to 0 or 100.

StatsSource

The StatsSource column records the method by which this statistic is acquired. For information about the possible values for the StatsSource column, see Appendix A: “View Column Values.”

This view does not contain:

  • Statistics on tables protected by row-level security. To get these statistics, create views on DBC.StatsTbl. You can view details about this table via Teradata Administrator, Teradata SQL Assistant, or Teradata Studio Express.
  • Information about column attributes. To get this information, join the DBC.StatsTbl table to the DBC.TVFields table. You can view details about this table via Teradata Administrator, Teradata SQL Assistant, or Teradata Studio Express.
  • This example assumes the following statistics are collected:

          STATISTICS
          COLUMN CAST(o_orderdatetime AS DATE) AS Stats_OrderDate
          ON Orders;

    This query can be used to retrieve the statistics collected on single expressions:

    ==> SELECT * FROM dbc.ExpStatsV 
           WHERE databasename = 'sales' 
             AND tablename = ‘orders’;
     

    For more information about statistics collected on ...

    See ...

    non-indexed columns and single-column indexes

    “ColumnsV[X]” on page 87.

    indexes for which two or more columns have been defined

    “IndexStatsV[X]” on page 158.

    tables

    “StatsV” on page 320 or “TableStatsV” on page 330.

    materialized temporary tables

    “TempTableStatsV” on page 344.

    multiple columns

    “MultiColumnStatsV[X]” on page 185.

    multiple expressions

    “MultiExpStatsV” on page 188.