RelationalDBDesign RelationalDBDesign 

Index Techniques   «Prev 

Star Query Process

A Star query is a join between a fact table and a number of dimension tables.
  1. Each dimension table is joined to the fact table using a primary key to foreign key join
  2. The dimension tables are not joined to each other
  3. The Oracle 11g cost-based optimizer recognizes star queries and generates the most efficient execution plans for them

1) 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 RAM in the shared pool. This virtual table will contain all of the columns from all of the participating DIMENSION tables.

2) The primary key for this virtual table will be a composite of all of the keys for the DIMENSION tables.

Data Mining Business Analytics
3) If this key matches the composite index on the FACT table, then the query will be able to process very quickly.

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