Всем добра!

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

Задача:
Должна быть реализована универсальная процедура, работающая с любыми парами таблиц, если эти таблицы отвечают определенным требованиям.
назовем их таблица А и таблица В.
Структура у них одинаковая.
Таблица В может быть любого, даже самого гигантского размера.
Таблица А обычно не больше миллиона строк, но изредка может быть и 10 миллионов. В будущем системы запланирован рост числа данных.
В промежуточной таблице процедура сохраняет (на время сессии) rowid строк обеих таблиц, которые будут затронуты изменением.
В процедуре динамически формируется запрос, который формирует на основе данных обеих таблиц новые правильные строки для таблицы В. Часть из них пойдет как новые, а часть уже присутствует в В в неправильном виде. В том числе неправильным может считаться и одно из ключевых полей.

Т.о. можно удалить из таблицы B все связанные строки и вставить туда результат запроса.
Точнее, сначала вставить новые, т.к. в запросе используются данные B, а потом удалить старые уже не нужные.
Тут возникает препятствие в виде уникального индекса, который не дает вставить часть новых строк из-за пересечения по ключу.
И появляется два принципиальных варианта:
1. использовать merge по B.rowid, при котором для части строк (заранее неизвестно, для части какого/соотношения) будет апдейт с вероятным изменением одного из полей уникального ключа.
2. положить результат запроса в промежуточную структуру, удалить по rowid все связанные строки из В, вставить в В из промежуточной структуры.

Лично я склоняюсь к варианту 2.
но тут опять возникают варианты:
2.1. создать и в дальнейшем использовать некую таблицу С, повторяющую В, но хранящую только результат запроса. Может быть, temporary table. Возражение - Еще одна постоянная структура в базе, когда и так есть таблицы А, созданные специально для этой цели.
2.2. в процессе работы процедуры создавать таблицу С, а потом дропать ее за собой.
2.3. динамически создавать коллекцию, вставлять туда данные, потом извлекать через forall или table().

Эти три варианта выглядят несколько безумно =)
поэтому я начинаю склоняться к первоначальному варианту 1.
или 2.2., потому что он наиболее прост с точки зрения понимания работы (когда я открою эту процедуру через год и начну читать комментарии).

В общем, как вы считаете, что лучше выбрать? Насколько merge лучше или хуже, чем insert+delete+insert?

у меня, видимо, не получилось нормально описать.
если будет merge, он будет соединять целевую таблицу и то, что в using, по rowid, поэтому проблемы с обновлением ключа как таковой нет, все выполнится.

с exchange partition хорошая идея, но она является развитием варианта "создать таблицу, использовать, дропнуть".
а я как раз и не могу понять, насколько эта идея плоха.
какие могут быть подводные камни в таком подходе?
кроме одновременного запуска по одной и той же паре таблиц (это решаемо)

я не храню rowid на постоянной основе
авторВ промежуточной таблице процедура сохраняет ( на время сессии ) rowid строк обеих таблиц

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

ну это DWH =))
во время загрузки такого не делают по определению)))

в смысле, move'ов не делают. а одновременного наката изменений на таблицу тоже не будет, уже по ограничениям архитектуры