Bitmap Minus Operation

位图索引的一大优势便是可以对索引使用逻辑操作，如 and or等。Bitmap Minus Operation 是位图索引逻辑操作的一种，在类似 where c1 = :v1 and not c2 = :v2 的情况下，有可能会发生Bitmap Minus Operation。

SQL> create table t1(c1 number,c2 number,c3 varchar2(20));

表已创建。

SQL> create bitmap index i1 on t1(C1);

索引已创建。

SQL> create bitmap index i2 on t1(c2);

索引已创建。

SQL> insert into t1 select rownum,rownum,rownum from dual connect by level < 10000;

已创建9999行。
SQL> insert into t1 select * from t1;

已创建9999行。
SQL> commit;

通过hint强制优化器使用Bitmap Minux Operation

SQL> l
  1* select /*+index_combine(t1 i1 i2 ) */ * from t1 where c1=1 and not c2 =2
SQL> /

        C1         C2 C3
---------- ---------- --------------------
         1          1 1
         1          1 1


执行计划
----------------------------------------------------------
Plan hash value: 3546974129

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     2 |    76 |    14   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T1   |     2 |    76 |    14   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS |      |       |       |            |          |
|   3 |    BITMAP MINUS               |      |       |       |            |          |
|   4 |     BITMAP MINUS              |      |       |       |            |          |
|*  5 |      BITMAP INDEX SINGLE VALUE| I1   |       |       |            |          |
|*  6 |      BITMAP INDEX SINGLE VALUE| I2   |       |       |            |          |
|*  7 |     BITMAP INDEX SINGLE VALUE | I2   |       |       |            |          |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("C1"=1)
  -- 6 - access("C2" IS NULL)
   7 - access("C2"=2)

通过这里我们看到，总共执行了两次Bitmap Minus。这是因为除了排除c2=2的行，oracle还排除了C2 IS NULL的行（除非c2具有非空约束）。

SQL> alter table t1 modify c2 not null;

表已更改。

SQL> select /*+index_combine(t1 i1 i2 ) */ * from t1 where c1=1 and not c2 =2;

        C1         C2 C3
---------- ---------- --------------------
         1          1 1
         1          1 1


执行计划
----------------------------------------------------------
Plan hash value: 3468972141

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     2 |    76 |    13   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1   |     2 |    76 |    13   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |       |       |            |          |
|   3 |    BITMAP MINUS              |      |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE| I1   |       |       |            |          |
|*  5 |     BITMAP INDEX SINGLE VALUE| I2   |       |       |            |          |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("C1"=1)
   5 - access("C2"=2)