Доброе время суток!

Есть около 15 промежуточных таблиц, в которые другая система часто (раз в несколько секунд) инсертит данные.
Общее количество вставляемых строк за день по некоторым таблицам может достигать 100-200 миллионов.

Мне же нужно эти строки как можно быстрее учесть в своих целевых таблицах (произвести инсерты или апдейты).
Новая строка в некоторых промежуточных таблицах приводит к тому, что приходится добавлять или изменять строки в НЕСКОЛЬКИХ моих целевых таблицах. Чтобы понять, как учесть строки в промежуточных таблицах и определить ключи справочных элементов моей системы я выполняю select'ы в которых эти таблицы соединяется (join) с другими таблицами моей системы.

Вот мой подход к решению данной задачи и проблемы с которыми я столкнулся.

1. Создана процедура перекладки данных из промежуточных таблиц в мои целевые таблицы.

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

3. Select'ы в процедуре перекладки, про которые я писал выше, часто выполняются неоптимальными путями.
Это происходит из-за того, что статистика по таблицам не актуальна.
Можно конечно перед (и во время) каждого запуска моей процедуры выполнять:


по всем требуемым таблицам и по нужным партициям моих целевых таблиц, но данное действие выполняется продолжительное время, да еще и коммитит транзакцию, что также мне мешает.
Т.е., например, в статистике таблицы м.б. указано, что там 100 строк, а в реальности 10 миллионов, поэтому select из-за неоптимального плана выполнения запрос может выполняться длительное время.
Данную проблему я частично решил прописыванием хинтов в запросах, а также сбором статистики по условиям, т.е. например, если добавилось менее 50 тысяч записей в таблицу, то я не выполняю расчет статистики по ней, а если более то выполняю.
В некоторых случаях я устанавливаю статистику методом
для экономии времени.

4. После того как я обработал строки в промежуточных таблицах мне нужно как-то их пометить или удалить, чтобы не учитывать при следующих запусках процедуры загрузки.
Было решено удалять.
Для этого создано несколько временных таблиц с одним столбцом для хранения ROWID обработанных строк промежуточных таблиц.
В начале загрузки я произвожу инсерт ROWID всех строк промежуточных таблиц во временные таблицы, а после их обработки, удаляю по ROWID.
Минус этого в том, что DELETE очень тормозная операция и выполняется очень продолжительное время.
Можно конечно не удалять записи, а добавить новое поле и делать в нем отметку для обработанных записей, но апдейт тоже не быстрая операция и под конец дня мне придется просматривать таблицы с сотней миллионов строк, что тоже скажется на общем времени выполнения загрузки.
Можно конечно применить альтернативное удаление, например, с созданием промежуточной таблицы:


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

Буду очень благодарен, если прокомментируете мое решение или поделитесь своим опытом решения подобных задач.

EvgeniaMakarova,

Система, которая мне присылает данные, является системой реального времени.

Моя же система является хранилищем данных, но также к ней предъявлено требование оперативного отображения присылаемых данных (допустима задержка в несколько минут).

EvgeniaMakarova,

Спасибо за помощь.

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

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

Dimitry Sibiryakov,

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