15.10 - JoinIndicesV - 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 (NUSI)

JoinIdxDatabaseName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

JoinIdxName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

TVM.TVMName (NUSI)

IndexType

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(1)

Indexes.IndexType

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.

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

The following SELECT statement displays the join indexes defined on the table user1.oneoneone:

==> SELECT joinidxdatabasename, joinidxname 
   FROM DBC.JoinIndicesV
   WHERE databasename = 'user1' and tablename = 'oneoneone';

Result:

JoinIdxDataBaseName     JoinIdxName
-------------------     ------------
user1                   ji