在Oracle数据库中使用过程插入大数据集

Question

我有一个表AA，其中包含6列(a、b、c、d、e、f)，其中有2亿条记录将a、b、c作为组合键。列a、列b或列c可能有多个重复条目。列c中的每个a和b都有多个条目。

我必须将列a、b、d、e、f (c不存在)的值复制到另一个表AB中，该AB包含列a、b、d、D24、D25，并将D26、D27、D28作为组合键，以便D29列的最大值为2，D30、D31、D32列的最大条目数应为2。

我已经写了一个过程：

CREATE OR REPLACE Table_CHANGES(d IN AB.d%TYPE)
AS
  aaa     AB.a%TYPE;
  aab AB.b%TYPE;
  i             NUMBER   :=   0;
  countRow      NUMBER; 

CURSOR distinctUserIdCursor IS 
    SELECT DISTINCT urt.a, urt.b FROM AA urt WHERE urt.d = aad ORDER BY  urt.a;

BEGIN
    OPEN distinctUserIdCursor;
    LOOP

    FETCH distinctUserIdCursor INTO aaa, aab;
    EXIT WHEN distinctUserIdCursor%NOTFOUND;

    SELECT COUNT(*) INTO countRow FROM AB ur
    WHERE ur.a = aaa 
    AND ur.b = aab
    AND ur.d = aad;

    IF (countRow > 0) THEN
        DBMS_OUTPUT.PUT_LINE('Entry exist in AB');
    ELSE
        INSERT INTO AB 
        SELECT urt.a, urt.b, urt.d, urt.e, 
        urt.f FROM AA urt 
        WHERE urt.a = aaa 
        AND urt.d = aad
        AND urt.b = aab
        AND ROWNUM = 1;

        i := i+1;
    END IF;

    IF (i = 1000) THEN
        COMMIT;
        i := 0;
    END IF;

END LOOP;
COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error in Insertion of new role' 
                || '~~~~' || SQLERRM);
CLOSE distinctUserIdCursor;
END;
/

在toad中以exec Table_CHANGES('value1')和exec Table_CHANGES('value2')的身份执行此过程时，大约需要40分钟，并一直在Toad中执行。很难知道toad实际上是在执行任务还是在睡觉。

我是否可以优化这个过程，使其在表AB中插入数千万数据所需的时间更短？

Answer 1

我尝试使用集合来实现您正在做的事情。请查看这是否符合您的要求并提高性能。

create table AA(a number, b number,c number,d number, e number,f number );

create table AB(a number, b number,d number, e number,f number );

操作步骤：

    CREATE OR REPLACE procedure Table_CHANGES(aad IN AB.d%TYPE)
    AS
    --  aaa           AB.a%TYPE;
    --  aab           AB.b%TYPE;
    --  i             NUMBER   :=   0;
    --  countRow      NUMBER; 

   ///**Put some filtering logic here like i have put a minus ***///
    CURSOR distinctUserIdCursor(aad number) IS 
        SELECT DISTINCT  urt.a, 
                urt.b,
                urt.d, 
                urt.e, 
                urt.f 
        FROM AA urt      
        WHERE urt.d = aad

        minus
        SELECT DISTINCT ur.a, 
                ur.b,
                ur.d, 
                ur.e, 
                ur.f 
        FROM AB ur      
        WHERE ur.d = aad;


    type var_cur is table of distinctUserIdCursor%rowtype;

    var var_cur;

    BEGIN
        OPEN distinctUserIdCursor(aad);

        LOOP

        FETCH distinctUserIdCursor bulk collect into var limit 100;       
         EXIT WHEN distinctUserIdCursor%NOTFOUND;

         FORALL i IN 1 .. var.count SAVE EXCEPTIONS
          INSERT INTO AB 
          VALUES var(i);              

        END LOOP;

        CLOSE distinctUserIdCursor;

    COMMIT;

    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Error in Insertion of new role' || '~~~~' || SQLERRM);
          FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
             LOOP
                DBMS_OUTPUT.put_line (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX|| ': '
                   || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
          END LOOP;

    END;
    /

执行：

execute Table_CHANGES(3)