Oracle 有什么更快的方法可以对120k条记录执行合并

数据库中大约有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吗？谢谢!

1) 删除提交
2) 移除环路
3) 删除PLSQL

:-)

整个代码可以是单个SQL

Merge into scores A 
USING
    (Select r_num.id_number as ID, functionA(r_num.id_number) as scorea, functionb(r_num.id_number) as scoreb, functionc(r_num.id_number) as scorec, TO_DATE(sysdate, 'DD/MM/YYYY') as scoredate
     FROM tableA r_num) 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;

复制