Oracle的update语句优化研究批量更新

「爱情、让人受尽委屈。」 2022-04-11 08:23 761阅读 0赞

在实际项目中，往往存在业务逻辑处理时，先更新，然后查询的情况，而更新则是在子查询的基础上执行，如果用传统语法更新，效率特别慢，难以满足用户使用需求。整理本文档的目的就是在提升性能方面出发，为解决实际问题提供一种可行的解决方案。

文档结构，从基本的语法和原理出发，并结合实际的实例，提升在系统性能方面的认识和解决问题思路。

1、update语句的语法与原理

语法

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

更新年度为“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的更新语句不通MySQL那么简单易写，就算写出来了，但执行时可能会报错

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

原理

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

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

1.标准update语法

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

2. inline view内嵌视图更新法

inline view更新法就是更新一个临时建立的视图。如：update (select a.join\_stateas join\_state\_a,b.join\_stateas join\_state\_b

from t\_join\_situation a, t\_people\_info bwhere 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条件中，并且是以“=”来关联被更新表，否则报错。

总结：适合于两表关联主键更新，如：两张表都是用人员id作为关联条件的情况。并且不适用于多表。

测试情况：建立两张测试表。GKFQ\_REC和OA2\_FTASK。

测试目的：用OA2\_FTASK 表的ft\_lstate字段值完成GKFQ\_REC的批量blzt更新。

*-- Create table*

create table GKFQ\_REC

(

slid VARCHAR2(12) not null,

blzt VARCHAR2(50),

wjbt VARCHAR2(100)

)

tablespace OAUTH2DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

*-- Create/Recreate primary, unique and foreign key constraints*

alter table GKFQ\_REC

add constraint SLID primary key (SLID)

using index

tablespace OAUTH2DATA

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

*-- Create table*

create table OA2\_FTASK

(

fi\_inst VARCHAR2(12) not null,

fi\_state VARCHAR2(50),

ft\_lstate VARCHAR2(50)

)

tablespace OAUTH2DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

*-- Create/Recreate primary, unique and foreign key constraints*

alter table OA2\_FTASK

add constraint FI\_INST primary key (FI\_INST)

using index

tablespace OAUTH2DATA

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

内嵌视图更新执行：

update gkfq\_rec t1

set t1.blzt =

(select b.ft\_lstate from oa2\_ftask b where t1.slid = b.fi\_inst)

where exists (select 1 from oa2\_ftask t2 where t1.slid = t2.fi\_inst);

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 crin (查询语句)loop –-循环

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

end loop; --结束循环

end;

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

快速游标更新，建议采用过程或者自定义函数。优点在于：

1.  对于应用程序调用简化。基本不用大的改动。建议自定义函数，一条语句就能搞定。
2.  对于过程使用，适用于数据加工，应用程序调用或者job自定义触发。相当于定时任务。

自定义函数实例：

create or replace function testlzgf return varchar2 is

PRAGMA AUTONOMOUS\_TRANSACTION;--关键点，自动事物处理，否则无法执行更新

Result varchar2(200):='执行';--纯粹是满足函数的语法，没实际意义，就是个识别常量

begin

for cr in (select a.fi\_inst, a.ft\_lstate, b.blzt

from oa2\_ftask a, gkfq\_rec b

where a.fi\_inst = b.slid)

\--括号中是快速游标查询结果集

loop

update gkfq\_rec g set g.blzt = cr.ft\_lstate where g.slid = cr.fi\_inst;

commit;

end loop;

return Result ;

end testlzgf;

过程实例

create or replace procedure tests is

v\_name VARCHAR2(20);

begin

for cr in (select a.fi\_inst, a.ft\_lstate, b.blzt

from oa2\_ftask a, gkfq\_rec b

where a.fi\_inst = b.slid)

loop

update gkfq\_rec g set g.blzt = cr.ft\_lstate where g.slid = cr.fi\_inst;

commit;

end loop;

end tests;

Plsql语句块例子如下：

begin

for crin (select a.rowid,b.join\_statefrom 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\_situationset join\_state=cr.join\_statewhere

rowid = cr.rowid;

end loop;

end;

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

三、 结论

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#7f7f7f;"> 方案 </td> 
 <td style="background-color:#7f7f7f;"> 建议 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 标准update语法 </td> 
 <td style="vertical-align:top;"> 单表更新或较简单的语句采用使用此方案更优。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> inline view更新法 </td> 
 <td style="vertical-align:top;"> 两表关联且被更新表通过关联表主键关联的，采用此方案更优。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> merge更新法 </td> 
 <td style="vertical-align:top;"> 两表关联且被更新表不是通过关联表主键关联的，采用此方案更优。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 快速游标更新法 </td> 
 <td style="vertical-align:top;"> 多表关联且逻辑复杂的，采用此方案更优。 </td> 
 </tr> 
 </tbody> 
</table>

实时测试的速度：

*--48466**条数据*

*--1.297*

update (select a.join\_stateas join\_state\_a,b.join\_stateas join\_state\_b

from t\_join\_situation a, t\_people\_info bwhere 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 aset a.join\_state=(select b.join\_statefrom 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