Column Partitioning | Optimizer Process | Teradata Vantage - 17.10 - Column Partitioning - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
English (United States)

In some ways, a column store and vertical partitioning are similar. In practice, a column store usually means each column of a table is stored separately, but a user views the table as one object, while vertical partitioning typically means that multiple columns are partitioned into separate tables and a view is defined over those tables to cause a user to perceive the set of tables as a single database object. If you only put one column in a vertical partition or allow a column store to have multiple columns in a structure and put that structure in one column, the 2 concepts are very similar from a functionality perspective.

The manner in which a column store and a vertically partitioned object are stored physically, however, is distinctively different. Vertical partitioning typically means that each of the vertical partitions is stored in separate tables using a row store that could be organized with different primary and secondary indexes, different partitioning, and so on so there is sometimes more flexibility, but it comes with the potential cost of requiring more space or CPU overhead to link columns of a row together.

When a column store is used, each column value is appended to its column store so a corresponding value for one column has the same relative position as the corresponding values for the "row" in other column partitions. This can produce a lower space overhead and more efficient processing of predicates. Because many columns, particularly the important columns in the table, are in single-column partitions, there are many opportunities to compress a column store that do not occur if there are multiple columns in a partition as is typical for vertical partitioning.

The column partitioning used by Vantage is more closely related to a column store than to vertical partitioning, but the column partitioning used by Vantage also supports multiple columns in a column partition.

The Vantage implementation of a column store is referred to as column partitioning because it builds on the row partitioning and it is efficient for storing columns individually in partitions, which it does by using containers as the basis for storing column values in a manner that is similar to a column store, and by allowing a hybrid approach that also permits a row store for a column partition.

Column partitioning can be considered to be a hybrid of column store characteristics and vertical partitioning characteristics, with a word from each description taken to form the descriptive phrase column partitioning.

You can either use column partitioning by itself or together with row partitioning efficiently in SELECT, INSERT, UPDATE (excluding UPSERT form), and DELETE query execution plans. You cannot use either the Upsert form of UPDATE or MERGE requests, however, if the target table of the request is column-partitioned.