Стоит следущая задача (не передо мной, но нас коснется) :
Имеется таблица (проводки) в которой чуток больше миллиарда записей.
Нужно :
1) Скопировать данные в хранилище данных
2) Ежедневно поддерживать хранилище в актуальном состоянии
3) Не притормозить ОЛТП систему
Дельта :
в обычные дни в таблицу добавляется несколько сотен тысяч записей. В особые дни - несколько миллионов, в конце месяца - порядка 30-40 миллионов.

Вопрос : у кого нибудь есть опыт, как реализовать данную задачу ?
Своих идей хватает, но т.к. опыта работы с DWH нету - все варианты стремно применять из-за условия 3).

Примерно на таких же условиях у нас каждую ночь данные джобами переносятся из биллинга в ОЛАП-кубы. Правда на МССКЛ, но сути, думаю, это не меняет.
Главное, ИМХО, не затягивать с началом копирования - как только ОЛТП активность кончилась, так и начинай.

> Sergey13 ©   (23.12.09 09:46) [1]

биллинг проще - там нету апдейтов за старые даты.
Собственно, механизм ненапрягающего выделения дельты придумать не может.

> [2] ANB   (23.12.09 09:48)

Отдельное поле с датой последней модификации записи не подходит?

> Отдельное поле с датой последней модификации записи не подходит?

Да вот думаем, но его заполнять неоткуда (все места в коде не поправишь) - это придется триггер вешать.
Сидим чешем репу - замедлит или нет потом массовые процедуры.

> [4] ANB   (23.12.09 10:08)
> Сидим чешем репу - замедлит или нет потом массовые процедуры.

А с чего бы ему замедлять? Он же никуда не полезет - просто возьмет текущую системную дату. И уникальность (как источник тормозов) не надо поддерживать.

> Sergey13 ©   (23.12.09 10:16) [5]

Идея катит. На удаление повесим триггер - пусть складывает логи, это редко бывает и плевать на скорость.
Остается вопрос с местом решить.

> [6] ANB   (23.12.09 11:12)
> На удаление повесим триггер - пусть складывает логи

А у вас физически проводка удаляется? Вроде как неправильно это.

> А у вас физически проводка удаляется? Вроде как неправильно
> это.

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

> Отдельное поле с датой последней модификации записи не подходит?
> Да вот думаем, но его заполнять неоткуда (все места в коде не поправишь) - это придется триггер вешать.
timestamp ?

блин, не обратил внимания на оракл, сбило
> Правда на МССКЛ,

> timestamp ?

Ага. У оракла есть подобие. Только заполнять при апдейте придется триггером. Собственно, на всех практически нужных для репликации таблицах у нас стандартный триггер висит, так что тормозов лишних не будет.
Вопрос стоит только с местом под поле и индекс на него.

> [11] ANB   (23.12.09 12:33)

Возможно стОит подумать об организации отдельной IOT таблицы, с ID-шником основной и временной меткой. Ее можно будет периодически чистить от старья.

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

но в общем то ладно, все одно не пойдет, из-за  платформы.

materialized views ? оно само умеет "актуальность" поддерживать, AFAIK

> Возможно стОит подумать об организации отдельной IOT таблицы,
>  с ID-шником основной и временной меткой. Ее можно будет
> периодически чистить от старья.

30 миллионов записей джойнить нестед лупсом стремно потом.


> materialized views ? оно само умеет "актуальность" поддерживать,
>  AFAIK

Пробовали. ОЛТП система легла. Ведение снапшот логов затормозило массовые процедуры раза в 3.

Тут с тамйштампом проблемка рисуется.

Имеем :
1) последний раз выгружали 00:00:00.0000, сейчас 01:00:00.0000.
В 00:59:59.9999 легла инсертом запись. Таймштамп триггером выставился, есно 00:59:59.9999. Коммита не было. Стартует джоб, выгребает все записи
с 00:00:00.0001 по 01:00:00.0000. Запись 00:59:59.9999 он не видит (не закоммичена).
2) сейчас 02:00:00.0000. Стартует джоб. Запись 00:59:59.9999 закоммитилась в 01:00:01.0000. Но в новой итерации джоб опять ее не видит, т.к. джоб смотрит с 01:00:00.0001 по 02:00:00.0000.
Проводка потерялась.

Чего тут можно придумать ?

> [15] ANB   (23.12.09 15:27)
> 30 миллионов записей джойнить нестед лупсом стремно потом.

Зато получается почти автономная система, которую можно легко выключить если попытка будет неудачной.

> Чего тут можно придумать ?

Еще одно поле-признак архивирована/нет. Может тогда и дату время не надо писать.

> Пробовали. ОЛТП система легла. Ведение снапшот логов затормозило
> массовые процедуры раза в 3.


Что вы пробовали ?

Это читал наизусть ?
http://download.oracle.com/docs/cd/B14117_01/server.101/b10736/basicmv.htm#i1007299
http://download.oracle.com/docs/cd/B14117_01/server.101/b10736/advmv.htm#i1007694

> Еще одно поле-признак архивирована/нет. Может тогда и дату
> время не надо писать.

RBS не хватит столько проапдейтить.


> Зато получается почти автономная система, которую можно
> легко выключить если попытка будет неудачной.

Выкачка может затянуться на пару часиков. А табличка не одна.


> Это читал наизусть ?
> http://download.oracle.com/docs/cd/B14117_01/server.101/b10736/basicmv.
> htm#i1007299
> http://download.oracle.com/docs/cd/B14117_01/server.101/b10736/advmv.
> htm#i1007694

Читал и даже делал. Игорь, не держи нас за полных лохов.
С материализованных представлений и начали. Вот только мой прошлый опыт работы с ними показал, что не такая уж это и клевая штука.
Она хорошо работает в фулл режиме, но у нас нет столько времени и ресурсов на ежедневную полную перекачку.
А фаст режим достаточно капризный и, как показал эксперимент, включение логов торомозит ОЛТП систему, что недопустимо.

ANB   (23.12.09 16:11) [18]

Видишь ли, проблема перекачки данных из OLTP в DWH появилась не у тебя первого. Из большого OLTP в большой DWH тоже. Потому я склонен все-таки доверять Oracle, который на этих процессах не одну собаку съел.
У меня сейчас под рукой нет ни одной книги по DWH в Oracle, я б процитировал насчет materialized views.

Martin Rennhackkamp в журнале DBMS много писал как про организацию DWH, так и про его загрузку, причем было это лет 8 назад. Я к чему - я к тому, что с тех пор многое из описанного взято на вооружение производителями СУБД :)

> [18] ANB   (23.12.09 16:11)
> RBS не хватит столько проапдейтить.

Никто и не заставляет всю кучу сразу тянуть. Тяни пачками.

> У меня сейчас под рукой нет ни одной книги по DWH в Oracle,
>  я б процитировал насчет materialized views.

Игорь, материализованные представления мы первым делом опробовали. Может мы их неправильно готовили, конечно.
Делали :
1) Прицепили на одну таблицу снап шот лог (всего их надо несколько десятков реплицировать)
2) Создали мат.представление с фаст обновлением раз в сутки
Запустили массовую процедуру. Причем обновление в этот момент не работало. Массовая процедура отработала раза в 3 медленнее. Да и вся система стала притормаживать. Снесли логи - система задышала.

> Никто и не заставляет всю кучу сразу тянуть. Тяни пачками.

апдейт 30 миллионов записей что пачками что разом - не быстро это. Плюс тянульщиков уже минимум 2 образовалось.

Можно пофлудить? Просто интересна область применения такого огромного количества данных? Сори за офтоп

> Можно пофлудить? Просто интересна область применения такого
> огромного количества данных? Сори за офтоп

Маленький скромный банк.

чем плохи Oracle Streams, BPEL?

> [22] ANB   (23.12.09 17:25)
> апдейт 30 миллионов записей что пачками что разом - не быстро это.

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

> Возможно стОит подумать об организации отдельной IOT таблицы,
>  с ID-шником основной и временной меткой. Ее можно будет
> периодически чистить от старья.

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

> чем плохи Oracle Streams, BPEL?

А чем хороши ? У них другой принцип, по сравнению с мат.вьюхами ?
У меня почему то сложилось стойкое предубеждение к новомодным фичам оракла.
Испытывались :
1. Репликация односторонняя
2. Адвансед репликация (двухсторонняя)
3. Очереди
От всех этих фичей осталось самое хреновое впечатление. Тормозят безбожно, много ограничений, писать под них много и неудобно.
И при любом раскладе требуют толстого и надежного канала связи.


> Ну во первых не так уж и много.

Для инсерта - да. Для апдейта - достаточно много.

ANB   (24.12.09 09:25) [28]
Streams - фича довольно-таки "старая"
в 10-ке Ваши пп.1-3 отлично работают
"тормозов" не замечено
удобство написания: три основных пакета с основными интерфейсами - capture, propagation, apply - ничего сложного
канал связи - действительно, желателен стабильный, но если связь прервется - ничего страшного, т.к. при возобновлении соединения данные польются ровно с того места(SCN), на котором связь пропала

> [28] ANB   (24.12.09 09:25)
> Для инсерта - да. Для апдейта - достаточно много.

Помнится в институте слышал - критерием истины является практика. Наколбасить 30 лимонов записей да проапдейтить.

Кстати можно и без апдейта и без всяких доп полей.
В отдельной таблице хранятся просто ид-шники измененных/новых и НЕзаархивированных записей. При перетаскивании/архивации из нее соответственно удалять.

> В отдельной таблице хранятся просто ид-шники измененных/новых
> и НЕзаархивированных записей. При перетаскивании/архивации
> из нее соответственно удалять.

Примерно так и работает фаст мат.вьюха на снапшот логах. Тормоза начинаются.


> "тормозов" не замечено

На каких обьемах ?

> Наколбасить 30 лимонов записей да проапдейтить.

Колбасил - апдейт висит долго.

> [32] ANB   (24.12.09 10:52)
> Тормоза начинаются.

На чем тормоза? На перекачке? Так они ИМХО по любому будут на таких объемах. Потому и надо их на ночь переносить - пусть подтормаживает. Лишь бы за ночь отрабатывало.

> Потому и надо их на ночь переносить - пусть подтормаживает.
>  Лишь бы за ночь отрабатывало.

Нужно каждый час запускать.
На перекачке тормозов не будет - минут 5-10. Висеть будет апдейт. Более того - он будет тормозить ОЛТП базу.

Кстати, покопал стрим - вроде ничего по идее. Архивные логи пишуться по любому.
У нас уже отрабатывается такой вариант. Только места под него пока нет - придется еще один дисковый массив покупать, а он дорогой зараза.
Сейчас архив логи раз в сутки у нас чистяться.

> [35] ANB   (24.12.09 12:02)
> Висеть будет апдейт.

Можно же без апдейта. На отдельную таблицу ссылок не будет - удалить должно быстро. Да и будет она при таком подходе небольшая.

> ANB   (24.12.09 10:52) [32]
> > "тормозов" не замечено
> На каких обьемах ?

на сравнимых, т.е. порядка 10^6 записей в день
впрочем, объемы не важны, т.к. репликация идет по мере поступления, а 10-20 записей в секунду - не тот объем, который повесит Oracle)

> Можно же без апдейта. На отдельную таблицу ссылок не будет
> - удалить должно быстро.

Таблицу можно вообще транкейтить.
С обработкой отдельной таблицы проблем особых нет, но :
1) Будет момент, когда в ней будет 30 миллионов записей (джойн с основной таблицей повиснет)
2) Можно не джойнить, а сразу складывать все поля, но ведение лога сильно затормозит массовые процессы.
Короче, будем смотреть в сторону стрима, если не придумаем ничего толкового с таймштампом.
Но, милин, начальство нас убъет :)
Там не один лям баксов надо будет вложить.

> не тот объем, который повесит Oracle)

По стриму, как я понял, самое полезное, что ОЛТП база вообще не затрагивается. Все на архив.логах сделано. А тормоза DWH нас волнуют слабо. Не наша проблема.

>ANB   (24.12.09 14:30) [39]
>По стриму, как я понял, самое полезное, что ОЛТП база вообще не >затрагивается.
именно так, а процесс, который шерстит архивлоги со скоростью 10-20 строк в секунду сколько-нибудь существенного вклада в утилизацию CPU и I/O не вносит

> который шерстит архивлоги со скоростью 10-20 строк в секунду
> сколько-нибудь существенного вклада в утилизацию CPU и I/O
> не вносит

Процессора не сильно жалко - у нас их 32, а И/О - на отдельный массив архив лог и проблема решена.
Только деньги придется трясти на новый массив.

Попробовали стрим. Лежит он. :(
Что еще можно посмотреть ?

>ANB   (30.12.09 15:56) [42]
>Попробовали стрим. Лежит он. :(
что значит "лежит"?)
у нас было свыше ста интерфейсов интеграции одновременно работавших на базе streams по табличкам, сравнимым с таблицами банковских проводок, - проблем не было
так что - пусть dba и админы докручивают сервак, т.к. затык явно не в технологии streams...

>Что еще можно посмотреть ?
посмотреть можно BPEL, но это заведомо медленнее streams и плюс денежные затраты
из преимуществ - гибкость и простота настройки самих процессов переноса данных

можно, конечно, изобретать и собственный велосипед
а если заказчик за это платит - так, даже, нужно)

> а если заказчик за это платит - так, даже, нужно)

На окладе я. И заказчик - мой начальник отдела. :(
Ищем вместе решение. Хорошо, хоть еще пару месяцев это не наша проблема, а другого отдела.


> что значит "лежит"?)

Коллеги попробовали погонять стрим на тесте. Им нужно было выкачать около 30 табличек. Сутки почти выкачивалась дельта за день. Долго читает логи. Хотя надо бы самому еще глянуть - могли и написать криво.
К дба ходил - те вообще говорят, что хранить долго логи им негде, нужно для репликаций покупать еще один дисковый массив, а это довольно дорого. Впрочем, если припрет - купят.