Блог

У меня есть относительно большая таблица (~ 100 миллионов записей), которая в основном представляет собой хранилище XML. Может быть несколько XML-документов с разными временными метками (с логикой, что последняя временная метка = самая последняя версия). Мы ожидаем ежемесячные пакеты обновленных данных, вероятно, с новыми версиями ~ 70% данных.

Мы планируем хранить в хранилище только самые последние 2-3 версии, поэтому я предполагаю, что наш текущий индекс b-дерева (идентификатор записи, временная метка) не обязательно самый быстрый? Простой запрос «выбрать * из таблицы, где временная метка >= гггг-мм-дд по идентификатору записи, отметка времени» вчера вечером занял 15 часов — довольно высокопроизводительный набор, и я не думаю, что кто-либо еще использовал БД в то время.

(re: сам запрос, в идеале я хочу выбрать только самый последний документ с меткой времени >= гггг-мм-дд, но на данный момент это не такая проблема).

Могу ли я каким-либо образом создать столбец автоматического уменьшения следующим образом:

 Record ID   Timestamp    Version   XML
1           2011-10-18   1         <...>
1           2011-10-11   2         <...>
1           2011-10-04   3         <...>
2           2011-10-18   1         <...>
2           2011-10-11   2         <...>
  

и т.д. и т.д. — т. е. При появлении новой версии самая последняя временная метка = версия 1, а все старые записи получают version = version 1. Таким образом, мои сценарии ведения домашнего хозяйства могут быть простыми «удалить, где версия > 3» (или что бы мы ни решили сохранить), и у меня может быть индекс b-дерева для идентификатора записи и двоичный индекс для версии?

Надеюсь, я не совсем лаю не по тому дереву — все утро «творчески гуглил», и это теория, которую я придумал…

Я не уверен, что уменьшение версии было бы хорошей идеей.. Единственный способ сделать это — использовать триггеры, ищущие соответствующие идентификаторы записей и обновляющие их соответствующим образом. Это было бы не очень хорошо для производительности..

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

Создайте отдельную архивную таблицу, в которой будут храниться все версии ваших записей. Это будет заполнено триггером при вставке в вашу основную таблицу. Триггер insert перенесет текущую версию записи в ваш архив и update запись в главную таблицу, увеличивая номер версии и обновляя временную метку и данные.

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

 SELECT * FROM TABLE;
  

Если вам нужна возможность просмотра «моментальных снимков» того, как данные выглядели в данный момент времени, вам также понадобятся valid_from и valid_to столбцы в таблице для записи времени, когда каждая версия записей была последней версией. Вы можете заполнить их с помощью триггеров при записи в таблицу архива..

Valid_to в последней версии записи может быть установлена максимальная доступная дата. Когда вставляется более новая версия записи, вы должны обновить valid_to предыдущую версию, чтобы она была непосредственно перед valid_from новой записью (это не то же самое, чтобы избежать дублирования)..

Затем, когда вы хотите увидеть, как выглядели ваши данные в данный момент времени, вы запрашиваете таблицу архива, используя SQL, подобный:

 SELECT *
FROM ARCHIVE_TABLE a
WHERE <time you're interested in> BETWEEN a.valid_from AND a.valid_to
  

Пакетная работа определенно отличается от обычного подхода вставки / обновления (особенно, если задействованы триггеры или много индексов). Даже с приличными дисками / оборудованием вы обнаружите, что традиционный подход DML работает очень медленно с этим объемом. Для таблиц размером более 100 мм, где вы обновляете 70 мм пакетно каждый месяц, я бы предложил рассмотреть подход, аналогичный:

1. Загрузите новый пакетный файл (70 мм) в отдельную таблицу (NEW_XML) в том же формате, что и существующая таблица (EXISTING_XML). Используйте nologging, чтобы избежать отмены.

2. Добавьте (отмените) записи из EXISTING_XML, которые не существуют в NEW_XML (30-миллиметровые записи, основанные на любых ключах, которые вы уже используете).

3. Переименуйте EXISTING_XML в HISTORY_XML и NEW_XML в EXISTING_XML. Здесь вам потребуется некоторое время простоя, возможно, в выходные дни. На самом деле это не займет много времени, но вам понадобится время для следующего шага (и из-за недействительности объекта). Если у вас уже есть HISTORY_XML за предыдущий месяц, сначала сократите и удалите его (сохраните данные за 1 месяц).

4. Создайте индексы, статистику, ограничения и т.д. На EXISTING_XML (который теперь также содержит новые данные). Перекомпилируйте любые недействительные объекты, используйте ведение журнала и т.д.

Итак, в двух словах, у вас будет таблица (EXISTING_XML), которая не только содержит новые данные, но и была построена относительно быстро (во много раз быстрее, чем подход DML / trigger). Кроме того, вы можете попробовать использовать parallel для шага 2, если это необходимо.

Надеюсь, это поможет.