一条update语句的优化

接到开发人员的求助，说一条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,从而提高效率.