15.00 - Limits for Hash and Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Limits for Hash and Join Indexes

  • Teradata tables can have up to 32 secondary, hash, and join indexes.
  • These 32 indexes can be any combination of secondary, hash, and join indexes, including the system‑defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints.

    Each multicolumn NUSI that specifies an ORDER BY clause counts as two consecutive indexes in this calculation.

  • Hash index columns cannot have XML, BLOB, CLOB, BLOB‑based UDT, CLOB‑based UDT, XML‑based UDT, Period, JSON, ARRAY, VARRAY, or Geospatial data types.
  • This also means that a join index cannot have a geospatial NUSI.

    Join index columns can have Period data types and can include expressions composed of system and user-defined functions and methods, including the use of the BEGIN, END, and P_INTERSECT built‑in functions on a Period data type column to compose an expression in the projection list and a single‑table condition in its WHERE or ON clauses.

  • Both hash and join indexes can be created on a row‑level security‑protected table only if all of the following criteria are met.
  • The hash or join index references a maximum of one row‑level security‑protected table.
  • All of the row‑level security constraint columns in the indexed table are included in the hash or join index definition.
  • Further Information

    Consult the following manuals for more detailed information on creating and using join indexes to enhance the performance of your database applications:

  • SQL Data Definition Language
  • Temporal Table Support
  • Security Administration