17.10 - Column-Partitioned Join Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
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.