Denormalizing Through Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Denormalizing Through Join Indexes

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.


Consider the prejoin example in “Denormalizing Through Prejoins” on page 129. 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.