|
| |||
Аруп Нанда Аруп Нанда: новые схемы секционирования
Источник: журнал Oracle Magazine, September – October 2007 Изучайте, когда и как использовать новые схемы секционирования в сервере Oracle Database 11g.
В статье "Partition Decisions", журнал Oracle Magazine, сентябрь-октябрь 2006 (русский перевод: "Выбор схем секционирования"), я писал о различных типах секционирования базы данных и о том, как выбирать стратегию секционирования, которая отвечает вашим специфическим требованиям. В сервере Oracle Database 11g число схем секционирования значительно увеличилось, предлагаются дополнительные функциональные возможности, включая возможность определения новых типов составного секционирования, выбор интервального секционирования, указание внешнего ключа для наследования ключа секционирования от родительской таблицы, а также секционирование по виртуальным столбцам.
Ссылочное секционирование
Рассмотрим гипотетическую компанию Acme Hotels, для которой вы разрабатываете систему бронирования отелей. В одной из основных таблиц RES хранится информация о бронировании. Столбцы этой таблицы: RES_ID NUMBER RES_DATE DATE HOTEL_ID NUMBER(3) GUEST_ID NUMBER Столбцы res_id, res_date, hotel_id и guest_id ссылаются на уникальный идентификатор операции бронирования, забронированную дату, уникальный идентификатор забронированного отеля и уникальный идентификатор клиента соответственно. Большинство пользователей выполняют запросы по столбцу res_date, он также используется для идентификации записей для секционирования, поэтому вы решаете выбрать поквартальное диапазонное секционирование таблицы по этому столбцу, как это показано на листинге 1. В конкретной операции бронирования может быть много транзакций, каждая запись идентифицируется уникальным идентификатором транзакции trans_id. Таблица TRANS – дочерняя таблица таблицы RES, поэтому есть внешний ключ по столбцу TRANS.res_id, указывающий на таблицу RES. Таблицу TRANS нужно также архивировать, как и таблицу RES, поэтому ее нужно секционировать точно таким образом – поквартальное диапазонное секционирование по столбцу res_date.
Затем, если вы хотите создать таблицу TRANS, которая содержит результаты выполнения транзакций операций бронирования, то она будет выглядеть так: TRANS_ID NUMBER RES_ID NUMBER TRANS_DATE DATE AMT NUMBER Однако возникает проблема: в таблице TRANS нет столбца res_date, вы же не можете секционировать по столбцу, который не существует? Зайдем в Database 11g. В сервере Oracle Database 11g появилась очень полезная новая возможность: ссылочное секционирование (referential partitioning). Поэтому вместо добавления столбца res_date к таблице TRANS и указания предложения секционирования, как это было показано на листинге 1 для таблицы RES, вы можете указать простое предложение PARTITION BY REFERENCE, как это показано на листинге 2. В качестве аргумента вы должны задать имя ограничения FOREIGN KEY (ограничение внешнего ключа) и указать, как устанавливаются ссылки. Например, в данном случае вы создаете ссылочные секции по таблице TRANS, ссылаясь на внешний ключ FK_TRANS_01, который указывает на родительскую таблицу RES. Таблица TRANS будет наследовать стратегию секционирования таблицы RES, даже в том случае, когда столбец секционирования отсутствует в таблице TRANS. Ссылочное секционирование по существу указывает серверу Oracle Database на необходимость эквисекционирования дочерней таблицы (в этом примере TRANS) точно таким же образом, что и секционирование родительской таблицы (RES).
Можно посмотреть, как устанавливается ссылочное секционирование, для этого нужно выполнить запрос к представлению словаря данных USER_PART_TABLES, как это показано на листинге 3. Столбец partitioning_type показывает тип схемы секционирования; на листинге 3 этот тип – REFERENCE, в этом случае столбец ref_ptn_constraint_name показывает имя ограничения внешнего ключа FK_TRANS_01
Для проверки границ секций выполните запрос к представлению словаря данных USER_TAB_PARTITIONS, как это показано на листинге 4. Обратите внимание, все границы секций дочерней таблицы TRANS, показанные в столбце HIGH_VALUE имеют значение null. Это указывает, что эти границы совпадают с границами секций родительской таблицы RES и они не определяются независимо.
Используя ссылочное секционирование для повышения производительности и удовлетворения требованиям архивирования, вы можете секционировать любые дочерние таблицы по их родительским таблицам, даже если в этих дочерних таблицах отсутствует столбец с ключом секционирования. Предположим, вы очищаете таблицу RES, удаляя ее секции. Все дочерние таблицы также очищаются, автоматически. Когда вы добавляете к родительской таблице секцию, соответствующая секция автоматически добавляется к дочерней таблице. Аналогично, когда вы разделяете секцию PM таблицы RES, также на две секции разделяется секция PM таблицы TRANS, точно с такими же границами. Например, вы можете разделить секцию PM таблицы RES по границе, равной 401: alter table res split partition pm at (401) into (partition p4, partition pm); Теперь, если вы посмотрите на секции дочерней таблицы TRANS, вы увидите: select partition_name from user_tab_partitions where table_name = 'TRANS'; PARTITION_NAME --------------- P1 P2 P3 P4 SYS_P45 Обратите внимание, когда разделялась секция PM, была создана новая секция SYS_P45. (Имя SYS_P45 образовано из порядкового номера и префикса SYS_P.) Вы можете изменить это имя, чтобы оно было согласовано с именем секции PM в таблице RES. Дляэтого используйте следующий SQL-оператор: alter table trans rename partition SYS_P45 to PM; Также обратите внимание, если эта дочерняя таблица имеет какие-то локально секционированные индексы, их секции также должны быть разделены должным образом. Когда вы удаляете или разделяете секции родительской таблицы, эти операции выполняются и над соответствующими секциями дочерней таблицы. Если родительская таблица имеет множественные ссылочно секционированные дочерние таблицы, эти операции автоматически выполняются над всеми этими таблицами. Ссылочное секционирование позволяет вам определять надлежащую стратегию секционирования родительской таблицы, тогда как детали секционирования дочерних таблиц опускаются. Это не только существенно упрощает администрирование секционированных объектов, но и разрешает эквисекционирование, даже тогда, когда столбцы с ключами секционирования нежелательно включать в дочернюю таблицу или, когда можно включать, но это не разрешается, например, когда используются предварительно скомпонованные приложения.
Интервальное секционирование
Что произойдет, когда вставляемая оператором INSERT запись имеет значение столбца с ключом секционирования, для которого не определена секция? Этот оператор не будет выполнен. Однако это можно обойти, если вы определите секцию по умолчанию, например, используя предложение MAXVALUE в случае диапазонного секционирования (как показано на листинге 1) или предложение DEFAULT в случае списочного секционирования, но это обессмысливает секционирование. Так что, вы должны обеспечивать наличие секций, доступных для всех возможных записей, вставляемых в таблицу; вы должны тщательно определять все возможные значения, а затем создавать для них секции (до появления реальных данных). Например, предположим, что таблица RES секционирована по столбцу res_id со значениями диапазонов секций 1-100, 101-200 и 201-300. Вы можете выполнять мониторинг максимального значения столбца res_id, и когда это значение приблизится к 300, вы можете создать новую секцию p4 для диапазона значений 301-400. Про эту операцию "легче сказать, чем сделать". Если вы забудете добавить эту секцию, новая запись со значением столбца res_id = 301 будет либо отвергаться, либо вставляться в секцию по умолчанию (если она создана). Хорошо бы такие секции создавать автоматически. Эта возможность появилась в сервере Oracle Database 11g: при необходимости секции создаются автоматически без какого-либо вашего вмешательства. Для этого следует использовать интервальное секционирование (interval partitioning): create table res ( res_id number not null, res_date date, hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );Этот скрипт создает секцию p1 для записей, значение столбца res_id которых находится в диапазоне 1-100. Когда вставляются записи со значением столбца res_id меньшим 101, они помещаются в секцию p1, а когда в новой записи значение этого столбца равно или больше 101, сервер Oracle Database 11g создает новую секцию, имя которой генерируется системой. Например, предположим, вы вставляете запись со значением столбца res_id, равным 901: insert into res values (901,sysdate,1,1); Теперь проверим секции, определенные над таблицей, выполнив запрос, показанный на листинге 5. Обратите внимание, автоматически была создана секция SYS_P82, в которую помещается новая запись со значением столбца res_id, равным 901, не входящее в границы диапазонов секции P1. В то же время, если вы вставите другую запись со значением столбца res_id res_id, равным 301, секция SYS_P82 разобьется на две секции, как это показано в нижней половине листинга 5.
Вы можете ссылаться на секции по имени, например, когда вы усекаете конкретную секцию. Предположим, имена секций генерируются во время исполнения и вы заранее их не знаете. Например, предположим, вы хотите усечь секцию, в которой в столбце res_id содержится значение, равное 901, но вы не знаете имя этой секции. Можно узнать это имя, выполнив запрос к представлению словаря данных USER_TAB_PARTITIONS, как это показано на листингах 4 и 5, но проще воспользоваться расширенным в сервере Oracle Database 11g синтаксисом доступа к секциям. Вы можете усечь эту секцию следующим SQL-оператором: alter table res truncate partition for (901);
Предложение FOR (значение) можно использовать не только при обращении к интервально секционированным таблицам, но и во всех SQL-операторах непосредственного доступа к секционированным таблицам любого вида. При интервальном секционировании первая указываемая в скрипте создания таблицы создается в пользовательском табличном пространстве по умолчанию, а последующие секции создаются не в пользовательском табличном пространстве, а в табличном пространстве по умолчанию базы данных. Даже если вы в атрибутах таблицы измените табличное пространство по умолчанию, новые секции по-прежнему будут создаваться в табличном пространстве по умолчанию базы данных. Для создания секций в других табличных пространствах нужно во время создания таблицы указать дополнительное предложение. Для задания табличных пространств RESDATA1 и RESDATA2 для расположения новых секций вставьте после предложения INTERVAL предложение STORE IN: interval (100) store in (resdata1,resdata2) Теперь новые секции будут циклически распределяться по этим двум табличным пространствам. В качестве интервала секционирования также можно использовать отметку времени. Это пригодится для создания таблицы, секционированной по времени, в которой записи секционируются по месяцам: interval (numtoyminterval(1, 'MONTH')) Расширенное составное секционирование В моей предыдущей статье о секционировании я показывал другую важную возможность: составное секционирование. При составном секционировании секции в дальнейшем разбиваются на подсекции. До сервера Oracle Database 10g Release 2 включительно вы могли разбивать секции, секционированные по диапазонам ключей, только на хеш-подсекции (range-hash) или списочные подсекции (range-list). Этого достаточно для большинства видов секционирования, но в некоторых случаях можно действительно воспользоваться преимуществами диапазонного подсекционирования. Например, рассмотрим показанный ранее пример бронирования отелей. предположим, идентификатор отелей в таблице RES показывает тип отелей: значения 1-100 указывают на пятизвездочные отели, значения 101-200 указывают на четырехзвездочные отели и т.д. Сравнительный анализ доходов обычно делается для отелей с одинаковыми звездами, поэтому пользователи имеют тенденцию выбирать данные только для конкретного диапазона значений идентификатора отелей, например, 1-100. Следовательно, для таблицы RES имеет смысл выбрать диапазонное секционирование по столбцу hotel_id. Однако пользователи также имеют тенденцию выбирать самые последние данные и вы можете захотеть хранить более старые данные на дешевых дисках, что позволяет уменьшить стоимость хранения. Таким образом, это весомый аргумент в пользу диапазонного секционирования по столбцу res_date. Обе альтернативы – привлекательны, какую же выбрать?
А почему бы не выбрать обе? В сервере Oracle Database 11g в дополнение к уже существующему диапазонному секционированию с хеш-подсекционированием и списочным подсекционированием появились диапазонное секционирование с диапазонным подсекционированием (range-range), списочное секционирование с диапазонным подсекционированием (list-range), списочное секционирование с хеш-подсекционированием (list-hash) и списочное секционирование со списочным подсекционированием (list-list). В этом примере вы можете создать диапазонные секции по столбцу res_date, а затем создать диапазонные подсекции по столбцу hotel_id, как это показано на листинге 6. Обратите внимание, я умышленно выбрал интервальное секционирование. Я мог бы явно задавать имена секций и верхние границы значений, но я захотел показать, как использовать интервальное секционирование для облегчения сопровождения. Я мог бы выбрать обратный порядок секционирования: сначала секционировать по столбцу hotel_id, а затем создавать подсекции по столбцу res_date. Для проверки созданных подсекций можно выполнить запрос к представлению словаря данных USER_TAB_SUBPARTITIONS: SQL> select partition_name, 2 subpartition_name 3 from user_tab_subpartitions 4 where table_name = 'RES'; PARTITION_NAME SUBPARTITION_NAME ------------------ ----------------- P1 P1_SM P1 P1_S4 P1 P1_S3 P1 P1_S2 P1 P1_S1 SYS_P106 SYS_SUBP105 SYS_P106 SYS_SUBP104 SYS_P106 SYS_SUBP103 SYS_P106 SYS_SUBP102 SYS_P106 SYS_SUBP101 Секционирование по виртуальным столбцам В сервере Oracle Database 11g также предлагается новая функциональная возможность, которая называется виртуальный столбец (virtual column). Виртуальный столбец в действительности не хранится в таблице, а каждый раз вычисляется при обращении к нему во время исполнения. По виртуальному столбцу можно также секционировать данные Рассмотрим пример с таблицей RES. Как объяснялось ранее столбец hotel_id показывает звездочность отеля: 1-100 означает 5 звезд, 101-200 – 4 звезды и т.д. Предположим, вы хотите сделать количество звезд частью таблицы RES. Самый простой способ – создать столбец STAR_RATING и использовать его для списочного секционирования таблицы. А как заполнять этот столбец? Один вариант – переписать для этого приложение, но это не очень хороший вариант. Другой вариант – для автоматического заполнения столбца использовать триггер. Вместо этого в сервере Oracle Database 11g для заполнения столбца реальными значениями можно просто использовать виртуальный столбец. На листинге 7 показан скрипт создания таблицы.
Таким образом, вместо того, чтобы заставлять пользователей декодировать количество звезд, вам нужно создать столбец, который недвусмысленным образом делает это и секционировать таблицу по этому столбцу. И все это делается без каких либо строк кода в триггере заполнения столбца.
Заключение
Секционирование – мощный механизм современного проектирования базы данных, который обеспечивает более легкое администрирование базы данных без каких либо изменений логического проекта. В большинстве случаев это помогает без какого либо изменения приложения согласовать на вешний вид противоречащие цели: мелкомодульные хранение, резервирование, архивирование и извлечение. В сервере Oracle Database 11g опция секционирования получила дальнейшее развитие: ссылочное и интервальное секционирование, расширенное составное секционирование и секционирование по виртуальным столбцам; все это представляет собой неопровержимые доводы в пользу включения секционирования в ваш физический проект базы данных. Аруп Нанда (Arup Nanda) ( arup@proligence.com) – администратор баз данных Oracle более с чем двенадцатилетним стажем. Он занимается всеми аспектами администрирования – от оптимизации производительности до информационной безопасности и восстановления в аварийных ситуациях. Аруп – соавтор книги "PL/SQL for DBAs" (O'Reilly Media, 2005). В 2003 г. он был удостоен награды журнала Oracle Magazine "Oracle's DBA of the Year" (администратор года баз данных Oracle). | ||||||||||||||||
|
|
|
