Секционирование. Автоматическое добавление секций

[@wiseimp]

fyi. в литературе чаще используется термин «партицирование»

[@xtender]

В литературе? Например, в каких книгах? Все книги, которые я читал содержат секционирование. Транслитированное «партицирование» используют крайне редко и только на форумах.
fyi:

«партицирование таблиц» — Результатов: примерно 1 470
«секционирование таблиц» — Результатов: примерно 19 700

Кроме того:
ru.wikipedia.org/wiki/Секционирование а не партицирование
oracle.com — используют только «Секционирование»
msdn.microsoft.com — используют только «Секционирование»
Postgresql.org — используют только «Секционирование»

[@Iliapan]

Интересно было бы посмотреть бенчмарки без и с использованием партицирования.

[@zhekappp]

when (mod(NEW.id,10000) = 6000)

Надо помнить, что в sequnce, по которому формируется id легко могут быть пропуски.
Например, из-за rollback-ов или, чаще, cache > 0.

Вообще большой незачет oracle за то, что не придумали механизм формирования нормального имени новой автоматически добавляемой партиции.

[@xtender]

Совершенно верно, если используются сиквенсы пропуски будут, т.к. используется шаг для кэширования. В таких случаях придется устанавливать when (mod(NEW.id,10000) between 6000 and 6100) — соответственно триггер будет вызываться н-ое количество «лишних» раз, но выполняться будет быстро.

Вообще большой незачет oracle за то, что не придумали механизм формирования нормального имени новой автоматически добавляемой партиции.

Ну мне кажется это не особо важно, все равно при автоматизации брать данные из data dictionary.

[@zhekappp]

В основном работаю с историческими таблицами, секционированными по дате.
Очень удобно, когда в имени секции соответствует хранимым в ней данным. Как для написания запросов с использованием from ...partiotion (...), так и для оценки размеров сегментов по вьюхам *_segments, так и для операций truncate, drop, split, exchange partition…

Есть еще одна мысль по поводу select count(1) из максимальной секции — на моей системе (vldb) это будет работать, возможно, более суток для всех таблиц и неприятно скажется на содержимом db_cache. Добавление же хинта parallel решит проблему, но создаст заметную нагрузку на систему.
Мне кажется более интересным вариант — обеспечить актуальную статистику и брать количество строк из поля dba_tab_partitions.num_rows :)

[@xtender]

Спасибо за напоминание, забыл обновить скрипт — я позже его изменял на select count(1) where rownum<2, т.к. проверка нужна существования хоть одной записи. И добавил пункт про сбор статистики, хотя это гораздо более дорогостоящая операция, но в случае если статистика нужна не только для этого(для CBO, например), то использование um_rows действительно все упрощает :)

[@masiandr]

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

[@xtender]

На нагруженной инсертами таблице вызывать блокировку на уровне таблицы — вообще грешно, в остальных случаях никакая блокировка не мешает.

[@masiandr]

alter table add partition накладывает EXCLUSIVE блокировку. insert/update в свою очередь накладывает SHARED блокировку. Поэтому, если на таблице идет интенсивный insert/update, то add partition не сможет получить EXCLUSIVE блокировку и вынужден будет отвалиться с ORA-00054: resource busy and acquire with NOWAIT specified, или ждать освобождение блокировки, что увеличит время транзакции.

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

[@xtender]

Собственно, я про это и сказал в предыдущем комментарии, но раз уж настаиваете, то давайте разберемся для данного конкретного случая:

• Если вы все-таки хотите использовать триггер и получаете ora-54, у вас два варианта:
  
  1. увеличить ddl_lock_timeout
  2. повторно выполнять ddl до выполнения
  
  Пояснения: DDL будет выполнен, как только закончатся текущие блокирующие транзакции. Сама же процедура создания новой пустой секции очень быстрая, она гораздо быстрее разбиения maxvalued или default секции. Те же транзакции, которые будут начаты во время ddl, будут выполнены сразу после этой процедуры.
  
• Следующий вариант — автоматизированное создание секций в определенное время. Например, если ночью у вас значительное снижение нагрузки, то вы можете создать задание, в котором пройтись циклом по результату функции get_maxvalued_partitions, создавая секции для каждой из возвращенных таблиц.
• Если же и этот вариант вас не устраивает, то вы можете как я и описал, просто настроить джоб с уведомлением ДБА с помощью send_partitions_report

Все эти варианты я описал в статье.