15.10 - StatsV - 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 NULL

X(128)

DBase.DatabaseName

TableName

VARCHAR(128)

UNICODE

NOT NULL

X(128)

VTVM.TVMName

ColumnName

VARCHAR(10000)

UNICODE

X(255)

StatsTbl.ExpressionList

FieldIdList

VARCHAR(1000) LATIN

X(255)

StatsTbl.FieldIdList

StatsName

VARCHAR(128)

UNICODE

X(255)

StatsTbl.StatsName

ExpressionCount

SMALLINT

---,--9

StatsTbl.ExpressionCount

StatsId

INTEGER

NOT NULL

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

StatsTbl.StatsId

StatsType

CHAR(1) LATIN

NOT NULL

X(1)

StatsTbl.StatsType

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

SampleSignature

VARCHAR(256) LATIN

X(255)

StatsTbl.SampleSignature

SampleSizePct

DECIMAL(5, 2)

zz9.99

StatsTbl.SampleSizePct

ThresholdSignature

VARCHAR(512) LATIN

X(255)

StatsTbl.ThresholdSignature

MaxIntervals

SMALLINT

---,--9

StatsTbl.MaxIntervals

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

StatsSkipCount

INTEGER

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

StatsTbl.Reserved1

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

BLCCompRatio

 

INTEGER

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

StatsTbl.Reserved2

ColumnName

  • If more than one column or expression is specified, each column or expression is separated by a comma.
  • The maximum number of columns is 64.
  • If expressions are in the list, the maximum number of columns can be reduced past the limit of 64, depending on the combined total size of the text in the expressions.
  • If the combined total size of the expression text causes the maximum column limit to be less than the actual number of columns in the list, an error occurs.
  • IndexNumber

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

    FieldIdList

    The FieldIdList column is NULL for fields that involve expressions.

    MaxInterval and MaxValueLength

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

    Possible Values for StatsType

     

    Value

    Description

    T

    Table

    I

    Join Index

    N

    Hash Index

    V

    View

    Q

    Query

    L

    Link Row

    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 have been collected:

     STATISTICS
       INDEX (o_orderkey)
       ,INDEX (o_custkey, o_orderstatus)
          ON Orders;

    This query can be used to retrieve the statistics:

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

    For 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.

    multiple columns

    “MultiColumnStatsV[X]” on page 185.

    tables

    “TableStatsV” on page 330.

    materialized temporary tables

    “TempTableStatsV” on page 344.

    single expressions

    “ExpStatsV” on page 141.

    multiple expressions

    “MultiExpStatsV” on page 188.