When Join Indexes Are Useful - Teradata Vantage

Join indexes are designed to permit queries (join queries in the case of multitable join indexes) to be resolved by accessing the index instead of accessing, and possibly joining, their underlying base tables.

Multiple-table join indexes are useful for queries where the index contains all the columns referenced by one or more joins, thereby allowing the index to cover that part of the query, enabling retrieval of the requested data from the index rather than the underlying base tables. An index with this property is often called a covering index, and using a covering index is called index-only access. A join index defined with an expression in its select list provides less coverage than a join index defined using a base column (see Restrictions on Partial Covering by Join Indexes).

Even if a join index does not completely cover a query, the Optimizer can use the index to join to its underlying base tables in a way that provides better query optimization than scanning the base tables for all the columns specified in the request.

From the point of view of a database designer, the multitable join index permits great adaptability by providing the flexibility of normalization with the opportunity to create alternative, denormalized virtual data models, providing materialized views of the database.

Depending on their definition, single-table join indexes can also be useful for queries where the index contains a subset of the columns referenced in the statement. This situation is called a partial covering of the query. Multiple-table join indexes can also be used to partially cover a query for one or more of the tables defined in the join index.

Join indexes are also useful for queries that aggregate columns from tables with large cardinalities. These indexes play the role of prejoin and summary tables without denormalizing the logical design of the database or incurring the update anomalies and performance problems presented by denormalized tables. Denormalization often enhances the performance of a specific query or family of queries, but can make other queries perform worse.

Unlike traditional indexes, join indexes are optional to store pointers to their associated base table rows. Join indexes are used as a fast path final access point that eliminates the need to access and join their base tables. Join indexes substitute for, rather than reference, base table rows.

The exception is where an index partially covers a query. If the index is defined using either the ROWID keyword, the UPI of its base table, or a USI on the base table as one of its columns, then the index can be used to join with the base table to cover the query. A join index defined in this way is called a global index or global join index. You can only specify ROWID in the outermost SELECT of the CREATE JOIN INDEX statement. See CREATE JOIN INDEX .

This join can happen at different points in the query plan, depending on estimated costs. For example, the Optimizer may determine that performing a join with another table involved in the query is cheaper before joining to the base table.

Create join indexes with partitioned or nonpartitioned primary indexes, or join indexes that are column-partitioned, depending on the workloads the join indexes are designed to support. You can also create column-partitioned join indexes that have no primary index. See Column-Partitioned Join Indexes for the usage rules.

• Rules for Using the ROWID Keyword in a Join Index Definition
• Rules for Using the System-Derived PARTITION Column in a Join Index Definition
• Default Column Multivalue Compression for Join Index Columns When the Referenced Base Table Column Is Compressed
• Compression of Join Indexes at the Block Level
• Summary of Join Index Functions
• Similarities of Join Indexes to User Data Tables
• Join Index Applications
• Partial Query Coverage
• Designing for Range Queries: Choosing between a PPI and a Value-Ordered NUSI
• Collecting Statistics on a Join Index
• Fallback with Join Indexes