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:
| JobCode | JobDesc |
|---|---|
| PK | NN, ND |
| UPI | |
| 1015 | Programmer |
| 1023 | Analyst |
| 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:
- Views with joins (see Denormalizing Through Views)
- Join indexes (see Denormalizing through Join Indexes)
- Global temporary tables (see Denormalizing Through Global Temporary and Volatile Tables)