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
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.