update效率问题

Question

本帖最后由 lbxx159753 于 2010-06-01 18:51:55 编辑

Answer 1

用mergemerge into lzyrealamount_gsm_0912 a
using (select svcnum, sum(chrgamount) as chrgamount
from lzysvcnum_acctinsht
group by  svcnum ) b
on(a.msisdn=b.svcnum and a.innetmonth <= '200911')
when matched then update set (a.chargefee=b.chrgamount)

Answer 2

update效率太低，需要写子查询，比较麻烦

Answer 3

oracle 有merge这样的语句，用merge完全可以代替update多个表update效果特别明显

Answer 4

http://blog.chinaunix.net/u1/55091/showart_430716.html用google  不要用 baidu 。。搜东西要有针对性  要会搜索

Answer 5

这个我看过了啊，能不能讲一下merge它的原理。还有，比如说下面句子
MERGE INTO products p
USING newproducts np
ON (1=0)
WHEN NOT MATCHED THEN
INSERT
VALUES (np.product_id, np.product_name, np.category)
WHERE np.category = 'BOOKS'
你把p表和np表对调有什么区别。
on（）中的条件和where条件有什么不同，如果我把where的条件放到on（）里面，效果有什么区别啊？

Answer 6

MERGE INTO products p  -- 更新表 P 
USING newproducts np   -- 使用表 NP
ON (1=0)             -- 更新的条件
WHEN NOT MATCHED THEN  -- 如果不能更新 
INSERT               -- 插入语句 
VALUES (np.product_id, np.product_name, np.category)
WHERE np.category = 'BOOKS'  -- 插入语句条件 
我没用过MERGE INTO  大概我是这么理解的 应该没错 

Answer 7

哈哈，大神就是大神。
那如果我这样写
MERGE INTO products p 
USING newproducts np 
ON (np.category = 'BOOKS') -- 更新的条件
WHEN MATCHED THEN INSERT 
VALUES (np.product_id, np.product_name, np.category)
会是什么结果啊？

Answer 8

MERGE啊，我用了多年的Oracle，还不知道有那方法呢，我只知道SQLSERVER2008下面支持，看来是微软抄袭了，不知道是不是一样的用法。我还是比较喜欢使用for游标来实现更新，速度最快，而且不容易出错。

Answer 9

merge不行，就换update(inline视图)的方法，oracle特有
再不行，forall bulk+limit更新
注意你是在过程里写的，注意字面量用绑定变量替代！~
基本就这么几个技巧！

Answer 10

小弟还是菜鸟啊。inline视图根本不会。加上limit也就见过，对他的基本原理也不清楚。问题中给出的语句是前面的人写好的，按理来说应该能够满足日常工作。但是现在执行起来变得特慢，不知道那两个语句（红色和蓝色）的效率是否能够满足百万数据量。
大神们能不能给出一个理论上的比较，对于大数据量那种方法比较好。现在我知道的有update表关联，merge，游标，还有你刚才说的inline视图（等等百度上学习下）。那种比较适合，或者组合起来使用

Answer 11

merge好不好我不清楚，没有研究过，但是使用inline视图或者update表关联（两者原理相同）都不好，因为有些特殊情况更新不了，使用游标更新是万能的，而且速度保证，功能非常灵活。Oracle支持for游标，方便了游标的使用，用于更新最适合不过了。题外话，要敢于推翻前人的东西，他们写的不见得好，你那个语句之所以慢，就在于SET操作时需要单独执行查询，这样每更新一行数据就全表遍历查询一次数据给它，同时where语句部分需要每行数据都执行查询验证是否存在，能不慢就有鬼了。

Answer 12

你说的太好了！！！
我看过merge的原理了，在9i中，它的作用是查询B表（用作比较的表）的数据在A表（咱们要进行更新或者插入的表）中是否存在，存在就更新，否则就插入。所以大家使用的时候一定要小心啊。merge的操作次数完全由B表的数据条数来决定（当然要考虑where条件判断）决定。我还想问一下，游标我看很多人说当数据量比较大的时候就不行了，而且微软文档也说当数据超过2000条（我记不太清楚了啊）就不建议在使用游标了。嘿嘿，其实你才是真正解答我疑惑的人，我就想知道红色部分和蓝色部分的效率及其执行过程中的原理。那我回去改下，我开始也觉得set操作后面要调用子查询。

Answer 13

微软的游标没有Oracle的游标好，先是它不支持快速游标，声明后再使用，还需要一堆变量，麻烦死。
再是Oracle游标更新时需要用ROWID，这个微软绝对没有提供支持，这样即使是上百万数据的表中更新几万数据也不是问题。

Answer 14

哦，我都是在网上搜的，对游标也不了解。我分析了一下，把里面的很多条件拿出来单独测试
--这是原句
update lzyrealamount_gsm_0912 a --这个表数据量774906
set chargefee = (select sum(chrgamount) 
from lzysvcnum_acctinsht
where svcnum = a.msisdn)
where a.innetmonth <= '200911'
and exists (select 1 from lzysvcnum_acctinsht --这个表数据量4469617
where svcnum = a.msisdn);
--set后面的子查询
select sum(chrgamount) 
from lzysvcnum_acctinsht,lzyrealamount_gsm_0912 a
where svcnum = a.msisdn;--没有建立索引单独执行一次耗时平均为22.5秒，建立索引平均时间为10.5秒
--满足更新条件的数据总条数
select count(*) from lzyrealamount_gsm_0912 a--这个表数据量774906
where a.innetmonth <= '200911'
and exists (select 1 from lzysvcnum_acctinsht --这个表数据量4469617
where svcnum = a.msisdn);--符合条件的数据为352081条，搜索时间为30秒，为总表数量的一半
你说用什么方法优化比较好啊，我现在想把满足更新条件的语句抽出来，然后用游标循环
for abc (select chargefee from lzyrealamount_gsm_0912 a
where a.innetmonth <= '200911'
and exists (select 1 from lzysvcnum_acctinsht
where svcnum = a.msisdn)) loop
abc := (select sum(chrgamount) 
from lzysvcnum_acctinsht
where svcnum = a.msisdn);
end loop;
你说这个行不啊，我也不太懂。可能语法也有问题

Answer 15

免得你走弯路了，我直接把你贴的那段SQL语句改写好了，你去执行下，我猜的没错的话，30秒内可以更新完毕。
begin
for cr in (select a.rowid, sum(b.chrgamount) chrgamount  from lzyrealamount_gsm_0912 a,lzysvcnum_acctinsht b
where a.msisdn = b.svcnum and a.innetmonth <= '200911') loop
update lzyrealamount_gsm_0912 set chargefee = cr.chrgamount where rowid = cr.rowid;
end loop;
end;

Answer 16

哈哈，你人太好了begin
  for abc in (select chargefee from lzyrealamount_gsm_0912 a
  where a.innetmonth <= '200911'
  and exists (select 1 from lzysvcnum_acctinsht
  where svcnum = a.msisdn)) loop
    select sum(chrgamount) into abc.chargefee
    from lzysvcnum_acctinsht,lzyrealamount_gsm_0912
    where svcnum = msisdn;
  end loop;
end;这是我刚才用的方法，结果老报错，说什么number too large
哎，大神就是大神啊。

Answer 17

我靠，你真乃神人也，40秒搞定。
对了，你少了个group by a.rowid,我太崇拜你了

Answer 18

不好意思，写错了，这里纠正下：
begin
for cr in (select a.rowid, sum(b.chrgamount) chrgamount  from lzyrealamount_gsm_0912 a,lzysvcnum_acctinsht b
where a.msisdn = b.svcnum and a.innetmonth <= '200911'
group by a.rowid) loop
update lzyrealamount_gsm_0912 set chargefee = cr.chrgamount where rowid = cr.rowid;
end loop;
end;如果你执行下也会发现，会报一个不是分组的错误，因为这里面用到了分组汇总，忘了加了。

Answer 19

咦，我这里只报了缺少group by ，没有分组错误啊。
大神，我真的太崇拜你了