oracle 位图索引

创建数据：

CREATE TABLE TB_BITMAP
(
    BITMAP_ID INTEGER NOT NULL,
    SEX VARCHAR2(1) NOT NULL
);

BEGIN
  FOR I IN 1..5000 LOOP
    INSERT INTO TB_BITMAP (BITMAP_ID, SEX) VALUES (I, 'F');
  END LOOP;
  FOR I IN 5001..10000 LOOP
    INSERT INTO TB_BITMAP (BITMAP_ID, SEX) VALUES (I, 'M');
  END LOOP;
  COMMIT;
END;

对于下面的查询：

select sex from tb_bitmap where sex='F';

如果创建B树索引：

SQL> create index idx_bitmap_sex on tb_bitmap(sex);

索引已创建。

SQL> set autotrace traceonly
SQL> select sex from tb_bitmap where sex='F';

已选择5000行。


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

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  5000 | 10000 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TB_BITMAP |  5000 | 10000 |     6   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("SEX"='F')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
         61  recursive calls
          0  db block gets
        401  consistent gets
          5  physical reads
          0  redo size
      67719  bytes sent via SQL*Net to client
       4048  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5000  rows processed

SQL>

换成位图索引：

SQL> drop index idx_bitmap_sex;

索引已删除。

SQL> create bitmap index bmpidx_bitmap_sex on tb_bitmap(sex);

索引已创建。

SQL> select sex from tb_bitmap where sex='F';

已选择5000行。


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

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |  5000 | 10000 |     1   (0)| 00:00:01 |
|   1 |  BITMAP CONVERSION TO ROWIDS |                   |  5000 | 10000 |     1   (0)| 00:00:01 |
|*  2 |   BITMAP INDEX FAST FULL SCAN| BMPIDX_BITMAP_SEX |       |       |            |          |
--------------------------------------------------------------------------------------------------

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

   2 - filter("SEX"='F')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
         61  recursive calls
          0  db block gets
         43  consistent gets
          0  physical reads
          0  redo size
      67719  bytes sent via SQL*Net to client
       4048  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5000  rows processed

SQL>

或者：

SQL> select * from tb_bitmap where sex='F';

已选择5000行。


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

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |  5000 | 75000 |     6  (17)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TB_BITMAP         |  5000 | 75000 |     6  (17)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | BMPIDX_BITMAP_SEX |       |       |            |          |
--------------------------------------------------------------------------------------------------

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

   3 - access("SEX"='F')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        370  consistent gets
          0  physical reads
          0  redo size
      98633  bytes sent via SQL*Net to client
       4048  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5000  rows processed

SQL>

此时如果还是B树索引：

SQL> drop index bmpidx_bitmap_sex;

索引已删除。

SQL> create index idx_bitmap_sex on tb_bitmap(sex);

索引已创建。

SQL> select * from tb_bitmap where sex='F';

已选择5000行。


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

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  5000 | 75000 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TB_BITMAP |  5000 | 75000 |     6   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("SEX"='F')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
         61  recursive calls
          0  db block gets
        401  consistent gets
          5  physical reads
          0  redo size
      87629  bytes sent via SQL*Net to client
       4048  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5000  rows processed

SQL>

可见，对于distinct值比较少而且分布比较均匀的列，用位图索引比B树索引好。