Databases Reference
In-Depth Information
would require a reorganization of the database schema and repopulation of
emp2, a potentially significant overhead.
In summary, the key effects of denormalization are:
1.
A definite improvement (decrease) in query time.
2.
A potential increase in update time.
3.
A potential increase in storage space.
4.
A potential loss of data integrity due to certain deletions.
5.
The necessity for program transformations for all relevant queries.
6.
The overhead needed to reorganize one or more tables (e.g., emp to emp2).
These effects require careful consideration. The example in Section 15.3 goes into more
details of this analysis.
Many database systems have software that provides data synchronization between
redundant data and base data, and thus supports the concept of denormalization using
redundancy. For instance, software such as DB2 Everyplace, Oracle Data Hubs and
Oracle Streams, and SQL Server Compare (Red-Gate Software) and SQL Server Every-
where Edition, all provide such critical data synchronization services.
15.3
Table Denormalization Strategy
A practical strategy for table denormalization is to select only the most dominant pro-
cesses to determine those modifications that will most likely improve performance. The
basic modification is to add attributes to existing tables to reduce join operations. The
steps of this strategy for relational databases follow.
1.
Minimize the need for denormalization by developing a clear and concise logical
database design, including tables that are at least 3NF or BCNF. This establishes
the requirement of an accurate representation of reality and flexibility of the
design for future processing requirements. If the original database does not meet
performance requirements, consider denormalization as one of the options for
improving performance.
2.
Select the dominant queries and updates based on such criteria as high fre-
quency of execution, high volume of data accessed, response time constraints,
or explicit high priority. Remember this rule of thumb: Any process whose fre-
quency of execution or data volume accessed is 10 times that of another process
is considered to be dominant.
