Cекционирование по циклу таблиц c LOB-столбцами.

Секционирование таблиц в Oracle Database дает новые возможности повышения производительности работы с ними. Секционирование таблиц позволяет:

• повысить эффективность запросов вследствие поиска данных только в некоторых секциях таблицы (метод отсечения секций - partition pruning) или используя распараллеливание запросов;
• осуществить быструю очистку секции ( truncate) с освобождением места в табличном пространстве;
• ускорить ввод-вывод данных, распределив на разных дисковых устройствах более и менее интенсивно используемые секции;
• уменьшить конкуренцию за индексы, заменяя (там, где можно) глобальные индексы на локальные индексы (и, как вариант, секционируя глобальные индексы без секционирования таблицы). Так нами было достигнуто существенное снижение конкуренции за индексы вследствие hash-секционирования индексно-организованной таблицы (IOT);
• выполнять выборочное компрессирование разных секций;
• осуществлять быстрое архивирование редко используемых секций, в том числе используя exchange;
• повысить надежность функционирования таблицы вследствие независимости секций друг от друга, независимости операций резервирования и восстановления секций и т.д.

От версии к версии Oracle расширяются возможности секционирования таблиц. Например, в Oracle 11g появилась возможность автоматического создания новой секции, расширен перечень комбинаций секций и подсекций, использование в качестве ключа секционирования виртуального, а не только реального столбца и др. Последняя новая возможность была использована нами при секционировании по циклу.

Под секционированием таблиц по циклу понимаются секционирование, выполненное в соответствии с двумя правилами. Первое правило – таблица должна содержать фиксированное количество секций, равное числу дней в месяце (31 секция) или в году (366 секций), либо числу месяцев (12 секций). Второе правило - данные в одну и ту же секцию попадают с определенной периодичностью (цикличностью). Периодичность зависит от метода секционирования по циклу и может быть равна 31, 366 или 12. Например, данные попадают в секцию определенного дня года, независимо от номера года.

Идея секционирования по циклу возникла из следующей практической задачи. В одну из таблиц с именем DOCIMAGE записывается большой поток данных, основной объем которого составляют BLOB'ы, помещаемые в столбец OLEOBJECT таблицы DOCIMAGE. Вероятность обращения к этим данных со временем быстро снижается, однако, сохраняется требование их непрерывной доступности, т.е. в любой момент времени эти BLOB'ы могут быть востребованы. Для уменьшения стоимости хранения большого объема данных было решено переместить в архив основную по объему часть данных (а, именно, столбцы с BLOB-данными) и расположить их на более дешевом и более медленном устройстве хранения EMC Centera.

Для этого было проведено секционирование таблицы DOCIMAGE (с секциями на каждый день года) по столбцу PARTID, в котором номер текущего дня формируется из столбца CREATED соответствующим триггером. При создании таблицы было задано, что сама таблица размещается в одном табличном пространстве, а ее BLOB-данные в другом табличном пространстве IMAGETBS. Секции ежедневно заполняются, при этом раз в сутки запускается процесс, который очищает BLOB-столбцы более ранних секций (двух месячной давности и не имеющих запрета на очистку) со Shrink-сжатием табличного пространства этих секций. Для первой секции команда Shrink, например, имеет вид:

В случае, если пользовательский запрос запрашивает BLOB-данные, которых нет в оперативной таблице DOCIMAGE, система извлекает данные из архива с устройства EMC Centera и помещает их обратно в соответствующую секцию таблицы DOCIMAGE. После этого данные обрабатываются и остаются в оперативной таблице DOCIMAGE, пока не попадут в очередной цикл очистки. Таким образом, в оперативной таблице DOCIMAGE содержатся свежие данные за последние два месяца плюс более старые данные, которые недавно были извлечены из архива. Все остальные BLOB-данные располагается на более медленном архивном устройстве. Данная секционированная таблица успешно функционирует 1,5 года и показала эффективность принятого решения.

• цикличность буфера позволяет ограничить количество секций с возможностью сохранения данных за неограниченный период времени. Из-за постоянного удаления устаревших данных в архив размеры секций остается приемлемыми;
• наличие секций по дням позволяет получить приемлемое время сжатия табличного пространства по Shrink. Эти важно, так как необходимость сохранения в секциях отдельных неудаляемых BLOB'ов не позволяет применить операцию быстрой Truncate-очистки секции;
• по сравнению с нециклическим секционированием проще настраиваются циклически исполняемые процессы по обслуживанию таблицы (архивирование, очистка и т.д.).

Команда создания таблицы DOCIMAGE с размещением BLOB'ов в другом табличном пространстве приведена ниже:

позволяет не только обеспечивать ввод данных LOB-столбца OLEOBJECT в другое табличное пространство IMAGETBS (сама таблица находиться в табличном пространстве ADMDATA), но и задавать режимы ввода LOB данных. Так, при наличии фразы ENABLE STORAGE IN ROW маленькие LOB'ы размером менее 4000 байт будут храниться в самой таблице, а при размере LOB'ов более 4000 байт они будут размещаться в LOB-сегменте (при замене фразы ENABLE STORAGE IN ROW на DISABLE STORAGE IN ROW все LOB'ы будут храниться в LOB-сегменте). Фраза CACHE обеспечивает кэширование данных LOB.

При создании таблицы с LOB-столбцом для него формируются LOB-сегмент и LOB-индексный сегмент, даже если потом они окажутся пустыми вследствие того, что все LOB'ы останутся в строках таблицы.

Создаваемые LOB-сегменты можно увидеть по запросу:

В случае секционирования таблицы с LOB-столбцами каждой секции таблицы будет соответствовать две новых секции: секция LOB-сегмента и LOB-индексный сегмент. Результаты секционирования таблицы с LOB-столбцами можно увидеть запросом:

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

Опыт показал, что эффективно секционируются по циклу (помимо указанной выше таблицы типа DOCIMAGE) таблицы трех видов:

• секционированные таблицы с числом секций, равным числу дней в году с ежедневной очисткой устаревшей секции;
• секционированные таблицы с числом секций, равным числу месяцев в году (12 секций) с ежемесячной очисткой устаревшей секции с данными за месяц;
• секционированные таблицы с числом секций равным силу дней в месяце (31 секция) с ежедневной очисткой устаревшей секции с данными за день.

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

Идея секционирования по циклу была реализована в таблице PAGES с BLOB-столбцом PAGE_DATA. В этих BLOB-элементах содержится отсканированные документы по страхованию. Объем данных, вводимых ежемесячно в таблицу PAGES, более 500 Гб, и он продолжает расти. В силу этого, хранение информации в течение нескольких месяцев потребовало бы терабайты пространства. В связи с этим, было принято решение разбить таблицу PAGES на 12 месяцев и хранить данные только за текущий и предыдущий месяц (за 2 месяца), а более старые секции очищать по Truncate с освобождением табличного пространства для ввода новых данных. В результате объем хранимых данных стал более ограничен и предсказуем, а очистка по Truncate (вместо обычного Delete, требующего использования команды Shrink для освобождения табличного пространства) происходит быстро с минимальной загрузкой процессора. Секционирование по циклу таблицы PAGES проведено по методу RANGE, где ключем секционирования выступил вновь созданный в таблице столбец PARTID, принимающий значения от 1 до 12 (по числу месяцев). Столбец PARTID заполняется триггером при вводе новой строки в таблицу. В триггере выполняется функция to_number(to_char(CREATED,'MM')), которая обрабатывает столбец CREATED таблицы (дата создания записи), выделяя значение месяца.

Структура таблицы PAGES в схеме ADM приведена ниже.

Результаты секционирования могут быть просмотрены запросом:

Вид триггера, заполняющего столбец PARTID, приведен ниже:

Очистка по Truncate, например, второй секции выполняется по команде:

где фраза UPDATE GLOBAL INDEXES позволяет обеспечить после Truncate доступность глобальных индексов таблицы.

Контроль над освобождением табличного пространства (в байтах) после выполнения Truncate можно осуществлять по запросу:

Секционирование может быть не только по методу секционирования RANGE, но и по LIST. В этом случае командные строки секционирования таблицы примут вид

Приведенный выше вариант был разработан для версии Oracle 10g. С переходом на Oracle 11g другие таблицы, например, таблица SESS_LOCK (будет рассмотрена ниже) была секционирована с использованием виртуального столбца вместо реального столбца PARTID по методу секционирования LIST. Для таблицы PAGES виртуальный столбец имел бы вид:

где строки generated always as и virtual - типовые команды для виртуального столбца. Строка to_number(to_char(CREATED ,'MM')) отражает функцию автоматического заполнения виртуального столбца PARTID данными из столбца Created в процессе ввода данных в таблицу.

Преимуществом такого решения является то, что триггер записи данных в столбец PARTID создавать не надо, столбец автоматически будет заполняться числами от 1 до 12 из столбца CREATED.

Команда создания таблицы с виртуальным столбцом выглядит следующим образом:

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

В таблицу SESS_LOCK в течение дня вводиться информация о блокировках между сессиями. В таблице имеется столбец CLOB. Данная информация храниться менее месяца (за 14 дней). Секционирование по циклу было организовано с 31-й секцией (максимальное число дней в месяце). Ежедневно запускает JOB, который вызывает процедуру очистки старой секции, отстоящей на 15 дней от текущей, а также очищает секцию, следующей после текущей, подготавливая ее для ввода данных на следующий день.

Таблица работает в Oracle 11g, поэтому используется виртуальный столбец PARTDD взамен реального столбца (что позволило обойтись без триггера для заполнения столбца данными).

Виртуальный столбец автоматически заполняется значением из столбца Monitortime, содержащего текущую дату ввода строки. Таблица была секционирована по методу секционирования LIST. Команда создание таблицы выглядит следующим образом:

где COMPRESS FOR ALL OPERATIONS - команда на сжатие по DML операциям.

1. Чтобы провести секционирование по циклу надо иметь в таблице столбец, который может выступать ключем обычного RANGE секционирования таблицы по дате (типа CREATED, использованного выше) и из которого можно извлечь номер текущего дня или месяца. Это могут быть, например, столбцы, в которых вводиться время создания записи или изменения записи и т.д.
2. При секционировании по циклу возникает дополнительная задача очистки старых секций, чтобы освободить место в секции для ввода новых данных. Для этого был разработаны JOB Sheduler, запускающие процедуры очистки секций ADM.P_TRUNC_PART_DDMM.
3. JOB для таблицы PAGES запускается раз в месяц, а для таблицы SESS_LOCK ежедневно. Процедура очистки секций этими JOB является универсальной, работающей как для секционирования по циклу по месяцам в течение года, так и для секционирования по циклу по дням в течение месяца.

Режим процедуры определяется параметром PARAM_DDMM, задаваемым в JOB. В JOB так же через параметр DELTA_TRUNC задается число месяцев (число дней), в пределах которых подлежит сохранить данные в таблице. Вид JOB Sheduler для таблицы SESS_LOCK, запускающего ежедневно процедуру ADM.P_TRUNC_PART_DDMM очистки секций с сохранением данных за 14 дней, имеет вид:

Для секционирования по месяцам JOB-команда примет вид

Для секционирования по месяцам в JOB команда repeat_interval примет вид: repeat_interval => 'FREQ=MONTHLY; INTERVAL=1', а PARAM_DDMM примет значение "MM".

Универсальная процедура очистки секций ADM.P_TRUNC_PART_DDMM приводиться ниже.

Циклическое секционирование наиболее эффективно для таблиц, у которых имеется ограниченное время жизни данных в секциях, например, в течение нескольких дней внутри месяца, года или несколько месяцев внутри года (при этом в столбцах таблицы могут как быть, так не быть LOB-столбцы).

Преимуществами циклического секционирования являются:

• отсутствие необходимости создания новых и уничтожения старых секций;
• быстрая очистка секций операцией Truncate, что особенно эффективно для таблиц с BLOB столбцами;
• оперативное освобождение табличного пространства;
• упрощенный контроль за операциями над секциями в силу фиксирования имен секций.

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