oracle партиционирование таблицы скрипт
Ссылочное партиционирование в Oracle и опция flashback table to before drop
Ссылочное партиционирование позволяет организовать прозрачную для разработчика и пользователя поддержку партиционирования таблиц, связанных отношением master-child через внешние ключи.
Эта крайне удобная опция таит в себе однако одну скрытую особенность (не хочу говорить о баге в данном случае), которая может привести к случайной потере данных. В большей степени это касается экземпляров баз данных, на которых ведется разработка (и где высока вероятность, что кто-то выполнит DROP TABLE). Тем не менее, тот же казус может произойти и на продуктовых серверах в результате ошибок DBA.
Создадим пару таблиц, из которых одна — родительская, а вторая — дочерняя (имеется внешний ключ, ссылающийся на родительскую таблицу). Кроме того, сделаем таблицы партиционированными и обеспечим автоматическую поддержку партиций в дочерней таблице с помощью referential partitioning:
Допустим, что в результате ошибки (или преднамеренно) некто удалил дочернюю таблицу:
Казалось бы, ничего страшного — включенная опция recyclebin обеспечит нам восстановление таблицы (мы ведь пока ничего другого не делали), но не тут-то было:
Recyclebin включен — в этом можно убедиться:
На самом деле объяснение этому феномену очень простое — опция flashback table to before drop не восстанавливает внешние ключи (это отражено в документации). Поэтому Oracle просто не в состоянии восстановить дочернюю таблицу, поскольку механизм ссылочного партиционирования не может работать в отсутствие внешних ключей. К сожалению Oracle в этом случае не предусмотрел никакого механизма, который мог бы предупредить об опасности.
Будьте внимательны.
Скахин Алексей / pihel
Личный блог. Заметки о программировании и не только
Страницы
пятница, 19 июня 2015 г.
ORACLE: Оптимизация работы секционированных таблиц
Начиная с Oracle 11 размер любой партиции, представленной на диске, по умолчанию равен 8МБ, даже если она не имеет в себе никаких данных (До 11 версии размер по умолчанию был 65КБ).
Отсюда важность указания STORAGE INITIAL при создании партиционированной таблицы.
Отсюда не сложно высчитать минимальный размер такой таблицы 18*100*8МБ= 140ГБ.
Что явный перебор при исходном размере таблицы без секций = 2ГБ.
Размер партиции можно взять с небольшим запасом (1МБ):
Минимальный размер такой таблицы вырастет в сравнении с плоской незначительно на (1-0,68/1)*100% = 32%
+ Уменьшение размера таблицы сопоставимо изменению параметров PCTFREE/PCTUSED
— При update строки с увеличением его размера, в случае нехватки свободного места в блоке (PCTFREE) строка целиком будет перенесена из текущего блока в новый. В старом блоке будет проставлена ссылка на новое расположение. Т.е. при чтении данных из таблицы нужно будет выполнить дополнительное рекурсивное чтение, что значительно увеличит стоимость запроса.
— При большом числе параллельных сессии к одному блоку сильно разрастается информация о заинтересованных сессиях к строкам блока (ITL). Если место в блоке закончится, то невозможно будет расширить ITL, что приведет к ошибке обновления блока.
3. Быстрое обновление таблиц через PARTITION EXCANGE.
Для этого нужна вспомогательная таблица, куда будет класться очередная порция для обновления.
У таблиц должна совпадать полностью структура, включая размерности и последовательность столбцов.
После этого можно перекинуть из этой таблицу в целевую партиционированную одной командой.
Пример перекидки из промежуточной таблицы в RANGE-INTERVAL партиционированную таблицу.
Этой командой произойдет обмен данными между таблицами: партиция заполнится данными таблицы T_TBL_PT, а таблица данными пустой партиции T_TBL.
Аналогично можно делать обмены с субпартициями
4. Узнать имя партиции по произвольному фильтру
Такое может понадобится, допустим, для сбора статистики только по нужной секции, т.к. в dbms_stat нужно указать физическое имя.
Если известно значение в колонке секций, то нужно воспользоваться конструкцией:
5. System партицирование
Нет указания колонки при создании, из-за этого нужно указывать конкретную партицию при вставке или выборке
6. Reference партицирование
Возможность создания детализированной таблицы с наследованием партицирования от родительской: В строках нет даты, она автоматом подтягивается по фк из заголовка и партицируется по ней.
Цена этому увеличение нагрузки в 10 раз: https://jonathanlewis.wordpress.com/2018/03/19/reference-costs/
7. Изменение параметров партиции на основании статистики использования
На основании статистики использования партиций (DBA_HEAT_MAP_SEG_HISTOGRAM) можно включить компрессию
Секционирование. Автоматическое добавление секций
Решение для равномерно увеличивающегося ключа секционирования без пропусков
Данный триггер с использованием автономных транзакций автоматически создает новую секцию с именем ‘P’+номер секции размером в 10000, когда ID — наш ключ секционирования — остается 4000 значений до границы секции(10000-4000 = 6000, т.е. и тд.), но сначала проверяется не существует ли уже данная секция(такое может произойти, например, при повторном добавлении 6000-й записи, или ручном добавлении секции). Параметры секционирования — 10000 и 4000, вы должны подбирать исходя из вашей конкретной ситуации, но следует учесть, что граница(4000 в примере) должна быть больше максимального количества одномоментно добавляемых записей, т.к. иначе на момент транзакции вставки данных, транзакция не будет «знать» о новой секции, т.к. на начало транзакции ее не существовало, поэтому данные вставлены не будут с жалобой об отсутствии сопоставления секции данному ключу. Этого бы можно было избежать с использованием alter table split default_partition, который я рассмотрю далее, но это скажется на времени выполнения.
Проверим наш триггер, заполнив секцию:
insert into xtender.test_part
select rownum, o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE, o.CREATED
from all_objects o
where rownum
Кроме того, в случае использования сиквенсов, которые из-за кэширования «шагают» не последовательно можно изменить триггер, чтобы он выполнялся для набора значений с 4000 до 3900 записи с конца секции:
заменим условие
на
Решение для прочих случаев
В случаях случаях, когда мы указываем секцию по умолчанию, мы можем разделять ее тогда, когда туда уже попали записи, вопрос в том как это автоматически отслеживать.
В Data dictionary мы можем получить информацию о всех секциях секционированных таблиц, сделав выборку из dba_tab_partitions, в которой partition_position указывает порядок секции в таблице, а high_value — параметры секции. Следовательно, мы можем получить имя последней секции в таблице и сделать выборку из нее для получения количества записей в ней.
Как автоматически секционировать таблицу по дате?
Имеется вот такая вот таблица:
2 ответа 2
PS в первую партицию (секцию) попадут все дни
You must specify at least one range partition using the PARTITION clause.
UPDATE:
вместо select * from tab partition(p1); можно воспользоваться след. запросом, который должен прочитать только одну дневную партицию и вернуть из нее записи:
В дополнение к ответу уважаемого коллеги @MaxU.
Допустим, у таблицы в вопросе есть дочерняя таблица с внешним ключом. Так как в этой таблице записей обычно несравненно больше, чем в родительской, то естестственно, встаёт потребность её тоже партиционировать. Причём, логично, что все записи в дочерней таблице, относящиеся к первичным ключам одной родительской партиции, должны быть также в одной дочерней партиции.
Interval partitioning cannot be used with reference partitioning.
То есть, референциальное партиционирование по интервалу, как в соседнем ответе, было недоступно. Начиная с версии 12c это ограничение было снято, см. Interval-Reference Partitioning.
При вставке будут автоматически созданы новые партиции в обоих таблицах:
возникает такой вопрос, партиции создаются неименованные, как в следствии этого делать по ним выборку? стандартное select * from tab partition(p1)
Чтобы выбрать партицию не зная её имени, укажите ключ партиции в клаузе PARTITION FOR (см. partition_extension_clause), эффект будет тот же:
Как правильно разбить таблицу на partition в Oracle?
Есть таблица, в которой больше 100 млн записей.
Структура таблицы GoodsList:
1. Как ее правильно разбить на partition по date, если чаще всего отчеты делаются за текущий месяц? И стоит ли делать допустим такое разбиение partition по году затем в subpartition по месяцам.
2. Можно ли создание новой партиции сделать автоматическим согласно критерия деления?
3. Если структуру перестроить разбить данную таблицу на две.
В первую GoodsListHead:
А вторую GoodsList:
Как в таком случае организовать partition по дате в таблице GoodsList?
Теперь по вопросам:
1. Как ее правильно разбить на partition по date, если чаще всего отчеты делаются за текущий месяц?
Кроме отчетов, наверное, еще что-то происходит? 🙂 в целом, необходим целый ряд исследований и тестирований, прежде чем переходить на секционирование.
И стоит ли делать допустим такое разбиение partition по году затем в subpartition по месяцам.
не стоит. Если уж режете по дате, то делайте сразу по месяцам, иначе получите лишний оверхед.
2. Можно ли создание новой партиции сделать автоматическим согласно критерия деления?
3. Если структуру перестроить разбить данную таблицу на две.