Column-Partitioned Join Indexes - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

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.