15.00 - Determining the Space Overhead for a Hash or Join Index - Teradata Database

Teradata Database Design

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

Determining the Space Overhead for a Hash or Join Index

The ROWID, if included, is always 10 bytes long for hash indexes. For join indexes, the space overhead for the ROWID is 10 bytes for 2‑byte partitioning and 16 bytes for 8‑byte partitioning.

The following equation provides an estimate of the space overhead required for an uncompressed hash index. Double the result if you define fallback on the index.

where:

 

This symbol …

Defines the …

N

cardinality of the base table.

F

length of the columns, including base row identifier information.

O

row overhead.

Assume the following row overhead for a join index depending on whether it is partitioned or not.

  • 12 bytes for an nonpartitioned join index
  • 14 bytes for a join index with 2‑byte partitioning
  • 20 bytes for a join index with 8‑byte partitioning
  • The following equation provides an estimate of the space overhead required for a row‑compressed hash or join index. Double the result if you define fallback on the index.

    where:

     

    This symbol…

    Defines the …

    F

    length of the fixed column column_name.

    For a hash index, this length includes the base row identifier information in the fixed column.

    R

    length of a single repeating column column_name.

    For a hash index, this length includes the base row identifier information in the repeating column.

    A

    average number of repeated columns for a given value in column_1_name.

    U

    number of unique values in the specified column_1_name.

    O

    row overhead.

    Assume the following row overhead for a hash or join index depending on whether it is partitioned or not.

  • 12 bytes for an nonpartitioned hash or join index
  • 14 bytes for a join index with 2‑byte partitioning
  • 20 bytes for a join index with 8‑byte partitioning