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




Pre-Oracle8i Star Join Execution
Oracle Tips by Burleson

As we noted, prior to Oracle8i you must have a concatenated index on all columns of the fact table. During the STAR join, Oracle will first service the queries against the smaller dimension tables, combining the result set into a Cartesian product table that is held in Oracle memory. This virtual table will contain all of the columns from all of the participating dimension tables. The primary key for this virtual table will be a composite of all of the keys for the dimension tables. If this key matches the composite index on the fact table, then the query will be able to process very quickly. Once the sum of the reference tables has been addressed, Oracle will perform a nested loop join of the intermediate table against the fact table.

Oracle8i parallel bitmap star join execution

In Oracle8i, bitmap indexes are required for all join columns on the fact table, and Oracle8i will initially use these bitmap indexes as a path to the fact table.  The SQL optimizer will then re-write the original query, replacing the equi-join criteria with sub-queries using the IN clause.  These sub-queries are used as sources of keys to drive the bitmap index accesses, using bitmap key iteration to access the dimension tables. Once the resulting bitmap-ROWID lists are retrieved, Oracle will use a hash join to merge the result sets.

To see how the Oracle SQL optimizer transforms a star query, consider the following query where we sum the sales by region for all southern regions during the months of March and April:

   sales.store_key = store.store_key
   sales.month = time.month
   store.region = `south’                
   time.month in (`01-03’, `01-04’)                 
group by
   store.region, time.month

The star optimizer replaces the where clauses as follows. Note that the equi-join criteria is replaced by a sub-query using the IN clause.

  • Region clause before star transformation:

   store.region = `south’
   sales.store_key = store.store_key

  • Region clause after star transformation:

   sales.store_key in (select store_key from store
                        where region = `south’)

We see a similar transformation in the join into the time table:

  • Month clause before star transformation:

   sales.month = time.month
   time.month in (`01-03’, `01-04’)

  • Month clause after star transformation:

   sales.month in (select month from time
              where month in (`01-03’, `01-04’))

As we see, the query is significantly transformed, replacing all where clause entries for the dimension table with a single sub-select statement.  These IN sub-queries are ideal for the use of bitmap indexes because the bitmap can quickly scan the low-cardinality columns in the bitmap and produce a ROWID list of rows with matching values.

This approach 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. The speed is a result of reducing the physical I/O. The indexes are read to gather the virtual table in memory, and the fact table will not be accessed until the virtual index has everything it requires to go directly to the requested rows via the composite index on the fact table (Figure 16-11).

Starting with Oracle8i, the requirement for a concatenated index has changed, and the STAR hint requires bitmap indexes. The bitmap indexes can be joined more efficiently than a concatenated index, and they provide a faster result.

Figure 11: Oracle star query processing

As I have noted, the star query can be very tricky to implement, and careful consideration must be given to the proper placement of indexes. Each dimension table must have an index on the join key, and in Oracle7 and Oracle8, the large fact table must have a composite index consisting of all of the join keys from all of the dimension tables, while in Oracle8i you need bitmap indexes on the fact table. In addition, the sequencing of the keys in the fact table composite index must be in the correct order, or Oracle will not be able to use the index to service the query.

Next, let’s move on and look at some other important issues surrounding Oracle table joins. As I have noted in earlier chapters, Oracle must go to a great deal of work during the parse phase of a query to determine the optimal table join order when many tables are being joined.

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