15.00 - Column Partitioning - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Column Partitioning

Column partitioning is a form of partitioning for multiset tables and for single-table, non-aggregate, non-compressed join indexes. Columnar storage stores the data into a series of containers with usually many values of the column partition packed into each container; alternatively, the values of a column partition can be stored into a series of subrows with one value of the column partition per subrow.

Column partitioning enables sets of table or join index columns to be stored in separate partitions. Row partitioning of primary‑indexed tables also enables sets of rows to be stored in separate partitions. Teradata Columnar makes it possible for a table or join index to be column‑partitioned, or both column‑partitioned and row‑partitioned by using multilevel partitioning.

Column partitioning enables the Optimizer to devise efficient searches by using column and row partition elimination based on the columns that are needed by a query. If a table or index column is not needed by a request, the column partition with that column need not be read. If multiple columns are needed for a request, the query plan devised by the Optimizer includes putting projected column values from selected table rows together to form result rows. This can be combined with row partition elimination to further reduce the data that must be accessed to satisfy a request.

Teradata Database can apply various compression techniques to column‑partitioned data that can reduce the storage requirements for a table or join index, which can then reduce the I/O requirements for DML requests. When column partitioning is combined with row partitioning, the number of compression opportunities available to the system can increase.