16.10 - Column Partition Elimination - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
June 2017
Programming Reference
User Guide

Column partition elimination is a method for enhancing query performance for column-partitioned tables by skipping column partitions that do not contain column values that meet the search conditions of a query. Column partitioning enables efficient searches by using partition elimination based only on the columns needed by a query. If a column is not needed by a query, the column partition containing the data for that column does not need to be read. You can even consider this to be a form of join elimination because the table or join index can be considered to be a join of all the column partitions based on joins on equality of the rowids associated with each column partition value.

If multiple columns are required for the response set for a query, the query plan includes putting projected column values from selected table rows together to form result rows. Teradata Database can combine this with row partition elimination to further reduce the data that must be accessed to satisfy a query.

Column partition elimination is an automatic optimization in which the Optimizer determines, based on query conditions and a partitioning expression, that some column partitions for that partitioning expression cannot contain qualifying column values; therefore, those column partitions can be skipped during a file scan. Column partitions that are skipped for a particular query are called eliminated column partitions.

Teradata Database supports both static and dynamic column partition elimination.

Teradata Database also supports various forms of row partition elimination for row-partitioned tables and join indexes. See Row Partition Elimination, Static Row Partition Elimination, Delayed Row Partition Elimination, and Product Joins With Dynamic Row Partition Elimination for more information about row partition elimination.