SQL> SQL> drop table t1_a; Table dropped. SQL> create table t1_a (c1 number, c2 varchar2(2), c3 varchar2(4), c4 number); Table created. SQL> SQL> begin 2 for i in 1..100000 loop 3 insert into t1_a values(1,'xx','east',i); 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> SQL> insert /*+ append */ into t1_a select 2,'xx','west',c4+100 from t1_a; 100000 rows created. SQL> commit; Commit complete. SQL> SQL> insert /*+ append */ into t1_a select 3,'yy','east', c4+200 from t1_a; 200000 rows created. SQL> commit; Commit complete. SQL> SQL> insert /*+ append */ into t1_a select 1,'ww','west', c4+300 from t1_a; 400000 rows created. SQL> commit; Commit complete. SQL> SQL> create bitmap index t1_a_c1_bi on t1_a(c1) nologging; Index created. SQL> create bitmap index t1_a_c2_bi on t1_a(c2) nologging; Index created. SQL> create bitmap index t1_a_c3_bi on t1_a(c3) nologging; Index created. SQL> SQL> analyze table t1_a compute statistics; Table analyzed. SQL> SQL> set autotrace on explain SQL> SQL> -- Bitmap not used, bitmap to rowid conversion too expensive for large rows returned. SQL> select sum(c4) from t1_a 2 where c1=1 and c3='west' and c2='ww'; SUM(C4) ---------- 2.0180E+10 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=230 Card=1 Bytes=12) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'T1_A' (Cost=230 Card=44444 Bytes =533328) SQL> SQL> -- Simple counting is perfect for using bitmap index SQL> select count(1) from t1_a 2 where c1=1 and c3='west' and c2='ww'; COUNT(1) ---------- 400000 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=1 Bytes=8) 1 0 SORT (AGGREGATE) 2 1 BITMAP CONVERSION (COUNT) 3 2 BITMAP AND 4 3 BITMAP INDEX (SINGLE VALUE) OF 'T1_A_C1_BI' 5 3 BITMAP INDEX (SINGLE VALUE) OF 'T1_A_C2_BI' 6 3 BITMAP INDEX (SINGLE VALUE) OF 'T1_A_C3_BI' SQL> SQL> set autotrace off SQL> begin 2 for i in 1..100 loop 3 insert into t1_a values(2,'ww','east',i); 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> SQL> set autotrace on explain SQL> -- Why are bi not used? Only 100 rows are returned. SQL> select sum(c4) from t1_a 2 where c1=2 and c3='east' and c2='ww'; SUM(C4) ---------- 5050 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=230 Card=1 Bytes=12) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'T1_A' (Cost=230 Card=44444 Bytes =533328) SQL> SQL> select /*+ index_combine(t1_a T1_A_C1_BI T1_A_C2_BI T1_A_C3_BI) */ 2 sum(c4) from t1_a 3 where c1=2 and c3='east' and c2='ww'; SUM(C4) ---------- 5050 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=686 Card=1 Bytes=12) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1_A' (Cost=686 Card=4 4444 Bytes=533328) 3 2 BITMAP CONVERSION (TO ROWIDS) 4 3 BITMAP AND 5 4 BITMAP INDEX (SINGLE VALUE) OF 'T1_A_C1_BI' 6 4 BITMAP INDEX (SINGLE VALUE) OF 'T1_A_C2_BI' 7 4 BITMAP INDEX (SINGLE VALUE) OF 'T1_A_C3_BI' SQL> alter session set OPTIMIZER_INDEX_COST_ADJ=50; Session altered. SQL> select sum(c4) from t1_a 2 where (c1=2 or c1=1) and (c3='east' or c3='south') and (c2='ww' or c2='xx'); SUM(C4) ---------- 505550 SQL> set autotrace on explain SQL> -- Not low enough SQL> select sum(c4) from t1_a 2 where (c1=2 or c1=1) and (c3='east' or c3='south') and (c2='ww' or c2='xx'); SUM(C4) ---------- 505550 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=290 Card=1 Bytes=12) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'T1_A' (Cost=290 Card=227578 Byte s=2730936) SQL> alter session set OPTIMIZER_INDEX_COST_ADJ=1; Session altered. SQL> -- Finally SQL> select sum(c4) from t1_a 2 where (c1=2 or c1=1) and (c3='east' or c3='south') and (c2='ww' or c2='xx'); SUM(C4) ---------- 505550 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=12) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1_A' (Cost=19 Card=22 7578 Bytes=2730936) 3 2 BITMAP CONVERSION (TO ROWIDS) 4 3 BITMAP AND 5 4 BITMAP OR 6 5 BITMAP INDEX (SINGLE VALUE) OF 'T1_A_C3_BI' 7 5 BITMAP INDEX (SINGLE VALUE) OF 'T1_A_C3_BI' 8 4 BITMAP OR 9 8 BITMAP INDEX (SINGLE VALUE) OF 'T1_A_C1_BI' 10 8 BITMAP INDEX (SINGLE VALUE) OF 'T1_A_C1_BI' 11 4 BITMAP OR 12 11 BITMAP INDEX (SINGLE VALUE) OF 'T1_A_C2_BI' 13 11 BITMAP INDEX (SINGLE VALUE) OF 'T1_A_C2_BI' SQL> drop table t1_a; Table dropped. SQL> SQL> spool off