Oracle normalization & Performance 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 $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 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 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):

• Object tables – Oracle has 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.

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 plan 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 3NF design was perfect in 1986 when disk was expensive.  Today, over-normalization 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 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. 

A real-world case of over-normalization

This database stored information about establishments and their addresses, a legitimate many-to-many relationship that would normally require three tables.  In this case there were five “lookup” tables that linked to two-column tables.  From a pure theoretical perspective we could model this many-to-many relationship in 3NF with 8 tables; a ludicrous approach causing unnecessary 8-way tables joins simply to display the basic information about an establishment.  Primary keys are yellow and foreign keys are blue:

tai_store_entity_type

tai_store_entity

tai_entity_address

tai_address

tai_address_type

tai_state

tai_country

tai_parish

tai_district

We also have “type” lookup tables, in this case a two-column table to allow for changes to address types.  Unfortunately, using this approach would corrupt historical data, because the system does not want history information to change.

tai_address_type

Here is another example of an inappropriate lookup table, doing nothing but creating system overhead.  In this case, a table join is required on the key HOTE in order to get the full-name HOTEL:

tai_store_type

We can remove the complexity for the developers be encapsulating the complexity with a view (named store_details_view), but we still have the run-time overhead of processing a complex 8-way table join.  All of this complex SQL, just to display a business address! 

This is the syntax for a store_details_view that joins all eight tables.  This can be simplified by removing the incorrect design of the lookup tables.  Here is a view to encapsulate this mess.  To Oracle, parsing an 8-way table join is very time-consuming since Oracle must evaluate 8! (eight factorial) possible table join combinations:

Towards a More Appropriate Model

We must model the many-to-many between people and previous addresses and between establishments and address types.  The unnecessary joins include the five lookup tables and the surrogate foreign keys must be removed and the values inserted into the main tables.  To minimize code re-writing, the lookup table remain as-is with an unused surrogate key. 

This will reduce the 8-way join into a manageable (and logically correct) 3-way join to cross the many-to-many relationship.  This structure removes the five of the lookup tables without breaking any existing system code, all by adding the descriptive column to the table.

1. Alter table xxx add descriptive_column
    
2. Update xxx set descriptive_column (select description from lookup_table)
    
3. Alter table xxx drop column surrogate_lookup_key

Need an Oracle Health Check? Do you have bad performance after an upgrade?   Need to certify that your database follows best practices? BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.