bitmap index的优化案例

某系统存储过程执行不过去，通过查询长时间ACTIVE的SESSION定位到如下语句：

UPDATE AAA_BBBBBBBBB_ALM T SET KEY_BS='1111111111'
 WHERE T.PARTITION_KEY = 'S00000014097'
   AND T.KEY_BS_BK = 'ASSET_NS-INB-DMD'
   and SUBSTR(ATTRIBUTE_1,1,6) in ('101101', '101102', '101103','101104');  
Plan hash value: 2937027114

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |                      |     1 |    74 |     3   (0)| 00:00:01 |
|   1 |  UPDATE                       | AAA_BBBBBBBBB_ALM    |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID | AAA_BBBBBBBBB_ALM    |     1 |    74 |     3   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS|                      |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE | AAA_BBBBBBBBB_ALM_B1 |       |       |            |          |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("T"."KEY_BS_BK"='ASSET_NS-INB-DMD' AND (SUBSTR("ATTRIBUTE_1",1,6)='101101' OR 
              SUBSTR("ATTRIBUTE_1",1,6)='101102' OR SUBSTR("ATTRIBUTE_1",1,6)='101103' OR 
              SUBSTR("ATTRIBUTE_1",1,6)='101104'))
   4 - access("T"."PARTITION_KEY"='S00000014097')

通过执行计划可以得出，BITMAP INDEX SINGLE VALUE 先通过键值筛选，然后BITMAP CONVERSION TO ROWIDS转换为rowid，再通过ROWID去回表TABLE ACCESS BY INDEX ROWID，这个过程和普通的索引扫描类似，只是多了一个BITMAP CONVERSION TO ROWIDS的操作！查一下PARTITION_KEY字段的选择性

SELECT PARTITION_KEY,COUNT(*) FROM AAA_BBBBBBBBB_ALM GROUP BY PARTITION_KEY;
1    S00000014097    796083

呵呵了！整个表distinct值就一个，BITMAP INDEX SINGLE VALUE筛选根本没起作用，索引会取出所有行的ROWID，然后回表的时候回了读取了表所有的数据块，而且是单块读！

下面的图片显示了表上面所有的索引信息，而且都是位图索引。

.

这里我们详细说一下位图索引的原理和使用场景：

上图中 一个表上面建立了10个bitmap索引，如果是b-tree索引，一般不会允许建如此多，维护和存储花费的代价将很大。

而位图索引，则没有问题，这和他的存储方式有关。比如一个表有1000w 行，性别列 男 999w 女1w

如果是b-tree索引，存储的方式是这样的

男 ，ROWID

.....

男，ROWID   ->第999w行

女，ROWID

.....

女，ROWID ->第1w行

索引的叶子节点里面存储的数据行数是1000w行。

如果是bitmap索引，存储的方式是这样的，把 男 女 分别用1，0表示

男 ，ROWID 1条  1010001......
女 ，ROWID 1条  0101110......    男人 女人 存2条其他地方 用1 0 代替

可见DISTINCT值越多，行数就越多！这也是为什么位图索引最好建在基数低的列

如果一个表上面建多个位图索引比如 婚姻状态列（已婚、未婚、离异），级别（OCA、OCP、OCM） ........这样一个表上面有多个位图索引，存储方式如下：

对于性别这个列，位图索引形成两个向量，男向量为10100...，向量的每一位表示该行是否是男，如果是则位1，否为0，同理，女向量位01011。

RowId	1	2	3	4	5	...
男	1	0	1	0	0
女	0	1	0	1	1

对于婚姻状况这一列，位图索引生成三个向量，已婚为11000...，未婚为00100...，离婚为00010...。

RowId	1	2	3	4	5	...
已婚	1	1	0	0	0
未婚	0	0	1	0	1
离婚	0	0	0	1	0

对于级别这一列，同样生成三个向量，OCA为11000...，OCP为00100...，OCM为00010...。

RowId	1	2	3	4	5	...
OCA	1	1	0	0	0
OCP	0	0	1	0	1
OCM	0	0	0	1	0

在这个案例中位图索引只使用了一个条件"T"."PARTITION_KEY"='S00000014097'，这个条件BITMAP INDEX SINGLE VALUE筛选的结果就是 向量11111111111....11111再通过对应的位置，把向量转成ROWID(BITMAP CONVERSION TO ROWIDS)。所以切记 不要把位图索引当做B树索引来使用。

位图索引的使用场景：通常是SQL语句必须有多个含有位图索引字段的过滤条件，CBO去解析时，先通过这个条件再位图内部做逻辑运算（与 或 非）。比如：select * from table where 性别='男' and 婚姻状况='未婚'，首先取出男向量10100...，然后取出未婚向量00100...，将两个向量做and操作，这时生成新向量00100...，可以发现第三位为1，表示该表的第三行数据就是我们需要查询的结果。

RowId	1	2	3	4	5	...
男	1	0	1	0	0
AND
未婚	0	0	1	0	1
结果	0	0	1	0	0

我们得到的向量00100，表示第三个ROWID是我们需要，通过位图转换出ROWID的值即可！如果条件里面再加上AND 级别='OCM',可以进一步缩小行数，缩小转换出的ROWID个数。

总结一下：位图索引的适用场景是表上有多个选择性不好的列，而对于这个表的查询语句条件里多次使用这些列，就可以通过在这些列上面建位图索引的方式来提高查询效率(注：OLTP系统严禁使用位图索引)

根据上面的分析，需要优化的SQL完全符合使用位图索引的条件。条件列里面有4个逻辑或3个逻辑与，只是SQL没有按照位图的原理去走逻辑或和逻辑与，而是把位图索引当成普通的B树索引来用。这时候我们需要使用HINT强制让位图索引进行位运算

The INDEX_COMBINE hint instructs the optimizer to use a bitmap access path for the table. If indexspec is omitted from the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of indexes has the best cost estimate for the table. If you specify indexspec, then the optimizer tries to use some Boolean combination of the specified indexes. 

使用HINT后的执行计划如下：
UPDATE /*+ index_combine(t AAA_BBBBBBBBB_ALM_B1 AAA_BBBBBBBBB_ALM_B2 AAA_BBBBBBBBB_ALM_B7) */AAA_BBBBBBBBB_ALM T SET KEY_BS='1111111111'
 WHERE T.PARTITION_KEY = 'S00000014097'
   AND T.KEY_BS_BK = 'ASSET_NS-INB-DMD'
   and SUBSTR(ATTRIBUTE_1,1,6) in ('101101', '101102', '101103','101104');              

Plan hash value: 82554262
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT               |                      |     1 |    74 |    18   (0)| 00:00:01 |
|   1 |  UPDATE                        | AAA_BBBBBBBBB_ALM    |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID  | AAA_BBBBBBBBB_ALM    |     1 |    74 |    18   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS |                      |       |       |            |          |
|   4 |     BITMAP AND                 |                      |       |       |            |          |
|*  5 |      BITMAP INDEX SINGLE VALUE | AAA_BBBBBBBBB_ALM_B1 |       |       |            |          |
|*  6 |      BITMAP INDEX SINGLE VALUE | AAA_BBBBBBBBB_ALM_B2 |       |       |            |          |
|   7 |      BITMAP OR                 |                      |       |       |            |          |
|*  8 |       BITMAP INDEX SINGLE VALUE| AAA_BBBBBBBBB_ALM_B7 |       |       |            |          |
|*  9 |       BITMAP INDEX SINGLE VALUE| AAA_BBBBBBBBB_ALM_B7 |       |       |            |          |
|* 10 |       BITMAP INDEX SINGLE VALUE| AAA_BBBBBBBBB_ALM_B7 |       |       |            |          |
|* 11 |       BITMAP INDEX SINGLE VALUE| AAA_BBBBBBBBB_ALM_B7 |       |       |            |          |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("T"."PARTITION_KEY"='S00000014097')
   6 - access("T"."KEY_BS_BK"='ASSET_NS-INB-DMD')
   8 - access(SUBSTR("ATTRIBUTE_1",1,6)='101101')
   9 - access(SUBSTR("ATTRIBUTE_1",1,6)='101102')
  10 - access(SUBSTR("ATTRIBUTE_1",1,6)='101103')
  11 - access(SUBSTR("ATTRIBUTE_1",1,6)='101104')