Star Queries

If you have a query that joins a large (fact) table to a number of small (dimension) tables, and you are providing WHERE predicates on the dimension tables to reduce the result set to a very small percentage of the fact table, then there are some features that can optimize this style of query.

Without Bitmap Indexes

Prior to V7.3 of Oracle, Bitmap Indexes were not available. Star Queries developed at this time had to use a technique that used only regular b-tree indexes. This technique is now deprecated in favour of Bitmap Indexes.

The b-tree index solution involves creating a single concatenated index on the fact table that includes all of the foreign keys of the dimension tables, and then providing the STAR hint to the Star Query. Oracle will perform a cartesian join of the dimension tables, and then perform Nested Loops indexed access on the fact table. See the STAR hint in the Oracle Tuning manual for more detail.

If you are going to use this old technique, beware the following:

• Cartesian products grow very quickly unless the WHERE clauses on the dimensions are constrained to just a few rows. If the cartesian product of the dimension blows out to say 10,000, then we cannot expect brilliant performance from 10,000 Indexed lookups on the fact table.
• If you use queries with different combinations of dimension tables, then you need a separate concatenated index on the fact table for each combination. It may not be possible to index all combinations that are used in common end-user queries
• Do not fall into the trap of using single column b-tree indexes on the fact table in an attempt to replicate the way appearance of the bimap index technique described below. This would result in an AND-EQUAL abomination.

With Bitmap Indexes

This section pertains to the situation where a fact table with Bitmap Indexes is joined to two or more dimension tables. If your query is simply selecting from a single table and attempting to use Bitmap Indexes, then you should read Index Combine.

Consider the following query taken from Oracle's Data Warehousing Guide:

SELECT store.sales_district
,      time.fiscal_period
,      SUM(sales.dollar_sales) revenue
,      SUM(dollar_sales) - SUM(dollar_cost) income
FROM   sales, store, time, product
WHERE  sales.store_key = store.store_key
AND    sales.time_key = time.time_key
AND    sales.product_key = product.product_key
AND    time.fiscal_period IN ('3Q95', '4Q95', '1Q96')
AND    product.department = 'Grocery'
AND    store.sales_district IN ('San Francisco', 'Los Angeles')
GROUP
BY     store.sales_district, time.fiscal_period;

In its current form, Oracle is unable to use the Bitmap Indexes on the sales table, because there are no predicates that compare them directly to fixed expressions: they are all used in join predicates.

A better way to write the query would be:

SELECT ...
FROM   sales
WHERE  store_key IN (
       SELECT store_key
       FROM   store
       WHERE  sales_district IN ('WEST', 'SOUTHWEST'))
AND    time_key IN (
       SELECT time_key
       FROM   time
       WHERE  quarter IN ('3Q96', '4Q96', '1Q97'))
AND    product_key IN (
       SELECT product_key
       FROM   product
       WHERE  department = 'GROCERY');

In this example, Oracle can execute the 3 sub-queries to get a list of foreign keys to match with the fact table, retrieve the bitmaps for each foreign key, combine them, and then retrieve the resultant rows.

The amazing thing is: you don't have to re-write your query, Oracle can do it for you. In the right circumstances, when Oracle sees a query structured like the top one above, it will execute as if it had been written like the bottom query above. Oracle calls this Star Transformation, and it works automatically if the DBA has the Database Parameter STAR_TRANSFORMATION_ENABLED. Otherwise you must add the STAR_TRANSFORMATION hint to your SQL.