Working with Join Indexes | CREATE JOIN INDEX | VantageCloud Lake - Working with Join Indexes - Teradata Vantage

Similarities and Differences between Join Indexes and Base Tables

A join index is similar to a base table. For example, you can create NUSIs on a join index and Teradata recommends collecting statistics on appropriate columns and indexes of both to make sure the Optimizer has accurate statistical summaries of their demographics.

You can also perform DROP, HELP and SHOW statements on join indexes. However, you cannot directly query or update a join index. You can do this by creating a view that has a similar definition to that of the join index, which can then be queried like any other view. Such a view provides a logical view of the data, and you can maintain it for while you need it. A join index, in contrast, is a physical database object that you create or delete for performance reasons. It does not affect the logical view of the data in any way.

For example, the following query is not valid because ord_cust_idx is a join index, not a base table.

SELECT o_status, o_date, o_comment
FROM ord_cust_idx;

Because join indexes are not part of the logical definition of a database, you can use them as summary and prejoin tables, both of which would otherwise violate the rules of normalization. The update anomalies presented by denormalized structures like summary and prejoin tables are avoided because Vantage implicitly performs all necessary updates to join indexes to make sure that no information is ever lost and that semantic integrity is enforced (see the following section for details).

Join Index Updates Are Maintained by Vantage

Most values of a join index are automatically maintained by Vantage when update operations (DELETE, INSERT, MERGE, UPDATE) are performed on columns in their underlying base tables that are also defined for the join index. Additional steps are included in the execution plan to regenerate the affected portion of the stored join result.

The exception to this update rule is the values for any join index partitioning expression that specifies a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP built-in function. You must submit an appropriate ALTER TABLE TO CURRENT request to reconcile the values stored for the date or timestamp for the partitioning expression. You cannot use an ALTER TABLE TO CURRENT request to reconcile a CURRENT_TIME function that is specified within a partitioning expression.

Similarly, you cannot reconcile the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP values specified in the WHERE clause of a join index. For more information about updatable date and timestamp partitioning functions, see Join Indexes, Expressions, and Built-In Functions and ALTER TABLE TO CURRENT Usage Notes.

Maximum Number of Indexes Definable Per Data or Join Index Table

Up to 32 secondary and join indexes, in any combination, can be defined for a table.

Each multicolumn NUSI defined with an ORDER BY clause counts as two consecutive indexes in this calculation. See Why Consecutive Indexes Are Important for Value-Ordered NUSIs.

BLOCKCOMPRESSION

Use this option to set the block compression state of a join index.

The following table lists the available options for BLOCKCOMPRESSION.

Block-Level Compression and Join Indexes

Limits on data block sizes apply to the noncompressed size of a join index. Block compression does not raise of these limits or enable more data to be stored in a single data block than can be stored in an noncompressed data block of the same size.

Fallback and Join Indexes

If a hardware read error occurs when attempting to read the primary copy of the data, the system must reconstruct data from fallback copies. When a read error occurs, the file system reads the fallback copy of the join index subtable rows and reconstructs a memory-resident image of them on their home AMP. This is called Read From Fallback. See Reading or Repairing Data from Fallback . Without this feature, the file system fault isolation logic aborts the transaction and may mark the index as down. See SET DOWN and RESET DOWN Options.

Active Fallback may be able to repair the damage to the primary data dynamically. In situations where the bad data block cannot be repaired, Read From Fallback substitutes an error-free fallback copy of the corrupt rows each time the read error occurs. To avoid the overhead of this substitution, drop the join index and recreate it.

On the Block File System, enable the file system to detect all hardware read errors for join indexes by setting CHECKSUM to ON. On the Object File System, CHECKSUM has a default value that you cannot change.

Defining a Unique Primary Index for a Join Index

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 called 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. Therefore, 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 Using Unique Join Index in Access Path for Query.