16.10 - Denormalizing Through Prejoins - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

A prejoin moves frequently joined attributes to the same base relation in order to eliminate join processing. Some vendors refer to prejoins as materialized views.

Example: Denormalizing through Prejoins

The following example first indicates two normalized relations, Job and Employee, and then shows how the attributes of the minor relation Job can be carried to the parent relation Employee in order to enhance join processing:

Job
JobCode JobDesc
PK NN, ND
UPI
1015 Programmer
1023 Analyst
Employee
EmpNum EmpName JobCode
PK, SA   FK
 
UPI
22416 Jones 1023
30547 Smith 1015

This is the denormalized, prejoin form of the same data. This relation violates 2NF:

Employee
EmpNum EmpName JobCode JobDesc
PK, SA FK
UPI
22416 Jones 1023 Analyst
30547 Smith 1015 Programmer

Reasons to Denormalize Using Prejoins

The following items are all possible reasons for denormalizing with prejoins:

  • Performance can be enhanced significantly.
  • The method is a good way to handle situations where there are tables having fewer rows than there are AMPs in the configuration.
  • The minor entity is retained in the prejoin so anomalies are avoided and data consistency is maintained.

Reasons Not to Denormalize Using Prejoins

You can achieve the same results obtained with prejoins without denormalizing your database schema by using any of the following methods: