Performance Tuning   «Prev  Next»
Lesson 4Logical database performance design
Objective Contrast third normal form with de-normalized design.

Logical Database Performance Design

De-normalized form and redundancy

Logical database design issues have been with database professionals since the days of magnetic tapes. In most college database design courses, students are taught to design a database in third normal form, which means that they rip apart the database relationships to remove all redundancy in the tables.
A relation is in third normal form when:
  1. There are no transitive dependencies bewteen attributes.
  2. All non-key attributes are fully functionally dependent on the entire concatenated key.

Redundant or De-normalized database design

However, in the real world, redundancy is very important. The advent of data warehouse design has also taught us that a redundant, or De-normalized database design will almost always perform faster than a non-redundant third normal form design.
For now, accept the statement that the introduction of redundancy will improve performance. If you can duplicate a column in a table and stop Oracle from joining into another table, you will reduce the disk I/O for an Oracle query and the query will run faster.

When to use redundancy

In most cases, the introduction of redundant data into an Oracle design is based upon two criteria: the size of the data item, and the volatility of the data item. If an item is very small and seldom changes, it is a good candidate for redundant copying into other tables. On the other hand, large and frequently changing data items are not always a good choice for redundant copying.
Criteria for redundancy
Criteria for redundancy

Redundancy tradeoffs

The rule is simple: the more redundancy that you introduce into your data model, the less SQL joins that are incurred in your Oracle design, and the faster your queries will perform. You must balance the costs of adding redundant data (i.e. extra disk cost, longer update jobs) with the benefits of faster SQL queries. We will delve into logical design in more detail.