Data Warehouse SQL Joins Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Data Warehouse Design

DE-NORMALIZING ONE-TO-MANY DATA RELATIONSHIPS

One-to-many relationships exist in many real-world situations. Many entities that possess one-to-many relationships can be removed from the data model, eliminating some join operations. The basic principle here is simple: Redundant information avoids expensive SQL joins and yields faster processing. But remember, we must deal with the issue of additional disk storage and the problems associated with updating the redundant data. For example, consider the entity/relation (E/R) model shown in Figure 4.3.

Figure 4.3 A fully normalized E/R model sales database.

Here, we see that the structure is in pure third normal form. Note that the CITY and STATE tables exist because each state has many cities and each city has many customers. This model works for most transactions on an online transaction processing (OLTP) system. However, this high degree of normalization would require the joining of the CITY and STATE tables each time address information is requested, forcing some SQL requests to perform very slowly.

Consider a query to display the state_bird for all orders that have been placed for birdseed. This is a cumbersome query that requires the joining of six tables, as follows:

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

---

This is an excerpt from "High Performance Data Warehousing", copyright 1997.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.