15.00 - Column-Partitioned Join Indexes - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

Column‑Partitioned Join Indexes

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.