STAR Schema Design Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Data Warehouse Design

Massive De-normalization: STAR Schema Design

The STAR schema design was first introduced by Dr. Ralph Kimball as an alternative database design for data warehouses. The name STAR comes directly from the design form, where a large fact table resides at the center of the model surrounded by various points, or reference tables. The basic principle behind the STAR query schema is the introduction of highly redundant data for high performance. With a STAR schema, the designer can simulate the functions of a multidimensional database without having to purchase expensive third-party software. Kimball describes de-normalization as the pre-joining of tables, such that the runtime application does not have to join tables. At the heart of the STAR schema, the fact table is usually comprised entirely of key values and raw data. A fact table is generally very long and may have millions of rows.

Surrounding the fact table is a series of dimension tables which serve to add value to the base information in the fact table. For example, consider the E/R model for the sales database shown in Figure 4.3.

Here, we see a standard third normal form (3NF) database used to represent the sales of items. No redundant information is given; therefore, salient data such as the total for an order would have to be computed from the atomic items that comprise the order. In this 3NF database, a list of line items would need to be created, multiplying the quantity ordered by the price for all items that belong in order 123.

In the following example, an intermediate table called TEMP is created to hold the result list:

---

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.