Кода под рукой нет, но выглядит примерно так
Есть:


На вью есть два инстиадоф-триггера на инсерт и апдейт.
То есть если при апдейте указана start_date, то по фильтру запрос идет сразу в нужную таблицу.

Теперь задача в table3 есть куча записей и их надо смержить в эти две таблицы через test_view.
Для записей у которых изменилось только field1, а id и start_date все быстро и замечательно.
А вот для записей, у которых изменилась start_date запросу приходится бегать по всем таблицам, чтобы найти нужный id.
Теперь считаем, что таблиц во вьюшке 20 и в каждой миллионы записей.
Получается очень долго в сравнении с тем, когда поиск идет еще и по start_date.

Cейчас алгоритм такой:
1. Сначала по for loop, ищем записи в test_view из table3 по id + start_date.
Апдейтим test_view, запоминая id в коллекцию.
2. Удаляем из темповой table3 записи с id, которые нашлись в п.1
3. Идем по for loop, ищем записи в test_view из table3 по id (самый долгий этап).
Апдейтим test_view, запоминая id в коллекцию
(при этом понятно, что т.к. start_date изменилась, то хоть и редко, но она может измениться настолько, что в инстеад-триггере запись будет перенесена в другую таблицу, чтобы соответствовать условиям вьюшки)
4. Удаляем из темповой table3 записи с id, которые нашлись в п.3
5.


Интересует возможно ли как-то оптимизировать п.3, именно медленный поиск по id записей, у которых изменилась data? Понимаю, что самым лучшим вариантом было бы разбить данные на части не по дате, которая иногда тоже может меняться, но по сути, кроме id не меняющихся полей нет. Можно было бы например по последним цифрам того же id разбить сразу на сто "патишенов" или по какому-нибудь модулю от id и т.д. но нужно и по дате тоже, а делать многоуровневое "патиционирвание" с помощью вьюшки...
В общем есть ли варианты кроме изменения структуры?
Возможно,
1. сделать один проход по темповой таблице в цикле (один loop)
и внутри:
1.1. апдейт по id + start_date
1.2. апдейт по id, если sql%rowcount = 0
1.3. insert если sql%rowcount = 0
Но это примерно то же самое.

kaldoreyЯ правильно понимаю, это некая имитация партиций?
Если при изменении даты триггер по какому-то правилу перемещает запись, то почему бы по этому же правилу не искать нужную таблицу, а не искать по всем?
Да. Некая имитация.
На данном примере:
1. Пришла некая запись у нее есть id и дата
2. Сначала пробуем обновиться по id + дата - здесь оракл, по условию во вью сам выбирает нужную таблицу по плану (FILTER по дате). И триггер тоже по дате апдейтит сразу нужную таблицу (о чем вы и пишете). Здесь все быстро.
3. Но допустим, что обновление в п.2 нам ничего не дало (sql%rowcount=0), т.е. не нашли запись через вьюшку по id + Дата, это значит, что или к нам пришла новая запись, или у id изменилась дата. И чтобы выяснить точно нам надо искать теперь только по id уже во всех таблицах вьюшки (дата нам теперь не известна), после того, как находим запись, переносим ее в другую таблицу (соответствующую по дате в условиях вьюшки). И вот этот поиск по id конечно очень не оптимальный момент.

Решение нашел. Но оно относится к специфике организации данных в конкретном случае,
поэтому описывать его здесь смысла нет к сожалению. Тему можно закрыть. Спасибо.

kaldoreyМожет у кого-то есть решение получше, но если без сильных изменений, то у меня мысль только держать индексную табличку с существующими ID и на предмет существования обращаться к ней, а при вставке использовать insert all
Если коротко, то таких структур/сущностей еще и несколько штук.
Они связаны между собой условно по fk.
И решение в том, чтобы вообще уйти от ситуации в п.3 путем постоянного поддержания актуальной даты, опираясь на ее изменение в основной (мастер или задающей) сущности. То есть при мердже основной, если у нее изменилась дата, перетаскиваем ее в нужную патицию и сразу перетаскиваем зависимые сущности в соответствующие их патиции, благо здесь дата и исходная таблица нам известна и времени это займет меньше. Тогда в дочерних сущностях от п.3 вообще можно отказаться и останутся только быстрые апдейт по id + дата и insert. Но в целом ...опа конечно.

EgoрМожно глянуть на ваши инстиадоф-триггера?
Самые самые обычные с параметризованным динскл, который по входящей дате инсертит(апдейтит) нужную таблицу вьюшки.

EgoрОбычно в триггере на апдейт есть два значения для каждого поля. старое и новое.
что мешает по старому значению id+start_date идентифицировать и таблицу и запись, которая апдейтится?
Все так. Но речь о другом немного.
Как делается апдейт? Правильно сначала оракл ищет запись, которую надо обновить, т.е. условно выполняет селект. Так вот когда мы делаем апдейт и у нас есть только id (даты нет точнее есть, но она изменилась и по ней запись уже не найти), то идет скан индекса всех таблиц вьюшки. На этом этапе еще даже не попали в триггер и у нас нет ни олд, ни нью.
Вопрос был в том, как оптимизировать этот момент (хотя сам понимаю, что практически никак). В итоге логику переделал так, что от этого пункта вообще избавился во всяком случае на дочерних таблицах, что ощутимо ускорило процедуру мерджа.

То есть допустим, есть мастер-запись с id + дата.
К нам пришли новые записи (и новая мастер-запись и новые дочерние записи).
И у этих записей дата уже измененная, т.е. при апдейте по новым id+дата в текущих данных мы записи не найдем. Поэтому сначала обрабатываем записи мастер-таблицы и для тех, у которых изменилась дата ищем только по id и меняем ее и во всех дочерних записях других таблицах (с возможным переносом в соответсвующие "патишены"), благо на данном этапе мы знаем и старую и новую дату и делаться это будет быстро. А когда уже будем мерджить данные дочерних таблиц, то к этому моменту дата в них уже будет актуализирована на предыдущем этапе при мердже мастер-таблицы. В общем очевидный вариант получается, не сразу только пришел к этому.