sql - 以 block 的形式移动大量数据

Question

我正在编写一个脚本，其中应根据某些条件查询大量数据并分别移动到某些存档表。我有超过 5000 万条记录需要扫描并选择匹配的记录，以便对六个存档表执行 INSERT 操作。下面的脚本适用于大约 50 万条记录，但在运行数百万条记录时抛出以下异常

Error report:
ORA-04036: PGA memory used by the instance exceeds PGA_ AGGREGATE _LIMIT

除了增加 PGA_ AGGREGATE _LIMIT 之外，我想改进我的脚本，避免将所有记录加载到内存中，而是运行脚本并将值分块插入表中。目前我不知道应该怎么做。有人可以建议我通过让脚本以批处理方式运行来避免内存不足的问题

下面是我的脚本的一部分(显示了将值插入到四个表中)。

CREATE OR REPLACE TYPE R1_ID_TYPE IS TABLE OF NUMBER;
/
CREATE OR REPLACE TYPE R5_ID_TYPE IS TABLE OF NUMBER;
/
DECLARE
     R01_IDS R1_ID_TYPE;
     R05_IDS R5_ID_TYPE;

BEGIN
    --add the R05_IDs which are older than five years from R5_TABLE and R6_TABLE to R5_ID_TYPE nested table
    SELECT  R5.R05_ID AS R05_ID
    BULK COLLECT INTO R05_IDS
    FROM R6_TABLE R6 , R5_TABLE R5
          WHERE R5.R05_ID = R6.R06_R05_ID_FK
          AND R5.R05_DATE_TIME_CAPTURED <= TRUNC(SYSDATE) - 1825
          AND R5.R05_STATUS = 'D'
          AND R6.R06_STATUS = 'D';

    -- Inserts all the deregistered records which are older than five years from R5_TABLE and R6_TABLE tables to the relevant archive tables
    INSERT ALL
    INTO R5_TABLE_archived(
      R05_ID, 
      R05_R01_ID_FK,
      R05_NUMBER,
      R05_NUMBER_TYPE,
      R05_STATUS,
      R05_GSM_SUBSCRIBER_TYPE
      R05_DATE_TIME_CAPTURED) 
      values (
        R5_R05_ID, 
        R5_R05_R01_ID_FK,
        R5_NUMBER,
        R5_NUMBER_TYPE,
        R5_R05_STATUS,
        R5_R05_GSM_SUBSCRIBER_TYPE,
        R5_R05_DATE_TIME_CAPTURED)
    INTO R6_TABLE_archived(
        R06_ID,
        R06_R05_ID_FK,
        R06_R08_ID_FK,
        R06_STATUS,
        R06_REFERENCE_NUMBER,
        R06_DATE_TIME_CAPTURED,
        R06_DATE_EXPIRED) 
        values (
        R6_R06_ID,
        R6_R06_R05_ID_FK,
        R6_R06_R08_ID_FK,
        R6_R06_STATUS,
        R6_R06_REFERENCE_NUMBER,
        R6_R06_DATE_TIME_CAPTURED,
        R6_R06_DATE_EXPIRED)   
    SELECT R5_R05_ID, 
        R5_R05_R01_ID_FK,
        R5_NUMBER,
        R5_NUMBER_TYPE,
        R5_R05_STATUS,
        R5_R05_GSM_SUBSCRIBER_TYPE,
        R5_R05_DATE_TIME_CAPTURED,
        R6_R06_ID,
        R6_R06_R05_ID_FK,
        R6_R06_R08_ID_FK,
        R6_R06_CHANGE_SOURCE,
        R6_R06_REFERENCE_NUMBER,
        R6_R06_DATE_TIME_CAPTURED,
        R6_R06_DATE_EXPIRED
    FROM
    (
    SELECT R5.R05_ID R5_R05_ID, 
        R5.R05_R01_ID_FK R5_R05_R01_ID_FK,
        R5.R05_NUMBER R5_NUMBER,
        R5.R05_NUMBER_TYPE R5_NUMBER_TYPE,
        R5.R05_STATUS R5_R05_STATUS,
        R5.R05_GSM_SUBSCRIBER_TYPE R5_R05_GSM_SUBSCRIBER_TYPE,
        R5.R05_DATE_TIME_CAPTURED R5_R05_DATE_TIME_CAPTURED,
        R6.R06_ID R6_R06_ID,
        R6.R06_R05_ID_FK R6_R06_R05_ID_FK,
        R6.R06_R08_ID_FK R6_R06_R08_ID_FK,
        R6.R06_STATUS R6_R06_STATUS,
        R6.R06_REFERENCE_NUMBER R6_R06_REFERENCE_NUMBER,
        R6.R06_DATE_TIME_CAPTURED R6_R06_DATE_TIME_CAPTURED,
        R6.R06_DATE_EXPIRED R6_R06_DATE_EXPIRED
      FROM R6_TABLE R6 , R5_TABLE R5
      WHERE R5.R05_ID = R6.R06_R05_ID_FK
      AND R5.R05_DATE_TIME_CAPTURED <= TRUNC(SYSDATE) - 1825
      AND R5.R05_STATUS = 'D'
      AND R6.R06_STATUS = 'D');       

    --selects all the R01 IDs which matches with the above criteria and copy values to respective archive tables
    SELECT UNIQUE R1.R01_ID AS R01_ID
    BULK COLLECT INTO R01_IDS                      
    FROM R1_TABLE R1, R5_TABLE R5
    WHERE R5.R05_ID IN (Select column_value from table(R05_IDS))
    AND R1.R01_ID NOT IN (
                        SELECT R01.R01_ID
                        FROM R1_TABLE R01,
                               R5_TABLE R05
                        WHERE R05.R05_STATUS != 'D'
                               AND R01.R01_ID = R05.R05_R01_ID_FK)
    AND R1.R01_ID = R5.R05_R01_ID_FK;   

    --insert R1_TABLE tables values which matches with the above criteria into the R1_TABLE_ARCHIVED table
    INSERT ALL
    INTO R1_TABLE_ARCHIVED(R01_ID,R01_ID_TYPE,R01_IDENTITY_NUMBER,R01_PASSPORT_COUNTRY,R01_DATE_TIME_CAPTURED)  
    VALUES (RA1_R01_ID,RA1_R01_ID_TYPE,RA1_R01_IDENTITY_NUMBER,RA1_R01_PASSPORT_COUNTRY,RA1_R01_DATE_TIME_CAPTURED)
    SELECT RA1_R01_ID,RA1_R01_ID_TYPE,RA1_R01_IDENTITY_NUMBER,RA1_R01_PASSPORT_COUNTRY,RA1_R01_DATE_TIME_CAPTURED
    FROM (
        SELECT
            r1.R01_ID  RA1_R01_ID,
            r1.R01_ID_TYPE  RA1_R01_ID_TYPE,
            r1.R01_IDENTITY_NUMBER  RA1_R01_IDENTITY_NUMBER,
            r1.R01_PASSPORT_COUNTRY  RA1_R01_PASSPORT_COUNTRY,
            r1.R01_DATE_TIME_CAPTURED  RA1_R01_DATE_TIME_CAPTURED
            FROM
            R1_TABLE r1
            WHERE 
            r1.R01_ID IN (Select column_value from table(R01_IDS))
    );

    --insert R2_TABLE tables values which matches with the above criteria into the R2_TABLE_ARCHIVED table
    INSERT ALL 
    INTO R2_TABLE_ARCHIVED(R02_ID,R02_R01_ID_FK,R02_fname,R02_SURNAME,R02_CONTACT_NUMBER,R02_DATE_TIME_CAPTURED)
    VALUES(RA2_R02_ID,RA2_R02_R01_ID_FK,RA2_R02_fname,RA2_R02_SURNAME,RA2_R02_CONTACT_NUMBER,RA2_R02_DATE_TIME_CAPTURED)
    SELECT  RA2_R02_ID,RA2_R02_R01_ID_FK,RA2_R02_fname,RA2_R02_SURNAME,RA2_R02_CONTACT_NUMBER,RA2_R02_DATE_TIME_CAPTURED
    FROM (
            SELECT
            r2.R02_ID  RA2_R02_ID,
            r2.R02_R01_ID_FK  RA2_R02_R01_ID_FK,
            r2.R02_fname  RA2_R02_fname,
            r2.R02_SURNAME  RA2_R02_SURNAME,
            r2.R02_CONTACT_NUMBER  RA2_R02_CONTACT_NUMBER,
            r2.R02_DATE_TIME_CAPTURED  RA2_R02_DATE_TIME_CAPTURED
            FROM
            R2_TABLE r2
            WHERE 
            r2.R02_R01_ID_FK IN (Select column_value from table(R01_IDS)));     


    --All the delete queries to remove the above copied values from the parent tables respectively  
    DELETE FROM R1_TABLE WHERE R01_ID IN (Select column_value from table(R01_IDS));
    DELETE FROM R2_TABLE WHERE R02_R01_ID_FK IN (Select column_value from table(R01_IDS));
    DELETE FROM R5_TABLE WHERE R05_R01_ID_FK IN (Select column_value from table(R05_IDS));
    DELETE FROM R6_TABLE WHERE R06_R05_ID_FK IN (R05_IDS);      


COMMIT;
END;
/
COMMIT;

Answer 1

集合(和其他 PL/SQL 构造)存储在 session 内存 中。 (与存储在全局内存中的查询数据不同)。因为 session 内存是基于每个用户分配的，所以必须有一个限制，因为 RAM 仍然是一种相对昂贵的资源。

所以，你收到了这个错误......

ORA-04036: PGA memory used by the instance exceeds PGA_ AGGREGATE _LIMIT

...因为您的 session 已经占用了分配给 PGA( session 可用的内存池)的所有内存。

问题是您正试图用数百万行填充一个集合。尽管那一排很窄，但仍然没有打开。幸运的是，PL/SQL 有一个解决方案:它是 LIMIT 子句。

使用 LIMIT 我们可以用结果集的一个 block 填充一个集合，处理它并获取下一个 block 。没什么可改变的:

DECLARE
     R01_IDS R1_ID_TYPE;
     R05_IDS R5_ID_TYPE;
     cursor r5_cur is
        SELECT  R5.R05_ID
        BULK COLLECT INTO R05_IDS
        FROM R6_TABLE R6 , R5_TABLE R5
          WHERE R5.R05_ID = R6.R06_R05_ID_FK
          AND R5.R05_DATE_TIME_CAPTURED <= TRUNC(SYSDATE) - 1825
          AND R5.R05_STATUS = 'D'
          AND R6.R06_STATUS = 'D';       
BEGIN
    -- this is new
    open r5_cur;
    loop
        fetch r5_cur 
        BULK COLLECT INTO R05_IDS limit 100000;
        exit when R05_IDS.count() = 0;

        -- this is all your code

        -- Inserts all the deregistered records which are older than five years from R5_TABLE and R6_TABLE tables to the relevant archive tables
        INSERT ALL
        INTO R5_TABLE_archived(
          R05_ID, 
          R05_R01_ID_FK,
          R05_NUMBER,
          R05_NUMBER_TYPE,
          R05_STATUS,
          R05_GSM_SUBSCRIBER_TYPE
          R05_DATE_TIME_CAPTURED) 
          values (
            R5_R05_ID, 
            R5_R05_R01_ID_FK,
            R5_NUMBER,
            R5_NUMBER_TYPE,
            R5_R05_STATUS,
            R5_R05_GSM_SUBSCRIBER_TYPE,
            R5_R05_DATE_TIME_CAPTURED)
        INTO R6_TABLE_archived(
            R06_ID,
            R06_R05_ID_FK,
            R06_R08_ID_FK,
            R06_STATUS,
            R06_REFERENCE_NUMBER,
            R06_DATE_TIME_CAPTURED,
            R06_DATE_EXPIRED) 
            values (
            R6_R06_ID,
            R6_R06_R05_ID_FK,
            R6_R06_R08_ID_FK,
            R6_R06_STATUS,
            R6_R06_REFERENCE_NUMBER,
            R6_R06_DATE_TIME_CAPTURED,
            R6_R06_DATE_EXPIRED)   
        SELECT R5_R05_ID, 
            R5_R05_R01_ID_FK,
            R5_NUMBER,
            R5_NUMBER_TYPE,
            R5_R05_STATUS,
            R5_R05_GSM_SUBSCRIBER_TYPE,
            R5_R05_DATE_TIME_CAPTURED,
            R6_R06_ID,
            R6_R06_R05_ID_FK,
            R6_R06_R08_ID_FK,
            R6_R06_CHANGE_SOURCE,
            R6_R06_REFERENCE_NUMBER,
            R6_R06_DATE_TIME_CAPTURED,
            R6_R06_DATE_EXPIRED
        FROM
        (
        SELECT R5.R05_ID R5_R05_ID, 
            R5.R05_R01_ID_FK R5_R05_R01_ID_FK,
            R5.R05_NUMBER R5_NUMBER,
            R5.R05_NUMBER_TYPE R5_NUMBER_TYPE,
            R5.R05_STATUS R5_R05_STATUS,
            R5.R05_GSM_SUBSCRIBER_TYPE R5_R05_GSM_SUBSCRIBER_TYPE,
            R5.R05_DATE_TIME_CAPTURED R5_R05_DATE_TIME_CAPTURED,
            R6.R06_ID R6_R06_ID,
            R6.R06_R05_ID_FK R6_R06_R05_ID_FK,
            R6.R06_R08_ID_FK R6_R06_R08_ID_FK,
            R6.R06_STATUS R6_R06_STATUS,
            R6.R06_REFERENCE_NUMBER R6_R06_REFERENCE_NUMBER,
            R6.R06_DATE_TIME_CAPTURED R6_R06_DATE_TIME_CAPTURED,
            R6.R06_DATE_EXPIRED R6_R06_DATE_EXPIRED
          FROM R6_TABLE R6 , R5_TABLE R5
          WHERE R5.R05_ID = R6.R06_R05_ID_FK
          AND R5.R05_DATE_TIME_CAPTURED <= TRUNC(SYSDATE) - 1825
          AND R5.R05_STATUS = 'D'
          AND R6.R06_STATUS = 'D');       

        --selects all the R01 IDs which matches with the above criteria and copy values to respective archive tables
        SELECT UNIQUE R1.R01_ID AS R01_ID
        BULK COLLECT INTO R01_IDS                      
        FROM R1_TABLE R1, R5_TABLE R5
        WHERE R5.R05_ID IN (Select column_value from table(R05_IDS))
        AND R1.R01_ID NOT IN (
                            SELECT R01.R01_ID
                            FROM R1_TABLE R01,
                                   R5_TABLE R05
                            WHERE R05.R05_STATUS != 'D'
                                   AND R01.R01_ID = R05.R05_R01_ID_FK)
        AND R1.R01_ID = R5.R05_R01_ID_FK;   

        --insert R1_TABLE tables values which matches with the above criteria into the R1_TABLE_ARCHIVED table
        INSERT ALL
        INTO R1_TABLE_ARCHIVED(R01_ID,R01_ID_TYPE,R01_IDENTITY_NUMBER,R01_PASSPORT_COUNTRY,R01_DATE_TIME_CAPTURED)  
        VALUES (RA1_R01_ID,RA1_R01_ID_TYPE,RA1_R01_IDENTITY_NUMBER,RA1_R01_PASSPORT_COUNTRY,RA1_R01_DATE_TIME_CAPTURED)
        SELECT RA1_R01_ID,RA1_R01_ID_TYPE,RA1_R01_IDENTITY_NUMBER,RA1_R01_PASSPORT_COUNTRY,RA1_R01_DATE_TIME_CAPTURED
        FROM (
            SELECT
                r1.R01_ID  RA1_R01_ID,
                r1.R01_ID_TYPE  RA1_R01_ID_TYPE,
                r1.R01_IDENTITY_NUMBER  RA1_R01_IDENTITY_NUMBER,
                r1.R01_PASSPORT_COUNTRY  RA1_R01_PASSPORT_COUNTRY,
                r1.R01_DATE_TIME_CAPTURED  RA1_R01_DATE_TIME_CAPTURED
                FROM
                R1_TABLE r1
                WHERE 
                r1.R01_ID IN (Select column_value from table(R01_IDS))
        );

        --insert R2_TABLE tables values which matches with the above criteria into the R2_TABLE_ARCHIVED table
        INSERT ALL 
        INTO R2_TABLE_ARCHIVED(R02_ID,R02_R01_ID_FK,R02_fname,R02_SURNAME,R02_CONTACT_NUMBER,R02_DATE_TIME_CAPTURED)
        VALUES(RA2_R02_ID,RA2_R02_R01_ID_FK,RA2_R02_fname,RA2_R02_SURNAME,RA2_R02_CONTACT_NUMBER,RA2_R02_DATE_TIME_CAPTURED)
        SELECT  RA2_R02_ID,RA2_R02_R01_ID_FK,RA2_R02_fname,RA2_R02_SURNAME,RA2_R02_CONTACT_NUMBER,RA2_R02_DATE_TIME_CAPTURED
        FROM (
                SELECT
                r2.R02_ID  RA2_R02_ID,
                r2.R02_R01_ID_FK  RA2_R02_R01_ID_FK,
                r2.R02_fname  RA2_R02_fname,
                r2.R02_SURNAME  RA2_R02_SURNAME,
                r2.R02_CONTACT_NUMBER  RA2_R02_CONTACT_NUMBER,
                r2.R02_DATE_TIME_CAPTURED  RA2_R02_DATE_TIME_CAPTURED
                FROM
                R2_TABLE r2
                WHERE 
                r2.R02_R01_ID_FK IN (Select column_value from table(R01_IDS)));     


        --All the delete queries to remove the above copied values from the parent tables respectively  
        DELETE FROM R1_TABLE WHERE R01_ID IN (Select column_value from table(R01_IDS));
        DELETE FROM R2_TABLE WHERE R02_R01_ID_FK IN (Select column_value from table(R01_IDS));
        DELETE FROM R5_TABLE WHERE R05_R01_ID_FK IN (Select column_value from table(R05_IDS));
        DELETE FROM R6_TABLE WHERE R06_R05_ID_FK IN (R05_IDS);      


    end loop;           
    close r5_cur;
    COMMIT;
END;
/

不要忘记向下滚动到 END LOOP 并关闭光标!