分享
 
 
 

ORACLE UPDATE 语句语法与性能分析的一点看法

王朝oracle·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

ORACLE UPDATE 语句语法与性能分析的一点看法

为了方便起见,建立了以下简单模型,和构造了部分测试数据:

在某个业务受理子系统BSS中,

--客户资料表

create table customers

(

customer_id number(8) not null, -- 客户标示

city_name varchar2(10) not null, -- 所在城市

customer_type char(2) not null, -- 客户类型

...

)

create unique index PK_customers on customers (customer_id)

由于某些原因,客户所在城市这个信息并不什么准确,但是在

客户服务部的CRM子系统中,通过主动服务获取了部分客户20%的所在

城市等准确信息,于是你将该部分信息提取至一张临时表中:

create table tmp_cust_city

(

customer_id number(8) not null,

citye_name varchar2(10) not null,

customer_type char(2) not null

)

1) 最简单的形式

--经确认customers表中所有customer_id小于1000均为'北京'

--1000以内的均是公司走向全国之前的本城市的老客户:)

update customers

set city_name='北京'

where customer_id<1000

2) 两表(多表)关联update -- 仅在where字句中的连接

--这次提取的数据都是VIP,且包括新增的,所以顺便更新客户类别

update customers a -- 使用别名

set customer_type='01' --01 为vip,00为普通

where exists (select 1

from tmp_cust_city b

where b.customer_id=a.customer_id

)

3) 两表(多表)关联update -- 被修改值由另一个表运算而来

update customers a -- 使用别名

set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)

where exists (select 1

from tmp_cust_city b

where b.customer_id=a.customer_id

)

-- update 超过2个值

update customers a -- 使用别名

set (city_name,customer_type)=(select b.city_name,b.customer_type

from tmp_cust_city b

where b.customer_id=a.customer_id)

where exists (select 1

from tmp_cust_city b

where b.customer_id=a.customer_id

)

注意在这个语句中,

=(select b.city_name,b.customer_type

from tmp_cust_city b

where b.customer_id=a.customer_id

)

(select 1

from tmp_cust_city b

where b.customer_id=a.customer_id

)

是两个独立的子查询,查看执行计划可知,对b表/索引扫描了2篇;

如果舍弃where条件,则默认对A表进行全表

更新,但由于(select b.city_name from tmp_cust_city b where where b.customer_id=a.customer_id)

有可能不能提供"足够多"值,因为tmp_cust_city只是一部分客户的信息,

所以报错(如果指定的列--city_name可以为NULL则另当别论):

01407, 00000, "cannot update (%s) to NULL"

// *Cause:

// *Action:

一个替代的方法可以采用:

update customers a -- 使用别名

set city_name=nvl((select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id),a.city_name)

或者

set city_name=nvl((select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id),'未知')

-- 当然这不符合业务逻辑了

4) 上述3)在一些情况下,因为B表的纪录只有A表的20-30%的纪录数,

考虑A表使用INDEX的情况,使用cursor也许会比关联update带来更好的性能:

set serveroutput on

declare

cursor city_cur is

select customer_id,city_name

from tmp_cust_city

order by customer_id;

begin

for my_cur in city_cur loop

update customers

set city_name=my_cur.city_name

where customer_id=my_cur.customer_id;

/** 此处也可以单条/分批次提交,避免锁表情况 **/

-- if mod(city_cur%rowcount,10000)=0 then

-- dbms_output.put_line('----');

-- commit;

-- end if;

end loop;

end;

5) 关联update的一个特例以及性能再探讨

在oracle的update语句语法中,除了可以update表之外,也可以是视图,所以有以下1个特例:

update (select a.city_name,b.city_name as new_name

from customers a,

tmp_cust_city b

where b.customer_id=a.customer_id

)

set city_name=new_name

这样能避免对B表或其索引的2次扫描,但前提是 A(customer_id) b(customer_id)必需是unique index

或primary key。否则报错:

01779, 00000, "cannot modify a column which maps to a non key-preserved table"

// *Cause: An attempt was made to insert or update columns of a join view which

// map to a non-key-preserved table.

// *Action: Modify the underlying base tables directly.

6)oracle另一个常见错误

回到3)情况,由于某些原因,tmp_cust_city customer_id 不是唯一index/primary key

update customers a -- 使用别名

set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)

where exists (select 1

from tmp_cust_city b

where b.customer_id=a.customer_id

)

当对于一个给定的a.customer_id

(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)

返回多余1条的情况,则会报如下错误:

01427, 00000, "single-row subquery returns more than one row"

// *Cause:

// *Action:

一个比较简单近似于不负责任的做法是

update customers a -- 使用别名

set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)

如何理解 01427 错误,在一个很复杂的多表连接update的语句,经常因考虑不周,出现这个错误,

仍已上述例子来描述,一个比较简便的方法就是将A表代入 值表达式 中,使用group by 和

having 字句查看重复的纪录

(select b.customer_id,b.city_name,count(*)

from tmp_cust_city b,customers a

where b.customer_id=a.customer_id

group by b.customer_id,b.city_name

having count(*)>=2

)

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有