* Why? How? The star transformation is a cost-based query transformation aimed at executing star queries efficiently. Oracle processes a star query using two basic phases. The first phase retrieves exactly the necessary rows from the fact table (the result set). Because this retrieval utilizes bitmap indexes, it is very efficient. The second phase joins this result set to the dimension tables. * The requirements: 1. A bitmap index should be built on each of the foreign key columns of the fact table or tables. 2. The initialization parameter STAR_TRANSFORMATION_ENABLED should be set to true. This enables an important optimizer feature for star-queries. It is set to false by default for backward-compatibility. 3. The cost-based optimizer should be used. SQL> show parameter star_tran NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ star_transformation_enabled string FALSE SQL> select index_name,index_type from user_indexes where table_name='SALES'; INDEX_NAME INDEX_TYPE ------------------------------ --------------------------- SALES_CHANNEL_BIX BITMAP SALES_CUST_BIX BITMAP SALES_PROD_BIX BITMAP SALES_PROMO_BIX BITMAP SALES_TIME_BIX BITMAP SQL> select constraint_name from user_constraints where table_name='SALES' and constraint_type='R'; CONSTRAINT_NAME ------------------------------ SALES_PRODUCT_FK SALES_CUSTOMER_FK SALES_TIME_FK SALES_CHANNEL_FK SALES_PROMO_FK SQL> set autotrace on explain SQL> SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc, 2 SUM(s.amount_sold) sales_amount 3 FROM sales s, times t, customers c, channels ch 4 WHERE s.time_id = t.time_id 5 AND s.cust_id = c.cust_id 6 AND s.channel_id = ch.channel_id 7 AND c.cust_state_province = 'CA' 8 AND ch.channel_desc in ('Internet','Catalog') 9 AND t.calendar_quarter_desc IN ('1999-Q1','1999-Q2') 10 GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc; CHANNEL_CLASS CUST_CITY CALENDA SALES_AMOUNT -------------------- ------------------------------ ------- ------------ Indirect Pala 1999-Q1 26378.7 Indirect Pala 1999-Q2 19729.3 Indirect Montara 1999-Q1 363567.3 Indirect Montara 1999-Q2 389600.4 Indirect Arbuckle 1999-Q1 186887.85 Indirect Arbuckle 1999-Q2 214039.1 Indirect San Mateo 1999-Q1 67989.55 Indirect San Mateo 1999-Q2 87775.7 Indirect Cloverdale 1999-Q1 107198.3 Indirect Cloverdale 1999-Q2 76081.65 Indirect Quartzhill 1999-Q1 30676.8 CHANNEL_CLASS CUST_CITY CALENDA SALES_AMOUNT -------------------- ------------------------------ ------- ------------ Indirect Quartzhill 1999-Q2 24130.5 Indirect El Sobrante 1999-Q1 97719.7 Indirect El Sobrante 1999-Q2 98548 Indirect Los Angeles 1999-Q1 345436.7 Indirect Los Angeles 1999-Q2 353959.25 16 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=756 Card=530 Bytes=4 3460) 1 0 SORT (GROUP BY) (Cost=756 Card=530 Bytes=43460) 2 1 HASH JOIN (Cost=605 Card=15841 Bytes=1298962) 3 2 TABLE ACCESS (FULL) OF 'CUSTOMERS' (Cost=106 Card=345 Bytes=9315) 4 2 HASH JOIN (Cost=494 Card=72076 Bytes=3964180) 5 4 TABLE ACCESS (FULL) OF 'CHANNELS' (Cost=2 Card=2 Byt es=40) 6 4 HASH JOIN (Cost=488 Card=180191 Bytes=6306685) 7 6 TABLE ACCESS (FULL) OF 'TIMES' (Cost=6 Card=183 By tes=2928) 8 6 PARTITION RANGE (ITERATOR) 9 8 TABLE ACCESS (FULL) OF 'SALES' (Cost=469 Card=10 16271 Bytes=19309149) SQL> alter session set star_transformation_enabled=true; Session altered. SQL> SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc, 2 SUM(s.amount_sold) sales_amount 3 FROM sales s, times t, customers c, channels ch 4 WHERE s.time_id = t.time_id 5 AND s.cust_id = c.cust_id 6 AND s.channel_id = ch.channel_id 7 AND c.cust_state_province = 'CA' 8 AND ch.channel_desc in ('Internet','Catalog') 9 AND t.calendar_quarter_desc IN ('1999-Q1','1999-Q2') 10 GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc; CHANNEL_CLASS CUST_CITY CALENDA SALES_AMOUNT -------------------- ------------------------------ ------- ------------ Indirect Pala 1999-Q1 26378.7 Indirect Pala 1999-Q2 19729.3 Indirect Montara 1999-Q1 363567.3 Indirect Montara 1999-Q2 389600.4 Indirect Arbuckle 1999-Q1 186887.85 Indirect Arbuckle 1999-Q2 214039.1 Indirect San Mateo 1999-Q1 67989.55 Indirect San Mateo 1999-Q2 87775.7 Indirect Cloverdale 1999-Q1 107198.3 Indirect Cloverdale 1999-Q2 76081.65 Indirect Quartzhill 1999-Q1 30676.8 CHANNEL_CLASS CUST_CITY CALENDA SALES_AMOUNT -------------------- ------------------------------ ------- ------------ Indirect Quartzhill 1999-Q2 24130.5 Indirect El Sobrante 1999-Q1 97719.7 Indirect El Sobrante 1999-Q2 98548 Indirect Los Angeles 1999-Q1 345436.7 Indirect Los Angeles 1999-Q2 353959.25 16 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=225 Card=5 Bytes=350 ) 1 2 RECURSIVE EXECUTION OF 'SYS_LE_2_0' 2 0 TEMP TABLE TRANSFORMATION 3 2 SORT (GROUP BY) (Cost=225 Card=5 Bytes=350) 4 3 HASH JOIN (Cost=224 Card=5 Bytes=350) 5 4 HASH JOIN (Cost=221 Card=25 Bytes=1375) 6 5 HASH JOIN (Cost=214 Card=141 Bytes=5499) 7 6 TABLE ACCESS (FULL) OF 'CHANNELS' (Cost=2 Card=2 Bytes=40) 8 6 PARTITION RANGE (ITERATOR) 9 8 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SALES' (Cost=210 Card=351 Bytes=6675) 10 9 BITMAP CONVERSION (TO ROWIDS) 11 10 BITMAP AND 12 11 BITMAP MERGE 13 12 BITMAP KEY ITERATION 14 13 BUFFER (SORT) 15 14 TABLE ACCESS (FULL) OF 'CHANNELS' (Cost=2 Card=2 Bytes=40) 16 13 BITMAP INDEX (RANGE SCAN) OF 'SALES_CHANNEL_BIX' 17 11 BITMAP MERGE 18 17 BITMAP KEY ITERATION 19 18 BUFFER (SORT) 20 19 TABLE ACCESS (FULL) OF 'TIMES' (Cost=6 Card=183 Bytes=2928) 21 18 BITMAP INDEX (RANGE SCAN) OF 'SALES_TIME_BIX' 22 11 BITMAP MERGE 23 22 BITMAP KEY ITERATION 24 23 BUFFER (SORT) 25 24 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_569904F' (Cost=2 Card=1 Bytes=13) 26 23 BITMAP INDEX (RANGE SCAN) OF 'SALES_CUST_BIX' 27 5 TABLE ACCESS (FULL) OF 'TIMES' (Cost=6 Card=183 Bytes=2928) 28 4 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_569904F' (Cost=2 Card=345 Bytes=5175) SQL> spool off