forall и bulk collect into

← →
Petr V. Abramov © (2008-11-20 02:07) [0]

Кто более-менее уверенно разбирается в Oracle, просьба выручить ссылками на металинковские ноты и пр. источники.
Итак: есть такая фича bulk binding, в pl/sql синтаксически она выражается как forall statement.
есть такая фича returning clause в insert-update-delete.
в принципе если в forall у insert-update-delete поставить returning ... bulk collect into, по все будет работать как часы, это официально документировано, хоть и довольно вскользь.
Вопрос: практика показывает, что если сделать, допучтим, так:
forall i in mycoll.first .. mycoll.last delete from t1 where id = mycoll(i) returning id bulk collect into mycoll2
то mycoll и mycoll2 будут совпадать один в один, с точностью до порядка.
Документировано ли это где-то официально?

← →
Petr V. Abramov © (2008-11-20 02:07) [1]

если ув. модераторы перенесут в "Базы", я не обижусь :)

← →
ANB (2008-11-20 10:32) [2]

1. По сабжу - ну работает и ладно. :) Вон в очередном патче 9-ки навернули connect by. Хоть все и документировано.
2. Пробовали так удалять. Чет сильно не ускорилось по сравнению с удалением по одной (одним оператором не получалось - кончался сегмент отката). Быстрее insert truncate insert. Проверяли на удалении примерно четвертой части средней по размеру таблицы (около миллиарда записей).

← →
Кщд (2008-11-20 10:52) [3]

>то mycoll и mycoll2 будут совпадать один в один, с точностью до порядка.
>Документировано ли это где-то официально?
в чем, собственно, затруднения?
в том, что хотелось бы иметь в mycoll2 результат последнего удаления?
в том, что данные попадают в mycoll2 даже если причинный ID не существует в таблице?
или?...
к тому же, не указана версия базы, равно как и тип mycoll, mycoll2.
остается только гадать на кофейной гуще.

← →
Petr V. Abramov © (2008-11-20 11:04) [4]

> в том, что хотелось бы иметь в mycoll2 результат последнего
> удаления?

ну это мы имеем по-любому.
хоелось бы так: имеем коллекцию ID-шников, потом по ним update`им, returning неключевое поле, потом по комбинации ID и вернутого неключевого поля еще че-то делаем. Ессно, никто не мешает вернуть ID+неключевое поле (что и делается), но PGA жалко.

> к тому же, не указана версия базы, равно как и тип mycoll,
> mycoll2.

10.2.0.2.
тип mycoll не важен теоретически

> 2. Пробовали так удалять. Чет сильно не ускорилось по сравнению
> с удалением по одной (одним оператором не получалось - кончался
> сегмент отката).

че-то мы о разном немного. forall - это по определению один оператор, не цикл

← →
Petr V. Abramov © (2008-11-20 11:13) [5]

ладно, будем считать, что от желания спать вопрос был.
в общем случае устанавливать соответсвие между входной и выходной коллекциями - дурное дело, для одного входного параметра на выходе может быть 100 значений, для друго - ноль, для третьего exception.

← →
Кщд (2008-11-20 11:14) [6]

>хоелось бы так: имеем коллекцию ID-шников, потом по ним update`им, returning >неключевое поле, потом по комбинации ID и вернутого неключевого поля еще >че-то делаем. Ессно, никто не мешает вернуть ID+неключевое поле (что и >делается), но PGA жалко.
не могли бы привести пример, наглядно иллюстрирующий последовательность действий Вашего алгоритма и с комментарием, что конкретно не устраивает?

>10.2.0.2.
оффтоп, но надо бы пропатчиться до 10.2.0.4

>тип mycoll не важен теоретически
между типом уровня SQL, table of, table of index by, varray разница есть
и далеко не только теоретическая)
в общем, если у Вас есть рабочий тест-кейс - было бы отлично

← →
Petr V. Abramov © (2008-11-20 11:17) [7]

> оффтоп, но надо бы пропатчиться до 10.2.0.4

не надо

> между типом уровня SQL, table of, table of index by, varray
> разница есть

интересует случай table of и index by

testcase нарисую позже

← →
Кщд (2008-11-20 11:19) [8]

>Быстрее insert truncate insert. Проверяли на удалении примерно четвертой части >средней по размеру таблицы (около миллиарда записей).
это разовая задача или у Вас ddl прошит в коде?
в последнем случае, это выглядит как workaround неумелого администрирования путем программирования - т.е. несколько странно.

← →
Игорь Шевченко © (2008-11-20 11:25) [9]

> то mycoll и mycoll2 будут совпадать один в один, с точностью
> до порядка.

Не обязательно будут совпадать

CREATE TABLE foo ( id NUMBER NOT NULL, bar VARCHAR2(8) ) / INSERT INTO foo VALUES (1, "One") / INSERT INTO foo VALUES (3, "Three") / INSERT INTO foo VALUES (4, "Four") / COMMIT / CREATE OR REPLACE PROCEDURE foo_delete IS TYPE FooTab IS TABLE OF foo.id%TYPE INDEX BY BINARY_INTEGER; InTab FooTab; OutTab FooTab; BEGIN InTab(0) := 1; InTab(1) := 2; InTab(2) := 3; InTab(3) := 4; FORALL i IN InTab.First .. InTab.Last DELETE FROM foo WHERE id = InTab(i) RETURNING id BULK COLLECT INTO OutTab; dbms_output.put_line ("output count = "|| OutTab.Count); FOR i IN OutTab.First .. OutTab.Last LOOP dbms_output.put_line ("outtab("||i||") = "||OutTab(i)); END LOOP; ROLLBACK; END; /

SQL> exec foo_delete; output count = 3 outtab(1) = 1 outtab(2) = 3 outtab(3) = 4 PL/SQL procedure successfully completed.

← →
Petr V. Abramov © (2008-11-20 11:33) [10]

> Игорь Шевченко © (20.11.08 11:25) [9]

да понятно, в [5] признался :)
просто на шару хотелось одну весчь сделать :)

← →
ANB (2008-11-20 14:42) [11]

> че-то мы о разном немного. forall - это по определению один
> оператор, не цикл

Ну так мы и пытались менять цикл с периодическими коммитами на несколько forall. Чуток быстрее, но не кардинально.

← →
Petr V. Abramov © (2008-11-20 15:12) [12]

> ANB (20.11.08 14:42) [11]

что-то сильно неладно у вас, в мирной жизни forall запросто может на порядок ускорение дать, ну уж в несколько раз точно.
выходит, в какой-то момент начинается неимоверное торможение самого делита.

> Ну так мы и пытались менять цикл с периодическими коммитами
> на несколько forall.

а коммиты после forall ставили? :)

← →
ANB (2008-11-24 18:02) [14]

> а коммиты после forall ставили? :)

Ну ясен пень. Из-за них все и затеяли. Никаких порядков не получилось.
Сами делиты медленные. truncate рулит :)

> Из-за них все и затеяли. Никаких порядков не получилось.

значит, сам delete настолько тормозит порядка порядков :)
индексы? блокировки других транзакций?

← →
ANB (2008-11-25 10:15) [16]

> индексы? блокировки других транзакций?

Блокировок нету.

А вот индексы и триггера были. Не срубали, т.к. хотели аккуратно прогнать удаление лишнего на работающе базе.

Но срубание не сильно ускоряет (где то еще раз в 5).
Все равно 2 инсерта намного шустрее. Правда это уже монопольно гоняли.

> ANB (25.11.08 10:15) [16]

> Все равно 2 инсерта намного шустрее.

ессно

forall и bulk collect into Найти похожие ветки