15.00 - Similarities of Join Indexes to User Data Tables - Teradata Database

Teradata Database Design

Teradata Database
Release Number
Content Type
User Guide
Publication ID
English (United States)

Similarities of Join Indexes to User Data Tables

In many respects, hash and join indexes are identical to base user data tables.

For example, you can do the following things with a join index.

  • Create a unique or non-unique primary index, either a PPI or an nonpartitioned primary index, on its columns.
  • UPIs are supported only for uncompressed single‑table join indexes without an ORDER BY clause. See SQL Data Definition Language for details.

  • Create a column‑partitioned, single‑table uncompressed, non‑aggregate join index. Such a join index can be sparse join index.
  • Create non-unique secondary indexes on its columns.
  • Perform any of the following statements against it.
  • COLLECT STATISTICS (Optimizer Form)
  • DROP STATISTICS (Optimizer Form)
  • Specify UDT and BEGIN and END bound functions on Period or derived Period columns in its definition.
  • Specify row‑level security constraint columns in its definition.
  • You can do this only if both of the following criteria are true:

  • The index references a maximum of one row‑level security‑protected base table.
  • All of the row‑level security constraint columns defined in the base table are included in the join index definition.
  • Note: You cannot specify row‑level security constraint columns in a partitioning expression for a PPI or column‑partitioned join index.

  • Specify any valid expressions in the select list and WHERE clause when the expressions reference at least one column.
  • The following expression types are not valid in a join index definition:

  • OLAP expressions
  • UDF expressions
  • Built‑in functions that are explicitly not valid such as DEFAULT and PARTITION.
  • Note that a join index defined with an expression in its select list provides less coverage than a join index that is defined using a base column (see “Restrictions on Partial Covering by Join Indexes” on page 575).

    Unlike base tables, you cannot do the following things with join indexes:

  • Create a join index on a join index.
  • Query or update join index rows.
  • For example, if ordCustIdx is a join index, then the following query is not legal:

       SELECT o_status, o_date, o_comment 
       FROM ordCustIdx;
  • Create a USI on its columns.
  • Define multi‑value or algorithmic compression on its columns.
  • If multi‑value or algorithmic compression are defined on any columns of its parent base table set, however, a join index does inherit that compression under most circumstances (see “Default Column Multivalue Compression for Join Index Columns When the Referenced Base Table Column Is Compressed” on page 501).