Join Indexes - Analytics Database - Teradata Vantage

Database Introduction

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-09-27
dita:mapPath
gtm1628096154303.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
dsm1472253642401
lifecycle
latest
Product Category
Teradata Vantageā„¢

A Join Index (JI) is composed of columns from one or more base tables. Like a base table, a JI can have a primary index, which can be used for direct access to one or a few rows.

A JI covers a query when the JI includes all the columns the query references. A covered query needs to examine only the JI.

A JI partially covers a query when the JI includes only some columns the query references. A partially covered query can use the JI for the columns it includes, but must get the other columns from the base tables.

You can define aggregate JIs for commonly used aggregation queries.

You can join a nonaggregate JI to its base tables to retrieve columns referenced by a query but not stored in the JI.

JIs impose additional processing on insert and delete operations and update operations that change the values of columns stored in the JI. The performance trade-offs are similar to those for SIs.