Defining a Unique Primary Index for a Join Index - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
jbg1472252759029
lifecycle
latest
Product Category
Teradata Vantage™

You can define noncompressed and non-value-ordered single-table join indexes with a unique primary index. A join index that has a unique primary index is referred to as a unique join index. The primary index of a compressed join index must be based on the column_1 column set, which means there is only one instance of the column_2 column set for a given instance of column_1, so if the primary index column set in the column_1 column set were unique, there can be no row compression for that join index. Because of this, the ability to define a UPI for a compressed join index does not provide any advantages, so it is not supported.

The row hash value of a value-ordered join index corresponds to the ORDER BY column set instead of the primary index column set, and therefore does not support checking for the uniqueness of a UPI.

You can define a UPI on a join index. Because Vantage checks for uniqueness when new rows are inserted into the index, an INSERT or an UPDATE operation on the base table fails if the inserted or updated rows violate the uniqueness of the join index. Join index creation fails if rows of the base table or tables being indexed violate the uniqueness constraint defined by the UPI of the join index.

The value that you specify in the equality condition of the query forms the primary index key for accessing the unique join index.

For a user-defined unique join index whose primary index is explicitly defined as unique, the coverage testing and the presence of an equality condition on the primary index of the join index guarantee that the join index returns at most a single row, and therefore are sufficient to qualify the index to be used for single-row access by the Optimizer.

For information about how the Optimizer uses unique join indexes as an access path in two-AMP query plans, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

For information about temporal tables, see Teradata Vantage™ - ANSI Temporal Table Support, B035-1186 and Teradata Vantage™ - Temporal Table Support, B035-1182.