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:
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.