17.10 - Sizing Base Tables, Hash Indexes, and Join Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
User Guide
Publication ID
English (United States)

Estimate the size of your base tables and hash and join indexes using the equations provided in the sections below.

These equations assume a constant block size. Because block sizes can vary widely within a table, you can obtain more finely tuned estimates by computing values over a range of block sizes.

Calculating a Typical Block Size

Depending on how you define your tables and the boundary conditions on their rows, the maximum size in bytes for a typical block can be estimated by the following equation:

Typical block size = ROUNDUP(MAX × ¾, 512)


This variable … Specifies …
ROUNDUP the ROUNDUP function.
MAX × ¾ three quarters of the maximum block size in bytes.

The upper limit on this value is 255 sectors, or 130,560 bytes.

512 the rounded up sector boundary, which is the number of bytes per sector for the table.

Sizing a Column-Partitioned Join Index

See Sizing a Column-Partitioned Table for information about sizing a column-partitioned join index. The methodology for column-partitioned join indexes is identical to that used to size a column-partitioned table.

Table Sizing Equations

The following parameter definitions are used with this equation set:

              Parameter                                                      Definition
Block Overhead Block Header + Block Trailer = 72 bytes + 2 bytes = 74 bytes
Minimum Table Header Size 512 bytes
Typical Block Size Number of bytes per block (see Calculating a Typical Block Size above).
NumAmps Number of AMPs in the configuration.
RowCount Estimated cardinality for the table.
Average RowSize Physical row size for the table (see Row Size Calculation).

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

Rows per block (rounded down) = Typical block size – 38 ÷ Row size

Number of blocks (rounded up) = Row count ÷ Rows per block

Number of table header bytes = (Number of AMPs )(512)

Number of base table bytes (without fallback) = (Number of blocks × Typical block size) + Number of header bytes

Number of base table bytes (with fallback) = 2(Number of blocks)(Typical block size) – Number of header bytes

You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.

Example: Table, Hash, or Join Index on a Packed64 Format System

You have collected the following information.

                                     Parameter                                        Value
Block size 5,120 bytes
Typical block size used.
Number of AMPs 20
Row count 1,000,000
Row size 100 bytes
Rows per block are rounded down.

Number of blocks are rounded up.

Calculate the size of this object when it is defined with fallback.

Number of rows per block = (5,120 - 16) ÷ 100 = 51

Number of blocks = 1,000,000 ÷ 51 = 19,608

Number of table header bytes = 20 × 512 = 10240

Number of bytes in base table with fallback = ((19,608 × 5,120) × 2) + 10,240