Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




The Star Join
Oracle Tips by Burleson

The star join has its roots firmly planted in data warehouse design. Dr. Ralph Kimball, founder of Red Brick systems, 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.

create 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 product_shelf_life column.

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

Oracle follows a different procedure for processing star queries, depending on your Oracle version.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter