BITMAP CONVERSION FROM ROWIDS索引合并带来的问题：
下面两条SQL语句：
SELECT PHO.PHONE_NUMBER_ID , PHO.AN_TYPE_CD , PHO.PN_LEVEL_ID , PHO.PHONE_NUMBER , PHO.POOL_ID, pho.RSC_STATUS_CD
FROM PHONE_NUMBER PHO
WHERE PHO.AREA_ID IN
(SELECT ARE.AREA_ID
FROM AREA ARE
START WITH ARE.AREA_ID = 108 --本地网ID
CONNECT BY PRIOR ARE.AREA_ID = ARE.PARENT_AREA)
AND PHO.AN_TYPE_CD = 103 --号码类型ID
AND REVERSE(PHO.PHONE_NUMBER) like REVERSE('5624') || '%' --有输入值则带此条件
AND PHO.PN_LEVEL_ID = 59;
执行时间：0.359秒
SELECT PHO.PHONE_NUMBER_ID , PHO.AN_TYPE_CD , PHO.PN_LEVEL_ID , PHO.PHONE_NUMBER , PHO.POOL_ID, pho.RSC_STATUS_CD
FROM PHONE_NUMBER PHO
WHERE PHO.AREA_ID IN
(SELECT ARE.AREA_ID
FROM AREA ARE
START WITH ARE.AREA_ID = 108 --本地网ID
CONNECT BY PRIOR ARE.AREA_ID = ARE.PARENT_AREA)
AND PHO.AN_TYPE_CD = 103 --号码类型ID
AND REVERSE(PHO.PHONE_NUMBER) like REVERSE('5624') || '%' --有输入值则带此条件
AND PHO.PN_LEVEL_ID = 59
AND PHO.RSC_STATUS_CD = 1
执行时间：1.328秒
为什么加上个RSC_STATUS_CD = 1条件，会变慢？应该来说同一张表不会出现这种问题
查看执行计划：
rm@RM>SELECT PHO.PHONE_NUMBER_ID , PHO.AN_TYPE_CD , PHO.PN_LEVEL_ID , PHO.PHONE_NUMBER , PHO.POOL_ID, pho.RSC_STATUS_CD
2 FROM PHONE_NUMBER PHO
3 WHERE PHO.AREA_ID IN
4 (SELECT ARE.AREA_ID
5 FROM AREA ARE
6 START WITH ARE.AREA_ID = 108 --本地网ID
7 CONNECT BY PRIOR ARE.AREA_ID = ARE.PARENT_AREA)
8 AND PHO.AN_TYPE_CD = 103 --号码类型ID
9 AND REVERSE(PHO.PHONE_NUMBER) like REVERSE('5624') || '%' --有输入值则带此条件
10 AND PHO.PN_LEVEL_ID = 59;
执行计划
----------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5430 | 243K| | 13009 (1)|
| 1 | HASH JOIN RIGHT SEMI | | 5430 | 243K| | 13009 (1)|
| 2 | VIEW | VW_NSO_1 | 178 | 2314 | | 3 (0)|
| 3 | CONNECT BY WITH FILTERING | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | AREA | | | | |
| 5 | INDEX UNIQUE SCAN | PK_AREA | 1 | 4 | | 0 (0)|
| 6 | HASH JOIN | | | | | |
| 7 | CONNECT BY PUMP | | | | | |
| 8 | TABLE ACCESS FULL | AREA | 178 | 1424 | | 3 (0)|
| 9 | TABLE ACCESS FULL | AREA | 178 | 1424 | | 3 (0)|
| 10 | TABLE ACCESS BY INDEX ROWID | PHONE_NUMBER | 5430 | 174K| | 13006 (1)|
| 11 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 12 | BITMAP AND | | | | | |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 14 | INDEX RANGE SCAN | IDX_PHN_NMB_LEVEL | 705K| | | 1315 (1)|
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 16 | SORT ORDER BY | | | | 26M| |
| 17 | INDEX RANGE SCAN | IDX_REVE_PHONUM | 705K| | | 434 (1)|
----------------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1461 consistent gets
0 physical reads
0 redo size
710 bytes sent via SQL*Net to client
392 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
rm@RM>
rm@RM>SELECT PHO.PHONE_NUMBER_ID , PHO.AN_TYPE_CD , PHO.PN_LEVEL_ID , PHO.PHONE_NUMBER , PHO.POOL_ID, pho.RSC_STATUS
2 FROM PHONE_NUMBER PHO
3 WHERE PHO.AREA_ID IN
4 (SELECT ARE.AREA_ID
5 FROM AREA ARE
6 START WITH ARE.AREA_ID = 108 --本地网ID
7 CONNECT BY PRIOR ARE.AREA_ID = ARE.PARENT_AREA)
8 AND PHO.AN_TYPE_CD = 103 --号码类型ID
9 AND REVERSE(PHO.PHONE_NUMBER) like REVERSE('5624') || '%' --有输入值则带此条件
10 AND PHO.PN_LEVEL_ID = 59
11 AND PHO.RSC_STATUS_CD = 1;
执行计划
----------------------------------------------------------
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2299 | 103K| | 23358 (1)|
| 1 | HASH JOIN RIGHT SEMI | | 2299 | 103K| | 23358 (1)|
| 2 | VIEW | VW_NSO_1 | 178 | 2314 | | 3 (0)|
| 3 | CONNECT BY WITH FILTERING | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | AREA | | | | |
| 5 | INDEX UNIQUE SCAN | PK_AREA | 1 | 4 | | 0 (0)|
| 6 | HASH JOIN | | | | | |
| 7 | CONNECT BY PUMP | | | | | |
| 8 | TABLE ACCESS FULL | AREA | 178 | 1424 | | 3 (0)|
| 9 | TABLE ACCESS FULL | AREA | 178 | 1424 | | 3 (0)|
| 10 | TABLE ACCESS BY INDEX ROWID | PHONE_NUMBER | 2299 | 75867 | | 23354 (1)|
| 11 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 12 | BITMAP AND | | | | | |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 14 | INDEX RANGE SCAN | IDX_PHN_NMB_LEVEL | 705K| | | 1315 (1)|
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 16 | SORT ORDER BY | | | | 26M| |
| 17 | INDEX RANGE SCAN | IDX_REVE_PHONUM | 705K| | | 434 (1)|
| 18 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 19 | INDEX RANGE SCAN | IDX_PHN_NMB_STATUS | 705K| | | 14437 (1)|
-----------------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
15830 consistent gets
0 physical reads
0 redo size
710 bytes sent via SQL*Net to client
392 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
rm@RM>
比较了两个执行计划：问题主要出在：
| 18 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 19 | INDEX RANGE SCAN | IDX_PHN_NMB_STATUS | 705K| | | 14437 (1)|
IDX_PHN_NMB_STATUS这个索引建得不好，不晓得当时建它的理由是什么
create index IDX_PHN_NMB_STATUS on PHONE_NUMBER (RSC_STATUS_CD)
tablespace TBS_YNRM_TS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
解决办法：
1：删除该索引，但不晓得会不会引起其它的性能问题
2：指定走rowid路径，不走索引合并
select /*+ROWID(xx) */ *
from (SELECT ROWNUM r,
PHO.PHONE_NUMBER_ID,
PHO.AN_TYPE_CD,
PHO.PN_LEVEL_ID,
PHO.PHONE_NUMBER,
PHO.POOL_ID,
PHO.RSC_STATUS_CD
FROM PHONE_NUMBER PHO
WHERE PHO.AREA_ID IN
(SELECT ARE.AREA_ID
FROM AREA ARE
START WITH ARE.AREA_ID = 108 --本地网ID
CONNECT BY PRIOR ARE.AREA_ID = ARE.PARENT_AREA)
AND PHO.AN_TYPE_CD = 103 --号码类型ID
AND REVERSE(PHO.PHONE_NUMBER) like REVERSE('5624') || '%' --有输入值则带此条件
AND PHO.PN_LEVEL_ID = 59) xx
where xx.RSC_STATUS_CD = 1;
注意：一定要在xx视图中加上ROWNUM
阅读(2710) | 评论(0) | 转发(0) |
