16.20 - Column-Partitioned Join Indexes - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

The principal application of column-partitioned join indexes is to provide an alternative physical database design of a primary base table that is not column-partitioned. This enables you to maintain the identical data, but organized very differently to support different query workloads. By having these very different organizations of the same data, you can present different types of queries to a table, one set that performs better on a table with a standard row structure, and another set that performs better on a column-partitioned physical table. For this second case, the Parser can substitute a column-partitioned join index for the table with a standard row structure, and achieve better performance than could be realized with the row-oriented storage of the primary data table.

Of course, you could also create the primary data table using column partitioning and a supporting join index on the same columns, but using a row-oriented physical design. The two methods are equally applicable, with neither having any particular advantage over the other.