IndexTable - Advanced SQL Engine - Teradata Database

SQL Request and Transaction Processing
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
uqf1592445067244.ditamap
dita:ditavalPath
uqf1592445067244.ditaval
dita:id
B035-1142
lifecycle
previous
Product Category
Teradata Vantage™

Function of IndexTable

Describes all indexes on the tables specified by the query.

IndexTable Table Definition

The following CREATE TABLE request defines IndexTable:

   CREATE TABLE IndexTable (
     IndexNum        INTEGER NOT NULL,
     RelationKey     INTEGER NOT NULL,
     OrderBy         CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC
                     NOT NULL,
     AccessInfo      CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC
                     NOT NULL,
     Field1Only      CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC
                     NOT NULL,
     RangeConstraint CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC
                     NOT NULL,
     IndexFlag       CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC,
     IndexName       VARCHAR128 CHARACTER SET UNICODE NOT CASESPECIFIC,
     IndexType       CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC,
     UniqueFlag      CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC,
     IndexKind       CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC,
     NumNulls        FLOAT,
     NumIntervals    INTEGER,
     MinValue        VARCHAR(512) CHARACTER SET UNICODE
                     NOT CASESPECIFIC,
     ModeValue       VARCHAR(512) CHARACTER SET UNICODE
                     NOT CASESPECIFIC,
     ModeFreq        FLOAT,
     TotalValues     FLOAT,
     TotalRows       FLOAT)
   PRIMARY INDEX (RelationKey)
   UNIQUE INDEX USK_IdxNum_RelationKey ( IndexNum, RelationKey );

Attribute Definitions for IndexTable

The following table defines the IndexTable table attributes:

Attribute Description
IndexNum
  • Unique identifier for the captured index.
  • Partial USI for the table.
RelationKey
  • Unique identifier for the relation in which the captured index is defined.
  • NUPI for the table.
  • Partial USI for the table.
OrderBy Defines whether the index has an associated ORDER BY clause.
  • If F, the index has no ORDER BY clause.
  • If T, the index has an ORDER BY clause.
AccessInfo Specifies if the index is a covering index, bit map, or neither.
  • B is access using a bit map.
  • C is access using a covering index.
  • N is access neither by a bit map nor by a covering index.
  • P is access using a primary index.
Field1Only Defines whether the index is a join index and Field1 is the only part needed.
  • If F, this is not a join index requiring Field1 only. This generally means that either the index is not a join index or the index is a compressed join index.
  • If T, this is a join index requiring Field1 only.

    This means that the join index is not compressed.
RangeConstraint Flag for value-ordered indexes that have a range constraint used by the query plan.
  • If F, there is no range constraint on the index.

    The flag is set to F whether the index is used in the plan or not.

  • If T, there is a range constraint on the value-ordered index used in the plan.
IndexFlag Flag indicating whether the index was used in the query plan.
  • If F, the index was not used in the query plan.
  • If T, the index was used in the query plan.

    IndexFlag is also set to T if a subset of the row partitions of a row-partitioned primary index is accessed because of row partition elimination.
IndexName
  • The name of the index if it has one.
  • Null if this is not a named index.
IndexType A code indicating the type of the index.
  • H is a Hash-ordered secondary covering index.
  • J is a join index.
  • K is a primary key.
  • N is a hash index.
  • O is a value-ordered secondary covering index.
  • P is a nonpartitioned primary index.
  • Q is a row-partitioned primary index.
  • S is a secondary index.
  • U is a unique constraint.
  • V is a value-ordered secondary index.
UniqueFlag Code indicating whether the index is unique or nonunique.
  • If F, the index is nonunique.
  • If T, the index is unique.
IndexKind Code indicating whether the index is permanent or simulated.
  • P is a permanent index.
  • S is a simulated index.
NumNulls The number of nulls in the index.
NumIntervals The number of intervals in the index statistics.
MinValue The minimum value of the index.

This is obtained from statistical histogram interval 0 for the index.
ModeValue The value of the index that occurs the most in the table.

This is obtained from statistical histogram interval 0 for the index.
ModeFreq The number of times the modal value occurs in the index.
TotalValues The total number of values in the index other than the modal value.
TotalRows The cardinality of the table.