Перемещение огромного количества данных кусками
Я работаю над сценарием, в котором необходимо запрашивать огромное количество данных на основе определенных условий и перемещать их в некоторые архивные таблицы соответственно. У меня есть более пятидесяти миллионов записей для сканирования и выбора соответствующих записей для выполнения операций INSERT для шести архивных таблиц. Приведенный ниже скрипт прекрасно работает для примерно полумиллиона записей, но выдает приведенное ниже исключение при запуске для миллионов записей.
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;
2 ответа
Решение
Коллекции (и другие конструкции PL/SQL) хранятся в памяти сеанса. (В отличие от запрашиваемых данных, которые хранятся в глобальной памяти). Поскольку память сеанса выделяется для каждого пользователя, должен быть предел, потому что оперативная память все еще является относительно дорогим ресурсом.
Итак, вы получаете эту ошибку...
ORA-04036: PGA memory used by the instance exceeds PGA_ AGGREGATE _LIMIT
... потому что ваша сессия собрала всю память, выделенную PGA (пул памяти, доступный для сессий).
Проблема в том, что вы пытаетесь заполнить коллекцию миллионами строк. Хотя этот ряд очень узкий, он все еще не включен. К счастью, PL/SQL имеет решение: это предложение LIMIT.
С помощью LIMIT мы можем заполнить коллекцию фрагментом результирующего набора, обработать его и получить следующий фрагмент. Там не так много, чтобы изменить:
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;
/
Не забудьте прокрутить вниз для КОНЕЦ ПЕТЛИ и закрыть курсор!
Табличное пространство является отображением физического хранилища. Обходного пути нет, кроме как попросить вашего администратора базы данных добавить еще один файл данных.