Question:
I have a discussion where a colleague says that we must start by
designing our Oracle tables in third normal form (3NF). I was
told that 3NF is only a starting point, and that oracle suggests
that the DBA deliberately add redundant data to reduce table joins.
Who is correct?
Answer: It's been proven that the
intelligent introduction of redundancy can improve performance and
Oracle has many tools that help introduce redundant, non
third-normal form data (materialized views, replication, VARRAY
tables).
But the degree of database normalization is tied directly to
hardware costs. Back in the 1980's, a gigabyte of disk was the
size of a refrigerator and cost over $2000,000. Back when disk
was super-expensive DBA's did not have the luxury of de-normalizing
table for performance, and virtually all relational databases were
in third normal form, or even beyond 3NF to Boyce-Codd Normal Form (BCNF).
But now that disk is cheap, the rules have changed. In
addition to replication (Streams, Data Guard, and multi-master) and
materialized views, Oracle offers
several popular denormalization tools, some that create non
first-normal form structures (0NF):
-
Object tables - Oracle
has 0NF
tables nested tables and varray table columns whereby
repeating groups are stored within a row, violating 1NF.
-
Materialized Views -
Tables are pre-joined together, queries are re-written to access
the MV, and a method (Oracle snapshots) keeps the
denormalization in-sync with the normalized representation of
the data.
Violating third normal form
Even today, the Oracle designer start with a third normal form
model and then intelligently introduces redundant data items to
reduce unnecessary SQL joins. The decision to introduce
redundancy is based in three issues:
The amount of SQL that will experience less table joins:
Pre-joining table together is best when lots of SQL statements will
benefit from it.
The size of the redundant item: Large items consume more
disk than small items
The volatility of the data item: Remember, redundant items
must be keep synchronized in many tables. Seldom updated data
items are better for adding redundancy than frequently-updated
items.
Related database design notes:
See my important notes on:
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
