Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Data Warehouse Design

DE-NORMALIZING ONE-TO-MANY DATA RELATIONSHIPS

What about the cost_of_living field? If we choose to eliminate the CITY table and duplicate cost_of_living in every CUSTOMER row, it would be necessary to visit each and every customer row--which means changing cost_of_living 10,000 times. Before making this change, the following SQL should be used to update each CITY table:

UPDATE CITY SET cost_of_living = :var1
SEE CODE DEPOT FOR FULL SCRIPT

While the management of redundancy seems a formidable challenge, the following SQL UPDATE statement makes this change easily, and we can make the change to all affected rows as follows:

UPDATE CUSTOMER SET cost_of_living = :var1
SEE CODE DEPOT FOR FULL SCRIPT

Using the same state_bird query as before, we can see how it is simplified by removing the extra tables, as follows:

SELECT state_bird
FROM CUSTOMER, ORDER, QUANTITY, ITEM
SEE CODE DEPOT FOR FULL SCRIPT

It is still necessary to join all four tables together, but this results in a much faster, simpler query than the original five-way table join. You can carry this concept to the point where this model is condensed into a single, highly redundant table.

---

This is an excerpt from "High Performance Data Warehousing". To learn more about Oracle, try "Oracle Tuning: The Definitive Reference", by Donald K. Burleson.  You can buy it direct from the publisher at 30% off here:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm