接到开发人员的求助,说一条update 语句很奇怪,拆开很快,然后联合在一起执行很慢,不知道为什么? 请帮忙分析?
具体语句如下:
UPDATE provice_maintain_data a
SET (townbusiness) =
(SELECT townbusiness
FROM (SELECT province, COUNT (0) AS townbusiness
FROM suborder
WHERE ordoprtime >=
TO_DATE ('20100901235959', 'YYYYMMDDHH24MISS')
AND ordoprtime <=
TO_DATE ('20110316235959', 'YYYYMMDDHH24MISS')
AND vendorid = 'CMG0151'
AND iscompleted = 0 group by province) b
WHERE b.province = a.province)
WHERE a.calcudate = '20110316';
开发人员说的拆开是,将子查询部分拿出来,创建一张临时表,然后再将临时表与update联合起来操作。创建子查询的临时表需要13s,而update瞬间完成;原语句几分钟都不能出来结果.
create table b
as
SELECT province, COUNT (0) AS townbusiness
FROM suborder
WHERE ordoprtime >=
TO_DATE ('20100901235959', 'YYYYMMDDHH24MISS')
AND ordoprtime <=
TO_DATE ('20110316235959', 'YYYYMMDDHH24MISS')
AND vendorid = 'CMG0151'
AND iscompleted = 0 group by province;
原语句修改为:
UPDATE provice_maintain_data a
SET (townbusiness) =
(SELECT townbusiness
FROM b
WHERE b.province = a.province)
WHERE a.calcudate = '20110316';
分析原因:
1)手工执行该SQL语句
2)查询该SQL正在做什么,发现该SQL正在等待资源,事件为cache buffers chains,有hotblock.
3)suborder表本来就是一张hot表,而这个update要频繁的访问该表,所以在该等待事件上发生等待,进而SQL语句很久不能出来结果
4)利用临时表,缓解了对suborder表的contention,所以速度很快
5)同时避免了对suborder表多次扫描
模拟:
update t02
set name=(select name from t01 where t02.id=t01.id)
3 where exists (select 1 from t01 where t02.id=t01.id);
execute plan:
--------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------
| 0 | UPDATE STATEMENT | | |
| 1 | UPDATE | T02 | |
|* 2 | HASH JOIN SEMI | | 105 |
| 3 | TABLE ACCESS FULL | T02 | 105 |
| 4 | INDEX FAST FULL SCAN | T01_IDX_ID | 50331 |
| 5 | TABLE ACCESS BY INDEX ROWID | T01 | 1 |
|* 6 | INDEX RANGE SCAN | T01_IDX_ID | 1 |
--------------------------------------------------------------
-------------------------------------------------------------
| Id | Operation | Name | Starts |
-------------------------------------------------------------
| 1 | UPDATE | T02 | 1 |
| 2 | TABLE ACCESS FULL | T02 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID | T01 | 99 |
|* 4 | INDEX RANGE SCAN | T01_IDX_ID | 99 |
-------------------------------------------------------------
总结下:
所以解决这种问题没有好的办法,最好就是利用临时表,这样减少contention,从而提高效率.