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.
Corresponding 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) index, L is returned. In this instance, L represents an empty value.
|
| M | If IndexType is J, mapping join index. |
| 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. |
| 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 indexes.
|
| 2 | Indicates all the columns that comprise the second field of the join index This value is used for join 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 query in the following CREATE JOIN INDEX statement returns 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).
The index is created in the Block File System.
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 );
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. |