Sizing Base Tables and Subtables, and Indexes | Teradata Vantage - Sizing Base Tables, LOB Subtables, XML Subtables, and Index Subtables - Advanced SQL Engine - Teradata Database

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
qby1588121512748.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantageā„¢

Teradata Database data block sizes and alignments are somewhat variable. This permits a designer the flexibility of designing tables without having to spend time balancing data types, row sizes, and block sizes in order to optimize their storage.

Critical Sizing Variables

The critical variables for sizing Teradata Database tables and indexes are the following:

  • Row size
  • Estimated cardinality
  • Presence of fallback

Table headers are a fixed variable, but must be considered when you are evaluating the projected sizes of your tables. Ensure that you take multivalue compression into account when sizing table headers (see Compression Types Supported by Vantage).

Sizing a Column-Partitioned Table

Column-partitioned tables have similar space usage as a table with row partitioning except for the impact of compression.

A column-partitioned table or join index may incur a large size increase compared with a table that is not column-partitioned if any of the following are true:

  • There are few physical rows in populated combined partitions.
  • There are many column partitions with ROW format and the subrows are narrow.
  • There are few rows per unique value of the primary index (for a unique primary index, there would be only one row per unique value).

A container can only hold values of rows that have either the same internal partition number and hash bucket value (for PI or NoPI tables and join indexes) or hash value (for PI tables and join indexes). The potentially increased size is because of the increased number of physical rows and the overhead of the row header for each physical row. It is far more common for a column-partitioned table to be smaller, and often significantly so, than a table that is not column-partitioned if COLUMN format is used for narrow column partitions and the table is not over-partitioned because of the reduced number of physical rows and autocompression.

Use the following general procedure to size a column-partitioned table.

  1. Estimate the size of the table without column partitioning using the standard methods for calculating the size of a table.
  2. Adjust your estimate based on the expected impact of autocompression, row header compression, and row header expansion.

    You can estimate the necessary adjustment by measuring the impact of your proposed column partitioning scheme on some sample data.

LOB and XML Sizing Variables

Each chunk of a BLOB, CLOB, or XML string stores an 8-byte length followed by as much as a 64 KB data fragment. LOBs are stored outside its base table row in a subtable. See Sizing a LOB or XML Subtable for more information.