Sizing a Column-Partitioned Table - Teradata Database

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.