Я столкнулся с интересной и неожиданной проблемой при обработке записей в Oracle (11g) с помощью BULK COLLECT.
Следующий код работал отлично, обрабатывая более миллиона записей без проблем:
-- Define cursor
cursor My_Data_Cur Is
Select col1
,col2
from My_Table_1;
…
-- Open the cursor
open My_Data_Cur;
-- Loop through all the records in the cursor
loop
-- Read the first group of records
fetch My_Data_Cur
bulk collect into My_Data_Rec
limit 100;
-- Exit when there are no more records to process
Exit when My_Data_Rec.count = 0;
-- Loop through the records in the group
for idx in 1 .. My_Data_Rec.count
loop
… do work here to populate a records to be inserted into My_Table_2 …
end loop;
-- Insert the records into the second table
forall idx in 1 .. My_Data_Rec.count
insert into My_Table_2…;
-- Delete the records just processed from the source table
forall idx in 1 .. My_Data_Rec.count
delete from My_Table_1 …;
commit;
end loop;
Поскольку в конце обработки каждой группы из 100 записей (ограничение 100) мы удаляем только что прочитанные и обработанные записи, я подумал, что было бы неплохо добавить синтаксис «для обновления» в определение курсора, чтобы другой процесс мог не обновлять никакие записи между временем чтения данных и временем удаления записи.
Итак, единственное, что я изменил в коде, это…
cursor My_Data_Cur
is
select col1
,col2
from My_Table_1
for update;
Когда я запускал пакет PL/SQL после этого изменения, задание обрабатывает только 100 записей, а затем завершается. Я подтвердил, что это изменение вызывает проблему, удалив «для обновления» из курсора, и пакет снова обработал все записи из исходной таблицы.
Есть идеи, почему добавление пункта «для обновления» может привести к такому изменению поведения? Любые предложения о том, как обойти эту проблему? Я собираюсь попробовать запустить эксклюзивную транзакцию для таблицы в начале процесса, но это не идеальное решение, потому что я действительно не хочу блокировать всю таблицу, обрабатывающую данные.
Заранее спасибо за вашу помощь,
Грант