有没有更快的方法可以在oraclepl/sql中每周对120k条记录执行合并

Question

数据库中大约有120k条记录，我根据一些函数计算所有记录的分数，每周我必须用新记录和各自的分数更新表格。

以下是我用来将数据合并到表中的过程：

create or replace procedure scorecalc
AS
score1 number;
score2 number;
score3 number;
CURSOR cur IS
SELECT Id_number from tableA;
r_num cur%ROWTYPE;
BEGIN
--OPEN cur;
FOR r_num IN cur
LOOP
select functionA(r_num.id_number),functionb(r_num.id_number),functionc(r_num.id_number)  into score1, score2,score3 from dual;
Merge into scores A USING
(Select
r_num.id_number as ID, score1 as scorea, score2 as scoreb, score3 as scorec, TO_DATE(sysdate, 'DD/MM/YYYY') as scoredate
FROM DUAL) B
ON ( A.ID = B.ID and A.scoredate = B.scoredate)
WHEN NOT MATCHED THEN
INSERT (
ID, scorea, scoreb, scorec, scoredate)
VALUES (
B.ID, B.scorea, B.scoreb, B.scorec,B.scoredate)
WHEN MATCHED THEN
UPDATE SET
A.scorea = B.scorea,
A.scoreb = B.scoreb,
A.scorec = B.scorec;
COMMIT;
END LOOP;
END;

而函数A/B/C具有复杂的查询，加入其中来计算分数。

请给我建议任何提高性能的方法，因为目前有了这段代码，我只能在1小时内插入2k条记录？我可以在这里使用并行DML吗？非常感谢。

Answer 1

为什么要在过程中这样做？这一切都可以通过DML:完成

MERGE INTO scores a USING
(SELECT ta.id_number AS ID, 
functionA(ta.id) AS scoreA,
functionB(ta.id) AS scoreB,
functionC(ta.id) AS scoreC,
TO_DATE(sysdate, 'DD/MM/YYYY') as scoredate
FROM tableA ta) b
ON (a.id = b.id AND a.scoredate = b.scoredate)
WHEN MATCHED THEN UPDATE SET
a.scorea = b.scorea,
a.scoreb = b.scoreb,
a.scorec = b.scorec
WHEN NOT MATCHED THEN INSERT (ID, scorea, scoreb, scorec, scoredate)
VALUES (B.ID, B.scorea, B.scoreb, B.scorec,B.scoredate);

如果你想在那之后尝试使用PARALLEL提示，请放心。但是你绝对应该去掉那个光标，停止做"慢慢来"的处理。

Answer 2

我成功地从select语句中插入了一些内容。它的性能非常好，因为它不涉及逐行插入。

在你的情况下，我认为它会像：

INSERT INTO table (ID, scorea, scoreb, scorec, scoredate)
SELECT functionA(id_number), functionB(id_number), functionC(id_number)
FROM tableA

可以在以下链接中找到一个示例：https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems025.htm

Answer 3

要调度它，只需将语句@Del放在过程块中；

create or replace procedure Saturday_Night_Merge is 
begin 
<Put the merge statement here> 
end Saturday_Night_Merge;