17.10 - Sizing a Nonunique Secondary Index Subtable - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

Estimate the size of your nonunique secondary indexes using the equation provided below.

The number of base tables that can be referenced is limited by the maximum row size for the system and the length of the secondary index value. See below for more information about sizing NUSI subtables.

Special Considerations for NUSI Size Estimates

The number of AMPs in the configuration are an important factor in estimating the total size of any NUSIs defined on a base table, as summarized in the following table:.

IF the number of AMPS is … THEN at least … AND the result is that …
less than the number of rows per value one row from each NUSI value is probably distributed to each AMP.
  • Every AMP has every value.
  • Every AMP has a subtable row for every value.
greater than the number of rows per value some AMPs are missing some NUSI values.
  • Not every AMP has every value.
  • Not every AMP has a subtable row for every value.

NUSI Sizing Equation

The following parameter definitions are used with this equation.

           Parameter                                                       Definition
Cardinality x 8

Cardinality x 10

Each base table rowID is stored in a NUSI subtable.
  • For an nonpartitioned primary index table, the row ID is 8 bytes long.
  • For a PPI table, the row ID is 10 bytes long.

This means that you must use the Cardinality * 8 factor for NUSI subtables for nonpartitioned primary index base tables and the Cardinality * 10 factor for NUSI subtables for PPI base tables.

See equations below.

NumDistinct The value is an estimate of the number of distinct NUSI subtable values and is based on each NUSI subtable having at least one index row per AMP for each distinct index value of a base table row stored on that AMP.
IndexValueSize The number of index data bytes.
NUSI Row Overhead Sum of the following factors.
  • Row headers and trailers
  • NUSI row rowID
  • Spare byte
  • Presence octets = 18 bytes
MIN (NumAmps | Rows per value) The lesser of the two parameters.
6 The number of bytes consumed by the 3 VARCHAR Offset fields that follow the Additional Overhead field.

If fallback is defined for the base table, then double the calculated result.

NUSI Sizing Equation for Nonpartitioned Primary Index Base Table

NUSI subtable size NPPI = 8 × (Cardinality) + ((NumDistinct) × (IndexValueSize + 18)) x MIN(NumAMPs l Rows per value))) +6

NUSI Sizing Equation for PPI Base Table

NUSI subtable size PPI = 10 × (Cardinality) + ((NumDistinct) × (IndexValueSize + 18)) × MIN(NumAMPs l Rows per value))) + 6

NUSI Space Considerations

The PERM space required during the creation of a NUSI might temporarily be much greater than the space occupied by the finished index as described by the following table.

IF you create this sort of table … THEN the following peak temporary PERM space usage factors apply …
non-fallback or fallback with small AMP cluster size Estimate the temporary PERM space required when building a NUSI using the following worst case estimation equation.

TemporarySpaceNF = Cardinality × (LengthOfKey + 30)

  • TemporarySpaceNF is the size of the temporary PERM space required without fallback.
  • Cardinality is the number of rows in the base table.
  • LengthOfKey is the combined byte length of the key.
fallback Estimate the temporary PERM space required, assuming typical AMP cluster size, using the following

TemporarySpaceFB = NUSISubtableSize + TemporarySpaceNF

This is a very conservative space estimate. For typical AMP cluster sizes, peak usage exceeds the prediction made by the model.

AMP cluster size, which determines the relative size of the backup subtables, is a an important factor. For information about AMP clusters, see Teradata Vantage™ - Database Introduction, B035-1091. A typical AMP cluster size is 4 AMPs, but the valid range varies from 2 to 8 AMPs per cluster.

NUSIs do not use spool space and are built one subtable at a time.