17.00 - Usage Notes - Teradata Database

Teradata Vantage™ - Data Dictionary

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Administration
Programming Reference
featnum
B035-1092-170K

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 Teradata Vantage™ ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ Temporal Table Support , B035-1182 .

Corresponding Tables

The X view references these additional tables:
  • DBC.AccessRights
  • DBC.Owners
  • DBC.Roles
  • DBC.RoleGrants

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

Possible Values for RowSizeFormat

The RowSizeFormat indicates the row size:

Value Description
0 Row size of up to 64KB
1 Row size of up to 1MB

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.
This value is used for join and hash indexes.
2 Indicates all the columns that comprise the second field of the join index
This value is used for join and hash indexes.
G Geospatial nonunique secondary index.
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 column or the DBC.PartitioningConstraintsV[X].PartitioningLevels column.

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 column. IndexType is set to P for a nonpartitioned primary index or Q for a partitioned primary index.

Example: Using IndicesV

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