Sizing a Nonunique Secondary Index Subtable - Teradata VantageCloud Lake

Lake - Database Reference
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
ohi1683672393549.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
ohi1683672393549

Estimate the size of your nonunique secondary indexes using the following equation.

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.

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:

Number of AMPS Result
Less than the number of rows per value Typically, at least one row from each NUSI value is 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 More than one AMP is missing 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.

Therefore, 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 following the table.
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 may temporarily be much greater than the space occupied by the finished index as described by the following table.

Table Type Peak Temporary PERM Space Usage Factors
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)

where:
  • 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 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 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.