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. |
|
| greater than the number of rows per value | some AMPs are missing some NUSI values. |
|
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.
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.
|
|||
| 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) where:
|
|||
| 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.