Oracle normalization tips Oracle Tips by Burleson Consulting

Over-Normalization of Oracle Entities

Some Oracle databases were modeled according to the rules of normalization that were intended to eliminate redundancy.  However, a pure third-normal form (3NF) database can cause high overhead on the Oracle database and over-normalization is a big problem, especially in databases that were designed when disk cost was $250,000 per gigabyte.

I once met Ted Codd at the Database World Conference where we both were speaking, and I asked him how he came-up with the word “Normalization”.  He said that Nixon was normalizing relations with China at the time, and if Nixon could normalize relations, then so could he!

Obviously, the rules of Oracle normalization are required to understand your relationships and functional dependencies, but BCNF is just a starting point, not a completed data model.  Legacy systems tended to be more highly normalized than today's databases because of the high cost of disk in the 1980's and 1990's.  But disk is cheap today, RAM-SAN is coming, and denormalization is a Godsend. 

If we introduce redundancy to reduce joins, we can retrieve high-volume queries with far less runtime overhead.  In many OLTP systems there are usually a small set of queries that account for 90% of the overhead, and these queries are the starting point for a change in Oracle normalization.  I've seen databases where a single query form (show me all items for an order) was 75% of system traffic.

In addition, Oracle offers several popular denormalization tools, some that create non first-normal form structures (0NF):

• Normalization & Object tables – Oracle has nested tables and varray table columns whereby repeating groups are stored within a row, violating 1NF.
   
• Denormalization & 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.

How and where do we introduce redundancy to remove table joins?  The answer depends on the “redundancy boundary”, a function of the size and volatility of the redundant item.  Let’s look at a real example where an 6-way table join was required to display basic information about people. 

Is this high-level of normalization required just to display a person?  Optimizing the SQL is time-consuming and the query would be required to do at least five logical I/O's. 

When we de-normalize to introduce redundancy to improve performance and simplify the data model, we must always remember that we have to code to go to several tables to update the redundant data item.  This overhead can be huge if we have large data items that change frequently.  In this example system, data was to be stored with the historical values.

Now, in all fairness, a fully normalized Oracle design (3NF) design was perfect in 1986 when disk was expensive. Today, over-normalization of Oracle databases adds a huge burden on a high-performance online transaction processing (OLTP) database:

• Complexity to the developers (lots of extra coding for n-way table joins)
   
• Run-time overhead (complex SQL pre-processing by the CBO)
   
• Higher disk I/O (many data blocks must be visited to fetch related data)

This example illustrates the huge problems associated with fixing a “bad” schema and shows how it can be a huge and expensive undertaking to denormalize a schema in Oracle to reduce unnecessary table joins.  Some Oracle professionals use Materialized Views to de-normalize 3NF structures, but this only works in cases where the data changes infrequently.