-- BJI may not always win. In this demo, it lost out to a simple hash join, -- and ORACLE correctly refused to use it until a hint was given to force it. -- Note that the fact table is partitioned, dimension tables are not. SQL> SQL> -- On a star schema SQL> SQL> set timing on SQL> SQL> SELECT sum(sales.amount_sold), 2 customers.cust_gender, customers.cust_marital_status 3 FROM sales, customers 4 WHERE sales.cust_id = customers.cust_id 5 group by customers.cust_gender, customers.cust_marital_status; SUM(SALES.AMOUNT_SOLD) C CUST_MARITAL_STATUS ---------------------- - -------------------- 97682010.3 F 96622380.8 F single 66450879.9 F married 171789028 M 172966950 M single 122439595 M married 6 rows selected. Elapsed: 00:00:08.04 SQL> CREATE BITMAP INDEX sales_cust_gender_ms_bjix 2 ON sales(customers.cust_gender, customers.cust_marital_status) 3 FROM sales, customers 4 WHERE sales.cust_id = customers.cust_id 5 LOCAL NOLOGGING; Index created. Elapsed: 00:00:52.05 SQL> set autotrace on explain SQL> SELECT sum(sales.amount_sold), 2 customers.cust_gender, customers.cust_marital_status 3 FROM sales, customers 4 WHERE sales.cust_id = customers.cust_id 5 group by customers.cust_gender, customers.cust_marital_status; SUM(SALES.AMOUNT_SOLD) C CUST_MARITAL_STATUS ---------------------- - -------------------- 97682010.3 F 96622380.8 F single 66450879.9 F married 171789028 M 172966950 M single 122439595 M married 6 rows selected. Elapsed: 00:00:07.06 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4396 Card=3 Bytes=66 ) 1 0 SORT (GROUP BY) (Cost=4396 Card=3 Bytes=66) 2 1 HASH JOIN (Cost=1129 Card=1016271 Bytes=22357962) 3 2 TABLE ACCESS (FULL) OF 'CUSTOMERS' (Cost=106 Card=5000 0 Bytes=650000) 4 2 PARTITION RANGE (ALL) 5 4 TABLE ACCESS (FULL) OF 'SALES' (Cost=469 Card=101627 1 Bytes=9146439) SQL> SELECT sum(sales.amount_sold) 2 FROM sales, customers 3 WHERE sales.cust_id = customers.cust_id 4 AND customers.cust_gender='M' 5 AND customers.cust_marital_status='single'; SUM(SALES.AMOUNT_SOLD) ---------------------- 172966950 Elapsed: 00:00:03.05 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=805 Card=1 Bytes=22) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=805 Card=1016240 Bytes=22357280) 3 2 TABLE ACCESS (FULL) OF 'CUSTOMERS' (Cost=106 Card=8199 Bytes=106587) 4 2 PARTITION RANGE (ALL) 5 4 TABLE ACCESS (FULL) OF 'SALES' (Cost=469 Card=101627 1 Bytes=9146439) SQL> analyze index sales_cust_gender_ms_bjix compute statistics; Index analyzed. Elapsed: 00:00:00.09 SQL> SELECT sum(sales.amount_sold) 2 FROM sales, customers 3 WHERE sales.cust_id = customers.cust_id 4 AND customers.cust_gender='M' 5 AND customers.cust_marital_status='single'; SUM(SALES.AMOUNT_SOLD) ---------------------- 172966950 Elapsed: 00:00:03.02 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=805 Card=1 Bytes=22) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=805 Card=1016240 Bytes=22357280) 3 2 TABLE ACCESS (FULL) OF 'CUSTOMERS' (Cost=106 Card=8199 Bytes=106587) 4 2 PARTITION RANGE (ALL) 5 4 TABLE ACCESS (FULL) OF 'SALES' (Cost=469 Card=101627 1 Bytes=9146439) SQL> select /*+ index (sales sales_cust_gender_ms_bjix) */ 2 sum(sales.amount_sold) 3 FROM sales, customers 4 WHERE sales.cust_id = customers.cust_id 5 AND customers.cust_gender='M' 6 AND customers.cust_marital_status='single'; SUM(SALES.AMOUNT_SOLD) ---------------------- 172966950 Elapsed: 00:00:09.01 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1976 Card=1 Bytes=9) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SALES' (Cost=1 976 Card=166643 Bytes=1499787) 4 3 BITMAP CONVERSION (TO ROWIDS) 5 4 BITMAP INDEX (SINGLE VALUE) OF 'SALES_CUST_GENDER_ MS_BJIX' SQL> spool off