15.10 - IndicesV[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)

Schema

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

NOT CASESPECIFIC

NOT NULL

X(128)

TVM.TVMName

IndexNumber

SMALLINT

NOT NULL

ZZ9 (explicit)

Indexes.IndexNumber

IndexType

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(1)

Indexes.IndexType

UniqueFlag

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(1)

Indexes.UniqueFlag

IndexName

VARCHAR(128)

UNICODE

X(128)

Indexes.Name

ColumnName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

TVFields.FieldName

ColumnPosition

SMALLINT

NOT NULL

Z9 (explicit)

Indexes.FieldPosition

CreatorName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

CreateTimeStamp

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS

Indexes.CreateTimeStamp

LastAlterName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

LastAlterTimeStamp

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS

Indexes.LastAlterTimeStamp

IndexMode

CHAR(1) LATIN

NOT CASESPECIFIC

X(1)

Indexes.IndexMode

AccessCount

BIGINT

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

ObjectUsage.UserAccessCnt

LastAccessTimeStamp

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS

ObjectUsage,LastAccess
TimeStamp

UniqueOrPK

CHAR(1) LATIN

X(1)

Indexes.UniqueOrPK

VTConstraintType

CHAR(1) LATIN

X(1)

Indexes.VTConstraintType

TTConstraintType

CHAR(1) LATIN

X(1)

Indexes.TTConstraintType

SystemDefinedJI

CHAR(1) LATIN

X(1)

Indexes.SystemDefinedJI

IndexDatabaseName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

X(128)

Dbase.DatabaseName

LDIType

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

X(1)

Indexes.LDIType

The X view references these additional tables:

  • DBC.AccessRights
  • DBC.Owners
  • DBC.Roles
  • DBC.RoleGrants
  • One row is returned from the Indices view for each column in each index. Therefore, a query on an index made up of multiple columns returns multiple rows.

    Some of the column values show information related to a Teradata temporal table or an ANSI temporal table. For more information about these tables, see ANSI Temporal Table Support and Temporal Table Support.

    IndexDatabaseName

    If the IndexDatabaseName column is a join or hash index, the value is not NULL.

    Possible Values for IndexMode

     

    Value

    Description

    H

    Secondary index rows are hash distributed to the AMPs.

    L

    Secondary index rows are on the same AMP as the base data row.

    Note: If the index type is a join (J) or hash index (N) index, L is returned. In this instance, L represents an empty value.

    NULL

    Primary index or primary AMP index

    Possible Values for IndexNumber

    The IndexNumber is an internal number assigned to the index.

     

    Value

    Description

    1

    Primary index or primary AMP index

    Multiple of 4 (that is, a number between 4 and 128)

    Secondary index

    IndexType

     

    Value

    Description

    P

    Nonpartitioned primary index, except if the primary index is also a PRIMARY KEY or UNIQUE, then K or U, respectively, is used for this column

    Q

    Partitioned primary index with row, column, or both partitioning, except if the primary index is also a PRIMARY KEY or UNIQUE, then K or U, respectively, is used for this column

    A

    Indicates a primary AMP index.

    S

    Secondary index, except if the primary index is also a PRIMARY KEY or UNIQUE, then K or U, respectively, is used for this column

    J

    Join index

    N

    Hash index

    K

    Primary key

    U

    Unique constraint

    V

    Value-ordered secondary index

    H

    Hash-ordered ALL covering secondary index

    O

    Valued-ordered ALL covering secondary index

    I

    Ordering column of a composite secondary index

    1

    Indicates all the columns that comprise the first field of the join index.

    Note: This value is used for join and hash indexes.

    2

    Indicates all the columns that comprise the second field of the join index

    Note: This value is used for join and hash indexes.

    G

    Geospatial nonunique secondary index.

    Note: To determine if a table has partitioning, including when the primary index is for a primary key or unique constraint, see the DBC.TablesV[X].PartitioningLevels field or the DBC.PartitioningConstraintsV[X].PartitioningLevels field.

    To determine if a primary-indexed table has partitioning (when the primary index is not for a primary key or unique constraint) view the value of the IndexType field. IndexType is set to P for a nonpartitioned primary index or Q for a partitioned primary index.

    Example  

    The following statement results in DBC.IndicesV[X].IndexType of 1 and 2:

    CREATE JOIN INDEX BB_OTB_DATA.ORDER_JOIN_LINE ,NO FALLBACK ,CHECKSUM = DEFAULT AS 
    SELECT (BB_OTB_DATA.lineitem.l_orderkey ,BB_OTB_DATA.orders.o_orderdate,
            BB_OTB_DATA.orders.o_custkey ,BB_OTB_DATA.orders.o_totalprice),
     
           (BB_OTB_DATA.lineitem.l_partkey ,BB_OTB_DATA.lineitem.l_quantity,
            BB_OTB_DATA.lineitem.l_extendedprice,         BB_OTB_DATA.lineitem.l_shipdate )
     
    FROM 
    (BB_OTB_DATA.lineitem  LEFT OUTER JOIN BB_OTB_DATA.orders  ON 
     
    BB_OTB_DATA.lineitem.l_orderkey =  BB_OTB_DATA.orders.o_orderkey )
     
    ORDER BY BB_OTB_DATA.orders.o_orderdate ASC 
    PRIMARY INDEX ( l_orderkey );

    This query results in four rows in DBC.IndicesV[X] with value “1” (the four columns in the first field of the join index) and four rows with value “2” (the four columns in the second field of the join index).

    Possible Values for LDIType

    USI or NUSI defined on load isolated (LDI) tables or join indexes. Possible values are shown in the table.

     

    Value

    Description

    NULL

    Any secondary index on tables that are not load isolated and for a join index, primary index, or a primary AMP index on a table. LDIType is set to NULL for a join index, primary index, or a primary AMP index on a load isolated table as well as on a non-LDI table.

    E

    The base table and the secondary index are load isolated.

    N

    The base table is load isolated, but the secondary index is not.

    Possible Values for SystemDefinedJI

     

    Value

    Description

    Y

    The TVM row describes a join index defined by the system. Such an index is defined when there are temporal constraints on the underlying base table.

    NULL

    Any other objects in the system or a user-defined join index.

    Possible Values for TTConstraintType

     

    Value

    Description

    C

    CURRENT

    N

    NONSEQUENCED

    NULL

    Used when all rows on a table do not support TRANSACTIONTIME.

    If the table supports TRANSACTIONTIME, a value of NULL indicates that the row is for an index and not a temporal unique or primary constraint.

    S

    SEQUENCED

    Possible Values for UniqueOrPK

     

    Value

    Description

    U

    Unique

    K

    Primary key

    NULL

    The unique or primary key constraint is not associated with a time dimension, or the row is not for a unique or primary key constraint description.

    Possible Values for VTConstraintType

     

    Value

    Description

    A

    ANSIQUALIFIER

    Note: ANSI temporal tables require that the session temporal qualifier for systems using Teradata temporal tables be explicitly set to ANSIQUALIFIER.

    C

    CURRENT

    N

    NONSEQUENCED

    NULL

    Used when all rows on a table do not support VALIDTIME.

    If the table supports VALIDTIME, a value of NULL indicates that the row is for an index and not a temporal unique or primary constraint.

    S

    SEQUENCED

    W

    Unique or primary key constraint defined with WITHOUT OVERLAPS. ANSI valid-time table definitions can include primary key and unique constraints that prevent rows from having valid-time periods that overlap.

    The following SELECT statement displays index information for all the tables in the Personnel database:

    ==> SELECT TableName,ColumnName,ColumnPosition,IndexType,UniqueFlag FROM 
        DBC.Indices
           WHERE DatabaseName = ’Personnel’
           ORDER BY TableName,ColumnPosition ;

    The results of this query are as follows:

    TableName   ­ColumnName   ­ColumnPosition   ­IndexType   ­UniqueFlag
    ----------  ----------   --------------   ---------   ----------
    Charges     Proj_id               1             S       N
    Charges     EmpNo                 1             P       N
    Charges     Proj_id               2             P       N
    Department  DeptNo                1             P       Y
    Employee    EmpNo                 1             P       Y
    Employee    Name                  1             S       N
    Project     Proj_id               1             P       Y