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.
See also Join and Hash Indexes.