17.10 - Denormalizing through Join Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

Join indexes provide the performance benefits of prejoin tables without incurring update anomalies and without denormalizing your logical or physical database schemas.

Although join indexes create and manage prejoins and, optionally, aggregates, they do not denormalize the physical implementation of your normalized logical model because they are not a component of the fully normalized physical model.

Remember: normalization is a logical concept, not a physical concept.

Example: Denormalizing through Join Indexes

Consider the prejoin example in Denormalizing Through Prejoins. You can obtain the same performance benefits this denormalization offers without incurring any of its negative effects by creating a join index.

    CREATE JOIN INDEX EmployeeJob
     AS SELECT (JobCode, JobDescription), (EmployeeNumber, EmployeeName)
    FROM Job JOIN Employee ON JobCode;

This join index not only eliminates the possibility for update anomalies, it also reduces storage by row compressing redundant Job table information.

Reasons to Denormalize Using Join Indexes

The following items are all reasons to use join indexes to “denormalize” your database by optimizing join and aggregate processing:

  • Update anomalies are eliminated because the system handles all updates to the join index for you, ensuring the integrity of your database.
  • Aggregates are also supported for join indexes and can be used to replace base summary tables.