Oracle的update语句优化研究

Oracle 的update语句优化研究

一、         update 语句的语法与原理

1.     语法

单表： UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

如： update t_join_situation set join_state= '1' where year = '2011'

更新年度为“ 2011 ”的数据的 join_state 字段为“ 1 ”。如果更新的字段加了索引，更新时会重建索引，更新效率会慢。

   多表关联，并把一个表的字段值更新到另一个表中的字段去：

update 表 a set a. 字段 1 = (select b. 字段 1 from 表 b where a. 字段 2=b. 字段 2) where exists(select 1 from 表 b where a. 字段 2=b. 字段 2)  

oracle 的更新语句不通 MSSQL 那么简单易写，就算写出来了，但执行时可能会报

这是由于 set 哪里的子查询查出了多行数据值， oracle 规定一对一更新数据，所以提示出错。要解决这样必须保证查出来的值一一对应。

2.     原理

Update 语句的原理是先根据 where 条件查到数据后，如果 set 中有子查询，则执行子查询把值查出来赋给更新的字段，执行更新。

如： update 表 a set a. 字段 1 = (select b. 字段 1 from 表 b where a. 字段 2=b. 字段 2) where exists(select 1 from 表 b where a. 字段 2=b. 字段 2) 。查表 a 的所有数据，循环每条数据，验证该条数据是否符合 exists(select 1 from 表 b where a. 字段 2=b. 字段 2) 条件，如果是则执行 (select b. 字段 1 from 表 b where a. 字段 2=b. 字段 2) 查询，查到对应的值更新 a. 字段 1 中。关联表更新时一定要有 exists(select 1 from 表 b where a. 字段 2=b. 字段 2) 这样的条件，否则将表 a 的其他数据的字段 1 更新为 null 值。

二、         提高 oracle 更新效率的各种解决方案

1.     标准 update 语法

当你需要更新的表是单个或者被更新的字段不需要关联其他表带过来，则最后选择标准的 update 语句，速度最快，稳定性最好，并返回影响条数。如果 where 条件中的字段加上索引，那么更新效率就更高。但对需要关联表更新字段时， update 的效率就非常差。

2.     inline view 更新法

inline view 更新法就是更新一个临时建立的视图。如： update ( select a.join_state as join_state_a,b.join_state as join_state_b

from t_join_situation a, t_people_info b where a.people_number=b.people_number

and a.year= '2011' and a.city_number= 'M00000' and a.town_number= 'M51000' ) set join_state_a=join_state_b

括号里通过关联两表建立一个视图， set 中设置好更新的字段。这个解决方法比写法较直观且执行速度快。但表 B 的主键一定要在 where 条件中，并且是以“ = ”来关联被更新表，否则报一下错误：

 

3. merge 更新法

merge 是 oracle 特有的语句，语法如下：

MERGE  INTO  table_name alias1 
USING ( table | view | sub_query) alias2
ON  ( join  condition) 
WHEN  MATCHED  THEN  
     UPDATE  table_name 
     SET  col1  =  col_val1, 
        col2      =  col2_val 
WHEN  NOT  MATCHED  THEN  
     INSERT  (column_list)  VALUES  (column_values); 

它的原理是 在 alias2 中 Select 出来的数据， 每一条 都跟 alias1 进行 ON (join condition) 的比较，如果匹配，就进行更新的操作 (Update), 如果不匹配，就进行插入操作 (Insert) 。执行 merge 不会返回影响的行数。 Merge 语句的写法比较繁琐，并且最多只能两个表关联，复杂的语句用 merge 更新法将力不从心且效率差。

4. 快速游标更新法

语法如：

begin

for cr in ( 查询语句 ) loop –- 循环

   -- 更新语句（根据查询出来的结果集合）

end loop ; -- 结束循环

end ;

oracle 支持快速游标，不需要定义直接把游标写到 for 循环中，这样就方便了我们批量更新数据。再加上 oracle 的 rowid 物理字段（ oracle 默认给每个表都有 rowid 这个字段，并且是唯一索引），可以快速定位到要更新的记录上。

例子如下：

begin

for cr in ( select a.rowid,b.join_state from t_join_situation a,t_people_info b

where a.people_number=b.people_number

and a.year= '2011' and a.city_number= 'M00000' and a.town_number= 'M51000' ) loop

update t_join_situation set join_state=cr.join_state where

rowid = cr.rowid;

end loop ;

end ;

使用快速游标的好处很多，可以支持复杂的查询语句，更新准确，无论数据多大更新效率仍然高，但执行后不返回影响行数。

三、 结论

方案	建议
标准 update 语法	单表更新或较简单的语句采用使用此方案更优。
inline view 更新法	两表关联且被更新表通过关联表主键关联的，采用此方案更优。
merge 更新法	两表关联且被更新表不是通过关联表主键关联的，采用此方案更优。
快速游标更新法	多表关联且逻辑复杂的，采用此方案更优。

 

 

实时测试的速度：

--48466条数据

--1.297

update ( select a.join_state as join_state_a,b.join_state as join_state_b

from t_join_situation a, t_people_in fo b where a.people_number=b.people_number

and a.year= '2011' and a.city_number= 'M00000' and a.town_number= 'M51000'

) set join_state_a=join_state_b

 

--7.156

update  t_join_situation a set a.join_state=( select b.join_state from t_people_info b

where a.people_number=b.people_number

and a.year= '2011' and a.city_number= 'M00000' and a.town_number= 'M51000' )

where exists ( select 1 from t_people_info b

where a.people_number=b.people_number

and a.year= '2011' and a.city_number= 'M00000' and a.town_number= 'M51000' )

 

--3.281

begin

for cr in ( select a.rowid,b.join_state from t_join_situation a,t_people_info b

where a.people_number=b.people_number

and a.year= '2011' and a.city_number= 'M00000' and a.town_number= 'M51000' ) loop

update t_join_situation set join_state=cr.join_state where

rowid = cr.rowid;

end loop ;

end ;

 

--1.641

merge into t_join_situation a

using  t_people_info b

on (a.people_number=b.people_number

and  a.year= '2011' and a.city_number= 'M00000' and a.town_number= 'M51000' )

when matched then update set a.join_state=b.join_state