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