时间戳

首页 > 解决方案 > 我应该清除每个要在 forall 中使用的 fetch 循环的集合吗？PLSQL 甲骨文

sql - 我应该清除每个要在 forall 中使用的 fetch 循环的集合吗？PLSQL 甲骨文

问题描述

假设我在 table1 中有大量数据（100k-1m 行），我需要进行一些检查，然后根据过滤结果更新它们在 table2 上的状态。正如您在我的简化代码中看到的那样。我每批批量收集 1000 行并将它们过滤到 3 个不同的集合（dsa1、dsa2、dsa3）中，然后再将这 3 个集合更新到 table2 中。

我的问题是，假设第一次提取将 100 行放入 dsa1，然后第二次提取仅将 70 行放入 dsa1。当 forall 更新运行时，它还会从第一次提取更新 dsa1 中的旧 30 行。

2 我想到的解决方案是首先，删除收集每个 fetch 循环中的所有元素。第二个是将 forall 放在 fetch 循环之外，这将使 3 个集合非常大，但 forall 只调用一次。

第二种解决方案会占用大量内存吗？请咨询什么是最好的解决方案

declare

cursor c1 is 
select t1.id, t1.status, t2.con from table1 t1, table2 t2
where t1.id = t2.id;

type ty_c1 is table of c1%rowtype;
asd1 ty_c1 := ty_c1();

type ty_id is table of c1.id%type index by pls_integer;
dsa1 ty_id;
dsa2 ty_id;
dsa3 ty_id;
begin
    open c1;
    loop
        fetch c1 bulk collect into asd1 limit 1000;
        exit when asd1.count = 0;

        for i in 1 .. asd1.count
        loop
            if (asd1(i).status = 'ACT') then
                dsa1(i).id := asd1(i).id;
            elsif (asd1(i).status = 'NOT ACT') then
                dsa2(i).id := asd1(i).id;
            else
                dsa3(i).id := asd1(i).id;
            end if;
        end loop;

        forall idx in indices of dsa1
            update table2 set con = 'ACTIVE'
            where id = dsa1(idx).id;

        forall idx in indices of dsa2
            update table2 set con = 'NOT ACTIVE'
            where id = dsa2(idx).id;

        forall idx in indices of dsa3
            update table2 set con = 'DEAD'
            where id = dsa3(idx).id;


    end loop;
    close c1;
end;

标签： sqloracleplsqlbulkupdate

解决方案

从性能的角度来看，构建三个集合并只访问一次数据库会提高性能，但会使用更多内存。

因此，答案取决于您将要处理的典型卷和可用内存。

您还可以有第四个集合，该集合保持为空，并将空集合分配给循环内的 dsa1、dsa2 和 dsa3，而不是删除条目。

但是看看你的代码，因为只有状态在改变，为什么不根据项目和状态的记录创建一个集合，或者有第二个集合来保存状态，然后不管状态如何，你将一次更新 1000 条记录一个集合。

更进一步，由于 1M 记录对于 Oracle 数据库来说不算什么，（数十亿记录是巨大的，一百万记录是微不足道的）只需使用数据库执行单个更新选择语句

update table2 t2
set conn=(select decode(t1.status, 'ACT', 'ACTIVE', 'NOT ACT','NON ACTIVE','DEAD') 
from table1 t1)
where t2.id=t1.id

t2 中不存在于 t1 中的 NB 行将 conn 设置为 null，但如果不需要，您可以限制 where 子句以限制影响。

您还可以在更新和选择上添加并行提示以使用更多 CPU，如果您能够修改数据模型，使用数字代码而不是 varchar 来表示状态也会更有效。

推荐阅读