代码之家 › 专栏 › 技术社区 › John Wick

存储过程耗时太长,是否有更好的方法来执行此操作/优化?

plsql oracle sql

John Wick · 技术社区 · 7 年前

目前,我正在尝试通过数据库链接大容量插入大量数据(约500000行)。我从物化视图获取数据。我本打算添加索引,但在某处读到这实际上会减慢进程。在插入行之后,我将获取惟一的id并将它们插入到一个标记表中,这样它们就被标记为“inserted”,并且不再被插入。然而,这个过程现在已经被卡住了大约30分钟。有更好的方法吗?(以下是我的代码)。

create or replace PROCEDURE   SEND_DATA
IS
   CURSOR cursora
   IS
      SELECT DISTINCT unique_id_1
        FROM mv_1;

   CURSOR cursorb
   IS 
      SELECT DISTINCT unique_id_2
       FROM mv_2;

ca cursora%ROWTYPE;
cb cursorb%ROWTYPE;

    sent_flag NUMBER(10);
BEGIN
    SELECT flag_id
     INTO sent_flag
     FROM flag f
    WHERE f.flag_tx = 'SENT';
---
Delete FROM TABLE1@db1
      WHERE to_date(to_char(LOCAL_TIMESTAMP,'mm/dd/yyyy'),'mm/dd/yyyy') || code in 
 (SELECT distinct to_date(to_char(LOCAL_TIME_TS,'mm/dd/yyyy'),'mm/dd/yyyy'), code FROM MV_1);
COMMIT;
    Delete FROM TABLE1@db1 
          WHERE type || timestamp in (SELECT DATA_Type_TX || UTC_TS FROM MV_1);
    COMMIT;
    insert into TABLE1@db1(DATE, TYPE, VALUE, LAST_UPDATE, FLAG, LOCAL_TIMESTAMP)
    SELECT DATA_DATE,  NAME, VALUE, SYSDATE, null, LOCAL_TIME
  FROM MV_2 A;

COMMIT;
OPEN cursora;

LOOP
 FETCH cursora into ra;
 EXIT WHEN cursora%NOTFOUND;
 INSERT INTO flag(
    SUBMIT_ID,
    FLAG_ID,
    CREATE_USER_ID,
         CREATE_DT)
   VALUES (
    rdba.SUBMIT_ID,
    SENT_FLAG,
    '1',
         sysdate);
END LOOP;
CLOSE cursora;
COMMIT;
---
EXCEPTION
      WHEN OTHERS
      THEN
           NULL;
       RAISE;
    END SEND_DATA;

1 回复 | 直到 7 年前

Wernfried Domscheit 7 年前

你的程序有几个缺陷,实际上应该失败。

create or replace PROCEDURE   SEND_DATA IS
   CURSOR cursora IS
      SELECT DISTINCT unique_id_1
        FROM mv_1;

   CURSOR cursorb IS 
      SELECT DISTINCT unique_id_2
       FROM mv_2;

光标 cursorb 在程序中没有使用,为什么要声明它?

Delete FROM TABLE1@db1
      WHERE to_date(to_char(LOCAL_TIMESTAMP,'mm/dd/yyyy'),'mm/dd/yyyy') || code in 
 (SELECT distinct to_date(to_char(LOCAL_TIME_TS,'mm/dd/yyyy'),'mm/dd/yyyy'), code FROM MV_1);

这应该失败,因为首先将两个列连接起来,但是 IN () 选择两列。无论如何,移除 DISTINCT

    Delete FROM TABLE1@db1 
          WHERE type || timestamp in (SELECT DATA_Type_TX || UTC_TS FROM MV_1);

您不应该使用保留关键字,例如 TIMESTAMP 作为列名。

LOOP
 FETCH cursora into ra;
 EXIT WHEN cursora%NOTFOUND;
 INSERT INTO flag(SUBMIT_ID, FLAG_ID, CREATE_USER_ID, CREATE_DT)
   VALUES ( rdba.SUBMIT_ID, SENT_FLAG, '1', sysdate);
END LOOP;

为什么要用引号括起数值(例如。 '1' )? 此代码也应该失败,因为变量 ra 和 rdba 未声明。我想是的

LOOP
 FETCH cursora into ca;
 EXIT WHEN cursora%NOTFOUND;
 INSERT INTO flag(SUBMIT_ID, FLAG_ID, CREATE_USER_ID, CREATE_DT)
   VALUES ( ca.unique_id_1, SENT_FLAG, '1', sysdate);
END LOOP;

将此改写为

INSERT INTO flag (SUBMIT_ID, FLAG_ID, CREATE_USER_ID, CREATE_DT)
SELECT DISTINCT unique_id_1, SENT_FLAG, 1, sysdate
FROM mv_1;

假设上述假设将呈现正确的逻辑

EXCEPTION
      WHEN OTHERS
      THEN
           NULL;
       RAISE;

WHEN OTHERS THEN NULL; 意思是“忽略任何错误”,但在下一行中,您将提出它。