цикл for на большое количество записей

Есть 2 скрипта с абсолютно одинаковой логикой

for cur in (select * from t1 where a1 is not null order by id) --выборка выдаёт 400к записей
loop
    --здесь код на select, update, delete с if-else на таблицу t3;
    --с коммитами на каждую тысячу записей;
end loop;

и

for cur in (select * from t2 where a2 is not null order by id) --выборка выдаёт 4kк записей
loop
    --здесь код на select, update, delete с if-else  на таблицу t3;
    --с коммитами на каждую тысячу записей;
end loop;

1й скрипт выполнился за 30мин. Запустил 2й скрипт, он за сутки обработал только 400к записей. Таблица t2 раз так в 10 больше t1.
Может ли для оракла составлять серьёзную проблему, что я во 2м скрипте выбрал 4кк а не 400к? или же всё-таки проблема в коде внутри цикла...

v04odp,

давай полный код, со структурой таблиц, триггерами, индексами, планами выполнения на select, update и insert

v04odp,

4кк это сколько?

сколько времени выполняется select * from t2 where a2 is not null order by id?

Заметь, что первая итерация цикла начнется только после завершения сортировки в запросе из t2. Так ли она тут нужна?

сними трассировку, посмотри критичные запросы. может и сам select из t2 тормозить (например order by id ушел в TEMP), может код в теле цикла.

v04odp
Может ли для оракла составлять серьёзную проблему...

Серьезная проблема для оракла это микрософты, постгресы и пр. Ламеры для него не проблема.

1. Навскидку просто возьми такое же кол-во записей и посмотри разницу )
2. Вполне возможно, что делается апдейт t2 (отмечаются обработанные записи), в таком раскладе лучше имхо,
записи для обработки выбирать небольшими партиями по индексированному полю, а не сразу все по where a2 is not null,
т.е.

for cur in (select * from t2 
               where a2 is not null
                  and rownum < 2)
loop
   for cur in (select * from t2 
                  where a2 is not null
                     and rownum < 1001)
  loop
      --здесь код на select, update, delete с if-else  на таблицу t3;
      --с коммитами на каждую тысячу записей;
  end loop;
  commit;
end loop;

Правда встает вопрос с сортировкой, если только с ней, то надо думать )
Исхожу из некогда витавшей в воздухе гипотезы о том, что если во время селекта выбираемые данные были изменены, оракл доходя до измененных данных должен вроде как, брать их из отката и вплоть до того, что может типа начать выполнять запрос заново )
(может и не прав, легенду не проверял :), тем более касаемо итераций в курсоре). Если кто прояснит по пути, буду рад )

Двойной loop совсем тупо конечно ) - просто в цикле выставляем флаг поймались ли записи, если нет, то берем следующую тысячу )

автор
давай полный код, со структурой таблиц, триггерами, индексами, планами выполнения на select, update и insert

таблица t3 состоит из 6 столбцов, на каждый из них есть индекс. триггеров на ней нет. планы хорошие cost 4-6;
код внутри цикла примерно такой

loop
  select items_count, rowid into n, rid from t3 where col1 = 'asd' and col2 = 'qwe' and col3 = 'gfd' and col4 = 'sert';
  if n > 0 then 
    update t3 set items_count := items_count - 1 where rowid = rid;
  else
    delete from t3 where rowid = rid;
  end if;
  k := k + 1;
    if k = 1000 then
        insert into tmp values(c.id);
	commit;
	k := 0;
    end if;
end loop;

4кк - это 4 миллиона.

автор
Заметь, что первая итерация цикла начнется только после завершения сортировки в запросе из t2. Так ли она тут нужна?

да я заметил это. первая запись в таблице tmp появилась минут через 15. сортировка нужна, чтобы в случае непредвиденной ситуации продолжить выполнение скрипта с последней обработанной записи.
Пойду попробую во 2м скрипте выбрать только 400к записей и посмотрим что будет :)

v04odp
сортировка нужна, чтобы в случае непредвиденной ситуации продолжить выполнение скрипта с последней обработанной записи

Если только для этого, то она не нужна )

Можно например так:
1. поле-флаг, в котором отмечаем, что запись обработана
2. фетчим в коллекцию по те же 1000 строк записей по флагу из п.1 еще не обработанные записи
3. форалл коллекции: сначала делаем то, что нам нужно (код внутри цикла), потом обновляем записи в т2 как обработанные
4. повторяем п.2-3 пока п.2. не перестанет подбирать записи )

автор
1. поле-флаг, в котором отмечаем, что запись обработана

была такая мысль в самом начале. это надо добавить столбец в таблицу, а потом его дропнуть после окончания, чтоб не мусорил таблицу. я почитал форумы на дроп столбца, и что-то побоялся такое делать :)

автор
что если во время селекта выбираемые данные были изменены

меня сейчас тоже очень интересует этот вопрос. Изначально я предполагал, что скрипт выполнится быстро, и поэтому запретил изменения таблиц. но видимо теперь придётся переделать скрипт так, чтоб данные обновлялись.