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.
Possible Values for IndexMode
|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.
|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:
|0||Row size of up to 64KB|
|1||Row size of up to 1MB|
|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|
|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 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.
|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
|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
|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.
Possible Values for UniqueOrPK
|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
ANSI temporal tables require that the session temporal qualifier for systems using Teradata temporal tables be explicitly set to ANSIQUALIFIER.
|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.
|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.|