SQL> 
SQL> -- How is BJI maintained?
SQL> 
SQL> drop table test1_a;

Table dropped.

SQL> drop table test2_a;

Table dropped.

SQL> drop table test3_a;

Table dropped.

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(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> 
SQL> set autotrace on explain
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=4 Card=1 Bytes=16)            
   1    0   SORT (AGGREGATE)                                                    
   2    1     INLIST ITERATOR                                                   
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TEST1_A' (Cost=4 Car          
          d=9 Bytes=149)                                                        
                                                                                
   4    3         BITMAP CONVERSION (TO ROWIDS)                                 
   5    4           BITMAP INDEX (SINGLE VALUE) OF 'TEST1_A_BJI'                



SQL> 
SQL> set autotrace off
SQL> analyze table test1_a compute statistics;

Table analyzed.

SQL> select num_rows,DISTINCT_KEYS from user_indexes where index_name='TEST1_A_BJI';

  NUM_ROWS DISTINCT_KEYS                                                        
---------- -------------                                                        
         3             3                                                        

SQL> 
SQL> delete from test3_a where c2=3;

1 row deleted.

SQL> 
SQL> -- Note that use of BJI gives correct result when data in the dimension table is changed.
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)                                                                      
----------                                                                      
       810                                                                      

SQL> 
SQL> analyze table test1_a compute statistics;

Table analyzed.

SQL> -- Not that index built on test1_a is being updated when data in the dimension table is changed.
SQL> select num_rows,DISTINCT_KEYS from user_indexes where index_name='TEST1_A_BJI';

  NUM_ROWS DISTINCT_KEYS                                                        
---------- -------------                                                        
         2             2                                                        

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