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

Describes all indexes on the tables specified by the query.

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

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 one of two things:
  • The index is not a join index.
  • 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.

    Indexes are simulated using the index validation function of the Teradata Index Wizard (see “Index and Partitioning Expression Validation” on page 845).

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