15.00 - Storage Considerations for Column-Partitioned Tables and Join Indexes - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Storage Considerations for Column‑Partitioned Tables and Join Indexes

Column‑partitioned tables and join indexes are always stored in packed64 format.

A table with column partitioning has similar space usage and guidelines as a table with only row partitioning except that you must take the impact of column compression or expansion into account.

If the flag byte that precedes the first presence byte for a physical row has the first high-order and third bits both not set, the physical row is in a nonpartitioned table or join index and does not contain an internal partition number because the internal partition number for any nonpartitioned database object it is assumed to be 0.

If the first bit is set and third bit is not set, the physical row is in a table or join index that has 2‑byte partitioning and that physical row contains a 2-byte internal partition number.

If the first bit is not set and the third bit is set, the physical row is in a table or join index that has 8-byte partitioning and the physical row contains an 8-byte internal partition number.

The following table summarizes this information.

 

Flag Byte Settings for Partitioning and Size of Internal Partition Number

First Bit

Third Bit

Description

Not set

Not set

Physical row is from a nonpartitioned table or join index.

Set

Not set

Physical row is from a partitioned table or join index that has 2‑byte partitioning.

Not set

Set

Physical row is from a partitioned table or join index that has 8‑byte partitioning.

Set

Set

Not used and reserved for future use.

Teradata Database stores the 2-byte or 8-byte internal partition number after the first presence byte of a physical row. Even though this information is stored after the hash value:uniqueness for a column‑partitioned table or after the hash bucket:uniqueness for a nonpartitioned NoPI table, the file system orders physical rows according to their internal partition number first, followed by the hash value:uniqueness.

The first presence byte in a container is not used as a presence byte; instead, it is used to indicate information about autocompression for the container.

A column-partitioned table or join index can be much larger than an otherwise equivalent, but not column‑partitioned, table or join index if there are few physical rows in populated combined partitions or there are many column partitions with ROW format and the subrows are narrow.

A container can only contain values of rows that have the same internal partition number and hash bucket value. The increased size occurs because of the increased number of physical rows and the overhead of the row header for each physical row.

However, a column-partitioned table or join index is usually smaller than a table or join index that is not column‑partitioned if COLUMN format is used for narrow column partitions and the table or join index is not over‑partitioned because of the reduced number of physical rows and autocompression.