Oracle的update语句优化研究-白红宇

一、

update

语句的语法与原理

语法

单表： UPDATE

表名称 SET

列名称 =

新值 WHERE

列名称 =

某值

如：

update

t_join_situation

set

join_state=

'1'

where

year

'2011'

更新年度为“

2011

”的数据的

join_state

字段为“

”。如果更新的字段加了索引，更新时会重建索引，更新效率会慢。

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

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.

字段

oracle

的更新语句不通

MSSQL

那么简单易写，就算写出来了，但执行时可能会报

这是由于 set

哪里的子查询查出了多行数据值， oracle

规定一对一更新数据，所以提示出错。要解决这样必须保证查出来的值一一对应。

原理

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.

字段

。查表

的所有数据，循环每条数据，验证该条数据是否符合

exists(select 1 from

表

b where a.

字段

2=b.

字段

条件，如果是则执行

(select b.

字段

1 from

表

b where a.

字段

2=b.

字段

查询，查到对应的值更新

字段

中。关联表更新时一定要有

exists(select 1 from

表

b where a.

字段

2=b.

字段

这样的条件，否则将表

的其他数据的字段

更新为

null

值。

二、

提高

oracle

更新效率的各种解决方案

标准

update

语法

当你需要更新的表是单个或者被更新的字段不需要关联其他表带过来，则最后选择标准的 update

语句，速度最快，稳定性最好，并返回影响条数。如果 where

条件中的字段加上索引，那么更新效率就更高。但对需要关联表更新字段时， update

的效率就非常差。

inline view

更新法

inline view

更新法就是更新一个临时建立的视图。如：

update

(

select

a.join_state

join_state_a,b.join_state

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

中设置好更新的字段。这个解决方法比写法较直观且执行速度快。但表

的主键一定要在

where

条件中，并且是以“

”来关联被更新表，否则报一下错误：

merge

更新法

merge

是

oracle

特有的语句，语法如下：

MERGE

INTO

table_name alias1

USING (

table

view

sub_query) alias2

(

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

更新法将力不从心且效率差。

快速游标更新法

语法如：

begin

for

(

查询语句

)

loop

–-

循环

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

end

loop

;

结束循环

end

;

oracle

支持快速游标，不需要定义直接把游标写到

for

循环中，这样就方便了我们批量更新数据。再加上

oracle

的

rowid

物理字段（

oracle

默认给每个表都有

rowid

这个字段，并且是唯一索引），可以快速定位到要更新的记录上。

例子如下：

begin

for

(

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

join_state_a,b.join_state

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

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

(

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

(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

本文转自 vfast_chenxy 51CTO博客，原文链接：http://blog.51cto.com/chenxy/811672，如需转载请自行联系原作者