15.00 - Secondary, Hash, and Join Index Limits - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Secondary, Hash, and Join Index Limits

 

Parameter

Value

Number of tables that can be referenced in a join.

128

Minimum number of secondary, hash, and join indexes, in any combination, per base data table.

The only index required for any Teradata Database table or index is a primary index.

A primary index is not required or allowed for:

  • Global temporary trace tables
  • Non‑partitioned NoPI tables
  • Column‑partitioned tables and join indexes
  • Secondary indexes
  • 0

    Maximum number of secondary, hash, and join indexes, in any combination, per base data table.

    Each composite NUSI defined with an ORDER BY clause counts as 2 consecutive indexes in this calculation.

    The number of system-defined secondary and single-table join indexes contributed by PRIMARY KEY and UNIQUE constraints counts against the combined limit of 32 secondary, hash, and join indexes per base data table.

    32

    Maximum number of columns referenced per secondary index.

    64

    Maximum number of columns referenced per single table in a hash or join index.

    64

    Maximum number of rows per secondary, hash, or join index.

    Limited only by disk capacity.

    Maximum number of columns referenced in the fixed part of a compressed join index.

    Teradata Database implements two very different types of user‑visible compression in the system.

    When describing compression of hash and join indexes, compression refers to a logical row compression in which multiple sets of nonrepeating column values are appended to a single set of repeating column values. This allows the system to store the repeating value set only once, while any nonrepeating column values are stored as logical segmental extensions of the base repeating set.

    Compression of column values refers one of the following:

  • Multi-value compression, in which Teradata Database stores the compressed values for a column one time only in the table header, not in the row itself.
  • Algorithmic compression, in which you specify scalar UDFs to compress and decompress specified byte, character, or graphic data values. The compression and decompression algorithms used by algorithmic compression are determined by you and follow the rules that are defined for a given pair of compression and decompression algorithms.
  • 64

    Maximum number of columns referenced in the repeating part of a compressed join index.

    64

    Maximum number of columns in an uncompressed join index.

    2,048

    Maximum number of columns in a compressed join index.

    128