15.00 - Denormalizing Through Join Indexes - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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.

Example  

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