The Star Join
Oracle Tips by
The star join has its roots firmly
planted in data warehouse design. Dr. Ralph Kimball, founder of Red
popularized the term star schema to describe a de-normalization
process that simulates the structure of a multidimensional database.
With a star schema, the designer can simulate the functions of a
multidimensional database without having to purchase expensive
third-party software. Dr. Kimball describes the de-normalization
process as the pre-joining of tables, such that the run-time SQL
application does not have to join the tables at execution time.
At the heart of
the star schema is a fact table, a long and wide table that is
usually composed entirely of key values and raw facts. A fact table is
generally very long and wide and may have millions of rows.
Surrounding the fact table are a series of dimension tables that serve
to add value to the base information in the fact table. For example,
consider the E/R model for a sales database shown in Figure 16-10.
Figure 10: An E/R model
for a sales database
Here we see a standard third normal form (3NF)
database to represent the sale of items. No redundant information is
provided in this design, and aggregated reports such as sales
summaries would need to be displayed by joining many tables together.
Clearly, high volumes of queries that summarize
information would benefit if we could pre-join the tables into a
single table with redundant values. Here we have used the Create Table
As Select (CTAS) syntax to de-normalize the third normal form table
into a large fact table.
item.item_nbr = line_item.iten_nbr
order.product_id = line_item.product_id
customer_cust_nbr = order.cust_nbr;
Here we have selected the keys from all of the
tables, and we have pre-joined a fact table. In this case, the facts
are quantity_sold and sale_amount, and all of the other
information exists as keys into the dimension tables. For example, the
product_nbr would be the key for the product dimension, and we
would reference this key to join into the product table for additional
details. For example, a data warehouse asking to summarize
sale_amount by product_shelf_life would require a join from
the fact table into the product table in order to get the
This massive de-normalization of the database
structure means that just about any query against the star schema is
going to involve the joining of many large tables—including a large
“fact” table and many smaller reference tables. Oracle has provided
star query joins that employ a special procedure to improve
performance of the query. Oracle does not require the use of a star
hint to invoke a star join. All that is required is the proper
index on the fact table, and the Oracle cost-based optimizer will
invoke the star join. However, the star hint is still allowed
in the SQL syntax and are generally a good idea for documentation
purposes. Prior to Oracle8i, the star query requires that a single
concatenated index resides in the fact table for all keys, but this
was changed to bitmap indexes in Oracle8i.
Requirements for a Star Join
The star join is far faster than the
traditional method of joining the smallest reference table against the
fact table and then joining each of the other reference tables against
the intermediate table. To invoke the star join, the following
prerequisites must be present:
There must be at least three tables being
joined, one large fact table and several smaller dimension tables.
Up until Oracle8, there must be a concatenated
index on the fact table with at least three columns, one for each of
the table join keys. Starting with Oracle8i, bitmap index structures
may also be used.
You must verify with an explain plan that the
NESTED LOOPS table access operation is being used to perform the
Oracle follows a
different procedure for processing star queries, depending on your
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.