SQL> SQL> -- On a snow flake schema SQL> -- Primary keys on all the dimension table, lack of FKs don't seem to matter. SQL> SQL> drop table test1_a; drop table test1_a * ERROR at line 1: ORA-00942: table or view does not exist SQL> drop table test2_a; drop table test2_a * ERROR at line 1: ORA-00942: table or view does not exist SQL> drop table test3_a; drop table test3_a * ERROR at line 1: ORA-00942: table or view does not exist SQL> create table test1_a as select * from test1; Table created. SQL> create table test2_a as select * from test2; Table created. SQL> alter table test2_a add constraint test2_a_pk primary key(c1); Table altered. SQL> create table test3_a as select * from test3; Table created. SQL> SQL> -- Need unique or primary "constraint" SQL> alter table test3_a add constraint test3_a_u unique (c2); Table altered. SQL> SQL> create bitmap index test1_a_bji on test1_a(t2.c1,t3.c2) 2 from test1_a t1, test2_a t2, test3_a t3 3 where t1.c1=t2.c1 and t2.c2=t3.c2; Index created. SQL> SQL> set autotrace on explain SQL> SQL> -- Note that table alias must be used within the hint SQL> -- Strange plan when t2.c1 is included in the bitmap SQL> select/*+ index (t1 test1_a_bji) */ sum(sumc2) from test1_a t1, test2_a t2, test3_a t3 2 where t1.c1=t2.c1 and t2.c2=t3.c2 3 and t3.c2 in (1,2,3); SUM(SUMC2) ---------- 1060 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=1 Bytes=45) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=44 Card=3 Bytes=135) 3 2 NESTED LOOPS (Cost=2 Card=3 Bytes=87) 4 3 TABLE ACCESS (FULL) OF 'TEST2_A' (Cost=2 Card=3 Byte s=48) 5 3 INLIST ITERATOR 6 5 INDEX (UNIQUE SCAN) OF 'TEST3_A_U' (UNIQUE) 7 2 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1_A' (Cost=41 Ca rd=327 Bytes=5232) 8 7 BITMAP CONVERSION (TO ROWIDS) 9 8 BITMAP INDEX (FULL SCAN) OF 'TEST1_A_BJI' SQL> SQL> analyze table test1_a compute statistics; Table analyzed. SQL> SQL> -- Analyze does not help SQL> select/*+ index (t1 test1_a_bji) */ sum(sumc2) from test1_a t1, test2_a t2, test3_a t3 2 where t1.c1=t2.c1 and t2.c2=t3.c2 3 and t3.c2 in (1,2,3); SUM(SUMC2) ---------- 1060 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=1 Bytes=33) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=17 Card=7 Bytes=231) 3 2 NESTED LOOPS (Cost=2 Card=3 Bytes=87) 4 3 TABLE ACCESS (FULL) OF 'TEST2_A' (Cost=2 Card=3 Byte s=48) 5 3 INLIST ITERATOR 6 5 INDEX (UNIQUE SCAN) OF 'TEST3_A_U' (UNIQUE) 7 2 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1_A' (Cost=13 Ca rd=23 Bytes=92) 8 7 BITMAP CONVERSION (TO ROWIDS) 9 8 BITMAP INDEX (FULL SCAN) OF 'TEST1_A_BJI' SQL> SQL> drop index test1_a_bji; Index dropped. SQL> SQL> -- Remove t2.c1 from the index SQL> create bitmap index test1_a_bji on test1_a(t3.c2) 2 from test1_a t1, test2_a t2, test3_a t3 3 where t1.c1=t2.c1 and t2.c2=t3.c2; Index created. SQL> SQL> -- This one works SQL> select/*+ index (t1 test1_a_bji) */ sum(sumc2) from test1_a t1, test2_a t2, test3_a t3 2 where t1.c1=t2.c1 and t2.c2=t3.c2 3 and t3.c2 in (1,2,3); SUM(SUMC2) ---------- 1060 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=4) 1 0 SORT (AGGREGATE) 2 1 INLIST ITERATOR 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1_A' (Cost=4 Car d=1 Bytes=4) 4 3 BITMAP CONVERSION (TO ROWIDS) 5 4 BITMAP INDEX (SINGLE VALUE) OF 'TEST1_A_BJI' SQL> SQL> -- Why does this one only partialy works? SQL> select/*+ index (t1 test1_a_bji) */ sum(sumc2) from test1_a t1, test2_a t2, test3_a t3 2 where t1.c1=t2.c1 and t2.c2=t3.c2 3 and t3.c2=1; SUM(SUMC2) ---------- 280 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=33) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=6 Card=2 Bytes=66) 3 2 NESTED LOOPS (Cost=3 Card=1 Bytes=29) 4 3 INDEX (UNIQUE SCAN) OF 'TEST3_A_U' (UNIQUE) (Cost=1 Card=1 Bytes=13) 5 3 TABLE ACCESS (FULL) OF 'TEST2_A' (Cost=2 Card=1 Byte s=16) 6 2 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1_A' (Cost=1 Car d=1 Bytes=4) 7 6 BITMAP CONVERSION (TO ROWIDS) 8 7 BITMAP INDEX (SINGLE VALUE) OF 'TEST1_A_BJI' SQL> SQL> -- Analyze the new index does not help SQL> analyze index test1_a_bji compute statistics; Index analyzed. SQL> SQL> select/*+ index (t1 test1_a_bji) */ sum(sumc2) from test1_a t1, test2_a t2, test3_a t3 2 where t1.c1=t2.c1 and t2.c2=t3.c2 3 and t3.c2=1; SUM(SUMC2) ---------- 280 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=33) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=6 Card=2 Bytes=66) 3 2 NESTED LOOPS (Cost=3 Card=1 Bytes=29) 4 3 INDEX (UNIQUE SCAN) OF 'TEST3_A_U' (UNIQUE) (Cost=1 Card=1 Bytes=13) 5 3 TABLE ACCESS (FULL) OF 'TEST2_A' (Cost=2 Card=1 Byte s=16) 6 2 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1_A' (Cost=1 Car d=1 Bytes=4) 7 6 BITMAP CONVERSION (TO ROWIDS) 8 7 BITMAP INDEX (SINGLE VALUE) OF 'TEST1_A_BJI' SQL> SQL> drop table test1_a; Table dropped. SQL> drop table test2_a; Table dropped. SQL> drop table test3_a; Table dropped. SQL> SQL> spool off