对于循环更新更好的替代方案

Question

在Oracle 11g中，我在过程中使用以下内容。。有人能提供一个更好的解决方案来达到同样的效果吗。

FOR REC IN 
(SELECT E.EMP FROM EMPLOYEE E 
JOIN 
COMPANY C ON E.EMP=C.EMP
WHERE C.FLAG='Y')
LOOP
UPDATE EMPLOYEE SET FLAG='Y' WHERE EMP=REC.EMP;
END LOOP;

有没有更有效/更好的方法来做到这一点？我觉得这个方法会为找到的每个记录运行一个更新语句(如果我错了，请纠正我)。

以下是完整的实际代码：

create or replace 
PROCEDURE ACTION_MSC AS 
BEGIN
-- ALL MIGRATED CONTACTS, CANDIDATES, COMPANIES, JOBS
-- ALL MIGRATED CANDIDATES, CONTACTS
FOR REC IN (SELECT DISTINCT AC.PEOPLE_HEX 
FROM ACTION AC JOIN PEOPLE P ON AC.PEOPLE_HEX=P.PEOPLE_HEX 
WHERE P.TO_MIGRATE='Y')
LOOP
UPDATE ACTION SET TO_MIGRATE='Y' WHERE PEOPLE_HEX=REC.PEOPLE_HEX;
END LOOP;
-- ALL MIGRATED COMPANIES
FOR REC IN (SELECT DISTINCT AC.COMPANY_HEX 
FROM ACTION AC JOIN COMPANY CM ON AC.COMPANY_HEX=CM.COMPANY_HEX
WHERE CM.TO_MIGRATE='Y')
LOOP
UPDATE ACTION SET TO_MIGRATE='Y' WHERE COMPANY_HEX=REC.COMPANY_HEX;
END LOOP;
-- ALL MIGRATED JOBS
FOR REC IN (SELECT DISTINCT AC.JOB_HEX 
FROM ACTION AC JOIN "JOB" J ON AC.JOB_HEX=J.JOB_HEX
WHERE J.TO_MIGRATE='Y')
LOOP
UPDATE ACTION SET TO_MIGRATE='Y' WHERE JOB_HEX=REC.JOB_HEX;
END LOOP;
COMMIT;
END ACTION_MSC;

Answer 1

你是对的，它将为找到的每个记录进行一次更新。看起来你可以做：

UPDATE EMPLOYEE SET FLAG = 'Y'
WHERE EMP IN (SELECT EMP FROM COMPANY WHERE FLAG = 'Y')
AND FLAG != 'Y';

单个更新通常比循环中的多个单独的行更新更快、更高效；另一个例子见这个答案。除此之外，您还减少了PL/SQL和SQL之间的上下文切换的数量，如果您有很多行，这些切换就会相加。当然，您可以随时使用自己的数据进行基准测试。

我添加了对当前标志状态的检查，这样你就不会在没有chadges的情况下进行毫无意义的更新。

---

比较这些方法很容易看出，单个更新比循环中的更新更快；有一些人为的数据：

create table people (id number, people_hex varchar2(16), to_migrate varchar2(1));
insert into people (id, people_hex, to_migrate)
select level, to_char(level - 1, 'xx'), 'Y'
from dual
connect by level <= 100;
create table action (id number, people_hex varchar2(16), to_migrate varchar2(1));
insert into action (id, people_hex, to_migrate)
select level, to_char(mod(level, 200), 'xx'), 'N'
from dual
connect by level <= 500000;

所有这些都将更新action表中的一半行。循环更新：

begin
for rec in (select distinct ac.people_hex 
from action ac join people p on ac.people_hex=p.people_hex 
where p.to_migrate='Y')
loop
update action set to_migrate='Y' where people_hex=rec.people_hex;
end loop;
end;
/
Elapsed: 00:00:10.87

单个更新(回滚后；我将其保留在一个块中以模仿您的过程)：

begin
update action set to_migrate = 'Y'
where people_hex in (select people_hex from people where to_migrate = 'Y');
end;
/
Elapsed: 00:00:07.14

合并(回滚后)：

begin
merge into action a
using (select people_hex, to_migrate from people where to_migrate = 'Y') p
on (a.people_hex = p.people_hex)
when matched then update set a.to_migrate = p.to_migrate;
end;
/
Elapsed: 00:00:07.00

重复运行有一些变化，特别是更新和合并通常非常接近，但有时交换在我的环境中更快；但两者总是比循环中的更新快得多。您可以在自己的环境中，使用自己的数据分布和卷重复此操作，如果性能如此关键，您应该这样做；但是一次更新将比循环更快。无论你是使用更新还是合并都不会有太大区别。