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> -- Won't work without a PK on snow flaked dimension 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; from test1_a t1, test2_a t2, test3_a t3 * ERROR at line 2: ORA-25954: missing primary key or unique constraint on dimension SQL> SQL> -- Unique index alone won't work. SQL> create unique index test3_a_u on test3_a(c2); Index created. 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; from test1_a t1, test2_a t2, test3_a t3 * ERROR at line 2: ORA-25954: missing primary key or unique constraint on dimension 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> select index_type, join_index from user_indexes where index_name='TEST1_A_BJI'; INDEX_TYPE JOI --------------------------- --- BITMAP YES SQL> select index_name,index_type,join_index from user_indexes where table_name='TEST1_A'; INDEX_NAME INDEX_TYPE JOI ------------------------------ --------------------------- --- TEST1_A_BJI BITMAP YES SQL> select index_name,index_type,join_index from user_indexes where table_name='TEST2_A'; INDEX_NAME INDEX_TYPE JOI ------------------------------ --------------------------- --- TEST2_A_PK NORMAL NO SQL> select index_name,index_type,join_index from user_indexes where table_name='TEST3_A'; INDEX_NAME INDEX_TYPE JOI ------------------------------ --------------------------- --- TEST3_A_U NORMAL NO SQL> SQL> set autotrace on explain SQL> select 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 1 0 SORT (AGGREGATE) 2 1 CONCATENATION 3 2 NESTED LOOPS 4 3 NESTED LOOPS 5 4 INDEX (UNIQUE SCAN) OF 'TEST3_A_U' (UNIQUE) 6 4 TABLE ACCESS (FULL) OF 'TEST1_A' 7 3 TABLE ACCESS (BY INDEX ROWID) OF 'TEST2_A' 8 7 INDEX (UNIQUE SCAN) OF 'TEST2_A_PK' (UNIQUE) 9 2 NESTED LOOPS 10 9 NESTED LOOPS 11 10 INDEX (UNIQUE SCAN) OF 'TEST3_A_U' (UNIQUE) 12 10 TABLE ACCESS (FULL) OF 'TEST1_A' 13 9 TABLE ACCESS (BY INDEX ROWID) OF 'TEST2_A' 14 13 INDEX (UNIQUE SCAN) OF 'TEST2_A_PK' (UNIQUE) 15 2 NESTED LOOPS 16 15 NESTED LOOPS 17 16 INDEX (UNIQUE SCAN) OF 'TEST3_A_U' (UNIQUE) 18 16 TABLE ACCESS (FULL) OF 'TEST1_A' 19 15 TABLE ACCESS (BY INDEX ROWID) OF 'TEST2_A' 20 19 INDEX (UNIQUE SCAN) OF 'TEST2_A_PK' (UNIQUE) SQL> SQL> -- Note that table alias must be used within the hint 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> -- 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=18 Card=1 Bytes=33) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=18 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=13 Ca rd=23 Bytes=92) 7 6 BITMAP CONVERSION (TO ROWIDS) 8 7 BITMAP INDEX (FULL SCAN) OF 'TEST1_A_BJI' SQL> SQL> set autotrace off 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