15.10 - IndexStatsV[X] - 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

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseNameI (UPI)

TableName

VARCHAR(128)

UNICODE

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(128)

TVM.TVMNameI (Composite UPI)

ColumnName

VARCHAR(10000)

UNICODE

NOT CASESPECIFIC

NOT NULL

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(256)

StatsTbl.SampleSignature

SampleSizePct

DECIMAL(5,2)

zz9.99

StatsTbl.SampleSizePct

ThresholdSignature

VARCHAR(512) LATIN

NOT CASESPECIFIC

X(512)

StatsTbl.ThresholdSignature

MaxIntervals

SMALLINT

---,--9

StatsTbl.MaxIntervals

MaxValueLength

INTEGER

---,--9

StatsTbl.MaxValueLength

RowCount

FLOAT

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

StatsTbl.RowCount

UniqueValueCount

FLOAT

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

StatsTbl.UniqueValueCount

PUniqueValueCount

FLOAT

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

StatsTbl.PUniqueValueCount

NullCount

FLOAT

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

StatsTbl.NullCoun

AllNullCount

FLOAT

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

StatsTbl.AllNullCoun

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.S(F)

StatsTbl.LastCollectTimeStamp

LastAlterTimeStamp

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS.S(F)

StatsTbl.LastAlterTimeStamp

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.

    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 ValidStats

     

    Value

    Description

    T

    Statistics are valid.

    F

    Statistics are not valid.

    Note: Statistics can be not valid if the query cannot be parsed. For example, if a table is dropped, all statistics referencing it are not valid.

    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.IndexStatsV 
           WHERE databasename = 'sales' 
             AND tablename = ‘orders’;
     

    For more information about statistics collected on ...

    See ...

    non-indexed columns and single-column indexes

    “ColumnStatsV[X]” on page 84.

    groups of non-indexed columns

    “MultiColumnStatsV[X]” on page 185.

    tables

    “StatsV” on page 320 and “TablesV[X]” on page 333.

    materialized temporary tables

    “TempTableStatsV” on page 344.

    single expressions

    “ExpStatsV” on page 141.

    multiple expressions

    “MultiExpStatsV” on page 188.