bitmap and 还是 and equal,都是一种将两个索引选择出来的rowid合并的算法,可以理解索引扫描结果合并;
假设这里的条件使用这两个索引选择性也非常之高,那么这里使用bitmap是不会影响效率的。
我们的sql 5分钟没有跑完,查看计划,走了BITMAP CONVERSION TO ROWIDS。
- SQL> explain plan for
- 2 select a.username ,
- 3 a.membertype ,
- 4 a.providername ,
- 5 a.tradename ,
- 6 a.actor ,
- 7 a.telephone ,
- 8 a.mobilephone ,
- 9 a.email ,
- 10 b.title ,
- 11 b.starttime ,
- 12 b.adminname
- 13 from Seller000 a, Chatroom000 b
- 14 where a.roomid = b.roomid
- 15 and b.isshow = '1'
- 16 and b.roomtype in ('0', '1')
- 17 and a.signupdate > to_date('2012-08-01', 'yyyy-mm-dd hh24:mi:ss')
- 18 and a.signupdate < to_date('2012-09-01', 'yyyy-mm-dd hh24:mi:ss')
- 19 order by b.roomid;
- 已解释。
- SQL> select * from table(dbms_xplan.display);
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------------------------------------
- Plan hash value: 273647176
- ----------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 11 | 1782 | 28 (11)| 00:00:01 |
- |* 1 | FILTER | | | | | |
- | 2 | TABLE ACCESS BY INDEX ROWID | SELLER | 1 | 112 | 28 (11)| 00:00:01 |
- | 3 | NESTED LOOPS | | 11 | 1782 | 28 (11)| 00:00:01 |
- |* 4 | TABLE ACCESS BY INDEX ROWID | CHATROOM | 10975 | 535K| 3 (0)| 00:00:01 |
- | 5 | INDEX FULL SCAN | PK_CHATROOM | 9 | | 1 (0)| 00:00:01 |
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------------------------------------
- | 6 | BITMAP CONVERSION TO ROWIDS | | | | | |
- | 7 | BITMAP AND | | | | | |
- | 8 | BITMAP CONVERSION FROM ROWIDS| | | | | |
- |* 9 | INDEX RANGE SCAN | IDX_SELLER_ROOMID | 141 | | 1 (0)| 00:00:01 |
- | 10 | BITMAP CONVERSION FROM ROWIDS| | | | | |
- | 11 | SORT ORDER BY | | | | | |
- |* 12 | INDEX RANGE SCAN | I_SELLER_SIGNUPDATE | 141 | | 2 (0)| 00:00:01 |
- ----------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------------------------------------
- 1 - filter(TO_DATE('2012-08-01','yyyy-mm-dd hh24:mi:ss')<TO_DATE('2012-09-01','yyyy-mm-dd
- hh24:mi:ss'))
- 4 - filter("B"."ISSHOW"='1' AND ("B"."ROOMTYPE"='0' OR "B"."ROOMTYPE"='1'))
- 9 - access("A"."ROOMID"="B"."ROOMID")
- 12 - access("A"."SIGNUPDATE">TO_DATE('2012-08-01','yyyy-mm-dd hh24:mi:ss') AND
- "A"."SIGNUPDATE"<TO_DATE('2012-09-01','yyyy-mm-dd hh24:mi:ss'))
- 已选择29行。
- SQL> explain plan for
- 2 select /*+ opt_param('_b_tree_bitmap_plans', 'false') */ a.username ,
- 3 a.membertype ,
- 4 a.providername ,
- 5 a.tradename ,
- 6 a.actor ,
- 7 a.telephone ,
- 8 a.mobilephone ,
- 9 a.email ,
- 10 b.title ,
- 11 b.starttime ,
- 12 b.adminname
- 13 from Seller000 a, Chatroom000 b
- 14 where a.roomid = b.roomid
- 15 and b.isshow = '1'
- 16 and b.roomtype in ('0', '1')
- 17 and a.signupdate > to_date('2012-08-01', 'yyyy-mm-dd hh24:mi:ss')
- 18 and a.signupdate < to_date('2012-09-01', 'yyyy-mm-dd hh24:mi:ss')
- 19 order by b.roomid;
- 已解释。
- SQL> select * from table(dbms_xplan.display);
- PLAN_TABLE_OUTPUT
- ---------------------------------------------------------------------------------------------------------------
- Plan hash value: 4238594111
- ----------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 11 | 1782 | 240 (0)| 00:00:03 |
- |* 1 | FILTER | | | | | |
- |* 2 | TABLE ACCESS BY INDEX ROWID | SELLER | 1 | 112 | 34 (0)| 00:00:01 |
- | 3 | NESTED LOOPS | | 11 | 1782 | 240 (0)| 00:00:03 |
- |* 4 | TABLE ACCESS BY INDEX ROWID| CHATROOM | 10975 | 535K| 3 (0)| 00:00:01 |
- | 5 | INDEX FULL SCAN | PK_CHATROOM | 9 | | 1 (0)| 00:00:01 |
- PLAN_TABLE_OUTPUT
- ---------------------------------------------------------------------------------------------------------------
- |* 6 | INDEX RANGE SCAN | IDX_SELLER_ROOMID | 141 | | 1 (0)| 00:00:01 |
- ----------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter(TO_DATE('2012-08-01','yyyy-mm-dd
- hh24:mi:ss')<TO_DATE('2012-09-01','yyyy-mm-dd hh24:mi:ss'))
- 2 - filter("A"."SIGNUPDATE">TO_DATE('2012-08-01','yyyy-mm-dd hh24:mi:ss') AND
- "A"."SIGNUPDATE"<TO_DATE('2012-09-01','yyyy-mm-dd hh24:mi:ss'))
- 4 - filter("B"."ISSHOW"='1' AND ("B"."ROOMTYPE"='0' OR "B"."ROOMTYPE"='1'))
- PLAN_TABLE_OUTPUT
- ---------------------------------------------------------------------------------------------------------------
- 6 - access("A"."ROOMID"="B"."ROOMID")
- 已选择23行。
出现这样的情况,是因为表中存在不适当的索引,这些索引列的唯一度不高,oracle就有可能选择两个这样的索引转为bitmap来执行,
根据这两个索引的值再确认共同有的ROWID,最后再通过ROWID回表提取符合条件的数据。
可以使用/*+ opt_param('_b_tree_bitmap_plans', 'false') */ hint在sql级消除bitmap。
最终解决方法,删除唯一度低的index,建立组合index。
删除索引IDX_SELLER_ROOMID,如果I_SELLER_SIGNUPDATE索引确认无用,也可以删除,IND_ROOMID_SIGNUPDATE是我们建立的组合索引,这样消除了BITMAP CONVERSION TO ROWIDS,30秒出结果。以下是新的执行计划:
- SQL> explain plan for
- 2 select a.username ,
- 3 a.membertype ,
- 4 a.providername ,
- 5 a.tradename ,
- 6 a.actor ,
- 7 a.telephone ,
- 8 a.mobilephone ,
- 9 a.email ,
- 10 b.title ,
- 11 b.starttime ,
- 12 b.adminname
- 13 from Seller000 a, Chatroom000 b
- 14 where a.roomid = b.roomid
- 15 and b.isshow = '1'
- 16 and b.roomtype in ('0', '1')
- 17 and a.signupdate > to_date('2012-08-01', 'yyyy-mm-dd hh24:mi:ss')
- 18 and a.signupdate < to_date('2012-09-01', 'yyyy-mm-dd hh24:mi:ss')
- 19 order by b.roomid;
- 已解释。
- SQL> select * from table(dbms_xplan.display);
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 2603971898
- --------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 11 | 1672 | 9 (0)| 00:00:01 |
- |* 1 | FILTER | | | | | |
- | 2 | TABLE ACCESS BY INDEX ROWID | SELLER | 1 | 102 | 1 (0)| 00:00:01 |
- | 3 | NESTED LOOPS | | 11 | 1672 | 9 (0)| 00:00:01 |
- |* 4 | TABLE ACCESS BY INDEX ROWID| CHATROOM | 9978 | 487K| 3 (0)| 00:00:01 |
- | 5 | INDEX FULL SCAN | PK_CHATROOM | 9 | | 1 (0)| 00:00:01 |
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------
- |* 6 | INDEX RANGE SCAN | IND_ROOMID_SIGNUPDATE | 1 | | 1 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter(TO_DATE('2012-08-01','yyyy-mm-dd hh24:mi:ss')<TO_DATE('2012-09-01','yyyy-mm-dd
- hh24:mi:ss'))
- 4 - filter("B"."ISSHOW"='1' AND ("B"."ROOMTYPE"='0' OR "B"."ROOMTYPE"='1'))
- 6 - access("A"."ROOMID"="B"."ROOMID" AND "A"."SIGNUPDATE">TO_DATE('2012-08-01','yyyy-mm-dd
- hh24:mi:ss') AND "A"."SIGNUPDATE"<TO_DATE('2012-09-01','yyyy-mm-dd hh24:mi:ss'))
- 已选择22行。