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

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

Estimate the size of your unique secondary indexes using the equations provided below.

Because the index is unique, there is one USI subtable row for each row in the base table, so the row counts are identical.

USI Sizing Equation

The following parameter definitions are used with this equation:

           Parameter                                                          Definition
Index Value Size Size of the column set on which the USI is defined.

This must include any trailing pad bytes added for alignment purposes.

All row lengths must be an even number of bytes (see Byte Alignment), so be sure to take this into account.

Block Overhead Sum of the following factors.
  • Block headers and trailers
  • Row headers and trailers
  • USI rowID
  • Spare byte
  • Presence bit arrays
  • Base table rowID = 28 bytes for a nonpartitioned primary index table but = 30 bytes for a PPI table

Packed64 Format Sizing Equations

NPPI USI subtable size = ((Row Count) × (Index Value Size + 28))

PPI USI subtable size = ((Row Count) × (Index Value Size + 30))

Aligned Row Format Sizing Equations

NPPI USI subtable size = ((Row Count) × (Index Value Size + 28))

PPI USI subtable size = ((Row Count) × (Index Value Size + 30))

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

Vantage implicitly creates a unique secondary index on any column set specified as PRIMARY KEY or UNIQUE, so you must take these indexes into consideration for your capacity planning as well. PRIMARY KEY and UNIQUE constraints are generally implemented as single-table join indexes for temporal tables (see Teradata Vantage™ - ANSI Temporal Table Support, B035-1186 and Teradata Vantage™ - Temporal Table Support, B035-1182 for details). You must also take any of these system-defined join indexes into account when doing capacity planning.