Секционированные таблицы

Разделение таблиц (секционирование) в OracleБазы данных Oracle Database могут быть достаточно большими, и не так уж редко можно встретить таблицы, хранящие гигабайты (или даже несколько терабайт) данных. Секционирование (partitioning) — это способ логического разделения крупной таблицы на более мелкие части для облегчения обработки запросов, операций DML и управления базой данных. Все разделы такой таблицы разделяют общее логическое определение, определения столбцов и ограничений.


Оглавление статьи[Показать]


Повышение производительности запросов ощущается при секционировании таблицы базы Oracle со многими миллионами записей на сотни или тысячи разделов. В некоторых тяжело нагруженных средах новые разделы могут создаваться даже каждый час! Секционирование приводит к повышению производительности потому, что базе данных в ответ на запрос приходится выполнять поиск только в определенных разделах таблицы. Такое избегание ненужных разделов при запросах называется отсечением разделов (partition pruning) и представляет собой возможность обработки и доступности одного раздела независимо от всех прочих разделов.

Ввод-вывод данных также улучшается благодаря секционированию, поскольку можно хранить разделы интенсивно используемой таблицы на разных дисковых устройствах. Если применяется средство параллельного DML Oracle, то секционированные таблицы обеспечивают более высокую производительность.

Секционирование таблицы также обеспечивает независимость разделов; это означает, помимо прочего, возможность выполнения операций резервирования и восстановления, загрузки данных и создания индексов на разделах, а не на всей крупной таблице целиком. Например, можно копировать данные отдельного раздела с помощью утилиты Data Pump Export, значительно сокращая при этом время экспорта и импорта, когда требуется только часть всего набора данных таблицы. Возможность выполнения таких задач над разделами вместо целых таблиц означает, что можно значительно сократить время простоя базы данных.


На заметку! Хотя секционирование таблиц  обычно повышает производительность в случае очень больших таблиц, это средство не компенсирует плохого кода или других проблем с дизайном приложений. Секционирование также имеет определенную цену, выражающуюся в дополнительной работе по обслуживанию разделов и их индексов. Конечно, также потребуется платить за возможность секционирования, поскольку эта опция лицензируется отдельно у корпорации Oracle.


Секционированные таблицы Oracle Database также являются эффективным способом очистки или архивирования старых данных, которые в настоящее время не нужны. Распространенный подход для крупных хранилищ данных состоит в архивировании данных, которые старше определенной даты, и секционирование таблиц значительно облегчает такое архивирование. Секционированные таблицы в этом случае постоянно содержат примерно один и тот же объем данных за один и тот же период времени (квартальные данные компании за три года всегда занимают 12 разделов таблицы). Вдобавок экспортирование крупных таблиц может происходить быстрее, если разбить их на более мелкие куски и экспортировать каждый из них отдельно.

 

Виды секционированных таблиц

Oracle предлагает шесть разных способов секционирования данных таблицы:

  • секционирование по диапазонам ключей (или диапазонное секционирование; range partitioning)
  • интервальное секционирование (interval partitioning)
  • хеш-секционирование (hash partition)
  • секционирование по списку значений ключа (или списковое секционирование; list partitioning)
  • ссылочное секционирование (reference partitioning)
  • системное секционирование (system partitioning)

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

  • Метод секционирования. Один из шести возможных типов секционирования.
  • Столбец (столбцы) секционирования. Это один или более столбцов, на основе которых необходимо выполнить секционирование таблицы (например, transaction_date). Диапазон или набор значений столбцов секционирования называются ключами секционирования.
  • Описания разделов. Эти описания специфицируют критерий для включения действительных ключей секционирования в каждый раздел. Вы используете границу раздела для диапазонного секционирования, а также конструкцию VALUES LESS THAN, чтобы ограничить значения ключей секционирования для каждого раздела. При списковом секционировании специфицируется список литеральных значений, который сообщает Oracle о том, какие значения ключей секционирования квалифицируют запись для включения в раздел.

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

 

Секционирование по диапазону ключей

Секционирование по диапазону ключей, или диапазонное секционирование, — популярный способ секционирования таблиц Oracle, который стал первым типом секционирования, представленным Oracle. Диапазонное секционирование используется для данных, которые разделяются на диапазоны на основе некоторого критерия. Наилучший результат от диапазонного секционирования можно получить, если данные распределены равномерно по создаваемым диапазонам. Диапазоны могут быть установлены на основе номера последовательности или номера раздела, но техника диапазонного секционирования обычно основана на времени (например, на ежеквартальных или ежемесячных данных).

Предположим, что нужно создать таблицу для хранения ежеквартальных данных за три года, касающихся продаж авиабилетов. Объем операций с такой информацией легко может достичь сотен миллионов транзакций. Если вы секционируете таблицу продаж по квартальным диапазонам и решите хранить данные за не более чем три года в любой заданный момент времени, то получите 12 разделов таблицы, секционированные по кварталам. Всякий раз, когда вводится новый квартал, можно архивировать данные самого старого квартала, тем самым сохраняя постоянное количество разделов. Секционируя огромную таблицу, которая может иметь, к примеру, 480 миллионов строк, вы добиваетесь того, что любой запускаемый запрос будет иметь дело всего с двенадцатой частью всей таблицы, т.е. примерно с 40 миллионами строк, что существенно меньше. Секционирование соответствует принципу “разделяй и властвуй”, позволяя эффективно справляться с огромными массивами данных таблиц.

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


 

SQL> CREATE TABLE sales_data
2 (ticket_no NUMBER,
3 sale_year INT NOT NULL,
4 sale_month INT NOT NULL,
5 sale_day INT NOT NULL)
6 PARTITION BY RANGE (sale_year, sale_month, sale_day)
7 (PARTITION sales_q1 VALUES LESS THAN (2008, 04, 01)
8 TABLESPACE ts1,
9 PARTITION sales_q2 VALUES LESS THAN (2008, 07, 01)
10 TABLESPACE ts2,
11 PARTITION sales_q3 VALUES LESS THAN (2008, 10, 01)
12 TABLESPACE ts3,
13 PARTITION sales_q4 VALUES LESS THAN (2009, 01, 01)
14* TABLESPACE ts4);
Table created.
SQL>

Оператор CREATE TABLE в листинге выше создаст четыре раздела — каждый в отдельном табличном пространстве. Обратите внимание на то, как разделы определяются на основе диапазонов данных.

Первый раздел — sales_q1 — будет включать все транзакции, которые имели место в последние три месяца (один квартал) 2008 г. Второй раздел — sales_q2 — будет включать транзакции, которые происходили с апреля по июнь 2008 г. (месяцы 4, 5 и 6),и т.д.

При секционировании по диапазонам принято последним устанавливать раздел “остальные” (catchall). Когда такой раздел создается, он содержит значения, меньшие максимального (maxvalue), которое просто является значением, большим значений в предпоследнем разделе. Обратите внимание, что каждый раздел имеет определенное имя и хранится в отдельном табличном пространстве.

В секционированной таблице sales_data дата продаж 10 июня 2008 г. (sale_year=2008, sale_month=6 и sale_day=6) имеет ключ секционирования (2008, 6, 10) и должна быть сохранена в разделе sales_q2. Когда запрашиваются данные за 10 июня 2008 г.,Oracle сразу обращается к разделу sales_q2 и полностью игнорирует остальные данные таблицы.

 

Интервальное секционирование

Интервальное секционирование — это расширение традиционного метода секционирования по диапазону ключей. Чтобы реализовать интервальное секционирование, сначала потребуется специфицировать минимум один диапазонный раздел таблицы.Используете вы минимальный однодиапазонный раздел или многодиапазонные разделы, максимальное значение ключа диапазонного секционирования называется точкой перехода (transition point). После того, как данные пересекают точку перехода, база данных автоматически создает интервальные разделы.

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

Ниже перечислено, что следует знать об интервальном секционировании.

  • Чтобы создать таблицу, секционированную по интервалам, укажите в операторе CREATE TABLE конструкцию INTERVAL.
  • Прежде чем специфицировать интервальные разделы, определите, как минимум,один диапазонный раздел с помощью конструкции PARTITION.
  • Можно использовать ключ секционирования, который включает более одного столбца.
  • Ключ секционирования должен быть типа NUMBER или DATE.
  • С помощью конструкции STORE IN в операторе CREATE TABLE можно дополнительно специфицировать табличные пространства для секционирования данных.

В следующем примере создается секционированная по интервалам таблица с четырьмя диапазонными разделами, идентифицированными как p0, p1, p2 и p3. Четыре диапазонных раздела создаются по столбцу time_id, с точкой перехода, начинающейся с максимального значения диапазонных разделов, которое составляет 1 января 2008 г.(в разделе p3). Как только столбец time_id пересекает значение 1 января 2009 г., база данных автоматически переходит к разделам на основе интервалов, причем все они имеют ширину в один месяц.

CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2008', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')) );

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

 

Хеш-секционирование

Предположим, что данные о транзакциях в предыдущем примере распределены по кварталам неравномерно. Что если по причинам, связанным с бизнесом, или социального характера большая часть продаж приходится на последние два квартала, а на первые два — относительно малая часть? Диапазонное секционирование хорошо выглядит только в теории, когда последние два квартала занимают почти половину объема данных исходной несекционированной таблицы.

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

SQL> CREATE TABLE sales_data
2 (ticket_no NUMBER,
3 sale_year INT NOT NULL,
4 sale_month INT NOT NULL,
5 sale_day INT NOT NULL)
6 PARTITION BY HASH (ticket_no)
7 PARTITIONS 4
8* STORE IN (ts1,ts2,ts3,ts4);
Table created.
SQL>

В приведенном примере создаются четыре хеш-раздела в четырех табличных пространствах. Мы не будем знать, в каком разделе находятся данные, скажем, за 10 июня 2008 г. Oracle определит место хранения на основе алгоритма хеширования, и вы никак не управляете отображением строк на разделы.

 

Секционирование по списку значений ключа

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

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

Например, когда вы имеете дело с итоговыми цифрами по штатам США, это значит,что вы имеете дело с 50 различными наборами данных. И тогда имеет смысл секционировать данные по четырем или пяти регионам, вместо использования метода диапазонов для их секционирования по алфавиту. В листинге ниже показано, как использовать списковое секционирование для таблицы ticket_sales. Разделы основаны на группах городов, откуда начинаются полеты, указанных в столбце start_city.


 

SQL> CREATE TABLE sales_data
2 (ticket_no NUMBER,
3 sale_year INT NOT NULL,
4 sale_month INT NOT NULL,
5 sale_day INT NOT NULL,
6 destination_city CHAR(3),
7 start_city CHAR(3))
8 PARTITION BY LIST (start_city)
9 (PARTITION northeast_sales values ('NYC','BOS','PEN') TABLESPACE ts1,
10 PARTITION southwest_sales values ('DFW','ORL','HOU') TABLESPACE ts2,
11 PARTITION pacificwest_sales values('SAN','LOS','WAS') TABLESPACE ts3,
12* PARTITION southeast_sales values ('MIA','CHA','ATL') TABLESPACE ts4);
Table created.
SQL>

В приведенном примере описание раздела специфицирует список значений столбца start_city. Оператор определения таблицы создает четыре списковых раздела. Города,входящие в конкретный список, попадают в соответствующий раздел. Билет с информацией 9999, 2004, 06, 01, DFW, HOU будет сохранен в разделе southwest_sales.

 

Ссылочное секционирование

Если две таблицы связаны друг с другом, можно воспользоваться преимуществом этого отношения, выполнив секционирование этих двух таблиц на основе существующего отношения “родительский–дочерний”. Это отношение задается ограничениями первичного и внешнего ключа. Если две таблицы разделяют отношение “родительский–дочерний”, нужно лишь формально секционировать родительскую таблицу. Тем самым исключается дублирование ключевых столбцов. Любые операции обслуживания разделов на родительской таблице автоматически распространятся на дочернюю таблицу.

Ниже приведен простой пример для прояснения концепции ссылочного секционирования. Таблицы orders и orderitems связаны друг с другом на основе столбца orderid из обеих таблиц. Это отношение зафиксировано ссылочным ограничением orderid_refconstraint. Родительская таблица — orders — секционирована по столбцу OrderDate с использованием схемы диапазонного секционирования. 

CREATE TABLE orders
( order_id NUMBER(12),
order_date DATE,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY(order_id)
)
PARTITION BY RANGE(order_date)
( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
);

Поскольку между таблицами orders и orderitems существует отношение “родительский–дочерний”, для создания секционированной таблицы используется ссылочное секционирование по ограничению orderid_refconstraint для таблицы orderitems.Конструкция FOREIGN KEY (order_id) REFERENCES order (order_id) говорит о том,что таблица orderitems создается со схемой ссылочного секционирования. Таблица orderitems разбивается в соответствии с секционированием по ссылке на родительскую таблицу orders

CREATE TABLE order_items
( order_id NUMBER(12) NOT NULL,
line_item_id NUMBER(3) NOT NULL,
product_id NUMBER(6) NOT NULL,
unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);

Вместе со ссылочным секционированием можно использовать любую стратегию секционирования, за исключением секционирования по интервалам.

При создании таблицы со ссылочным секционированием конструкция partition by reference в операторе CREATE TABLE специфицирует имя ссылочного ограничения,служащего основой для ссылочного секционирования. Необходимо гарантировать, что соответствующее ссылочное ограничение является действительным и включенным.

В приведенном примере таблица orderitems имеет четыре раздела: Q1_2005,Q2_2005, Q3_2005 и Q4_2005. Каждый их четырех разделов содержит записи со значениями столбца order_items, соответствующими заказам в разделе родительской таблицы.

Обратите внимание на следующие характеристики таблицы со ссылочным секционированием.

  • В приведенном примере ссылочного секционирования не используется никаких дескрипторов разделов. Если вы предоставляете дескрипторы разделов, они должны совпадать с количеством разделов или подразделов родительской таблицы. То есть дочерняя таблица будет иметь по одному разделу для каждого соответствующего раздела или подраздела родительской таблицы.
  • Разделам таблицы со ссылочным секционированием можно назначать имена.
  • Если имена разделам не назначаются, то разделы наследуют свои имена от соответствующих разделов родительской таблицы.
  • Можно специфицировать явное табличное пространство для разделов таблицы со ссылочным секционированием.
  • Если табличное пространство не указывается, разделы таблицы со ссылочным секционированием хранятся вместе с соответствующими разделами родительской таблицы, в тех же табличных пространствах.

На заметку! Специфицировать границы разделов таблиц со ссылочным секционированием нельзя.


 

Секционирование на основе виртуальных столбцов

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

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

В следующем примере таблица sales секционируется с использованием виртуального столбца в качестве ключа подраздела. Виртуальный столбец total_amount определен как произведение значений столбцов amount_sold и quantiry_sold.

CREATE TABLE sales
( prod_id NUMBER(6) NOT NULL
, cust_id NUMBER NOT NULL
, time_id DATE NOT NULL
, channel_id CHAR(1) NOT NULL
, promo_id NUMBER(6) NOT NULL
, quantity_sold NUMBER(3) NOT NULL
, amount_sold NUMBER(10,2) NOT NULL
, total_amount AS (quantity_sold * amount_sold)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE(total_amount)
SUBPARTITION TEMPLATE
( SUBPARTITION p_small VALUES LESS THAN (1000)
, SUBPARTITION p_medium VALUES LESS THAN (5000)
, SUBPARTITION p_large VALUES LESS THAN (10000)
, SUBPARTITION p_extreme VALUES LESS THAN (MAXVALUE)
)
(PARTITION sales_before_2007 VALUES LESS THAN
(TO_DATE('01-JAN-2007','dd-MON-yyyy'))
)
ENABLE ROW MOVEMENT
PARALLEL NOLOGGING; 

Обратите внимание, что конструкция ENABLE ROW MOVEMENT гарантирует, что строка может перемещаться из текущего раздела в другой, если вычисление виртуального столбца даст значение, которое не относится к текущему разделу.

Как осуществить секционирование таблиц в базе данных Oracle 

Системное секционирование

Системное секционирование — уникальный метод секционирования таблиц, при котором расположением данных управляет приложение, а не база данных. База данных просто позволяет разделить таблицу на разделы, не имея никакого представления относительно того, что будет содержать каждый из разделов. Приложение управляет тем,что попадает в каждый раздел. При вставке данных в таблицу с системным секционированием необходимо явно специфицировать раздел. Таким образом, если попытаться вставить данные в таблицу с системным секционированием, не указывая раздела, в который эти данные следует поместить, то вставка закончится неудачей.

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

 

Создание таблицы с системным секционированием

В следующем примере показано, как создать таблицу с системным секционированием.

CREATE TABLE test (c1 integer, c2 integer)
PARTITIONED BY SYSTEM
(
PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4
); 

Конструкция PARTITIONED BY SYSTEM, конечно же, указывает на то, что к таблице применяется системное секционирование.

 

Вставка данных

При вставке данных с применением оператора INSERT или MERGE необходимо указать раздел, в который должна быть помещена новая строка. Ниже приведен пример вставки в таблицу с системным секционированием. 

SQL> INSERT INTO test PARTITION (p1) VALUES (4,5);

В этом примере для вставки новых данных специфицирован раздел p1.

 

Удаление и обновление данных

В отличие от операции вставки, удаление и обновление данных в таблице с системным секционированием не требует применения синтаксиса с указанием имени раздела в командах DELETE или UPDATE. Однако Oracle рекомендует все же указывать разделы,чтобы база данных могла исключить ненужные разделы и обойтись без полного сканирования всей таблицы в поисках данных.

Ограничения

Системное секционирование не поддерживает операторов CREATE TABLE AS SELECT и INSERT INTO TABLE AS. В обоих случаях причина в том, что системное секционирование не использует здесь какой-нибудь метод секционирования, и потому отображение между строками и разделами отсутствует.

 

Составное секционирование

Иногда простого секционирования по диапазону, хешу или списку оказывается недостаточно. Для более тонкого контроля над размещением данных можно выполнить дальнейшее секционирование крупной таблицы на подразделы. Oracle предлагает несколько типов составного секционирования. Например, при секционировании “диапазон–хеш” таблица сначала секционируется по диапазонам ключей, а затем для каждого получившегося раздела с использованием схемы хеш-секционирования создаются подразделы. При секционировании “диапазон–список” таблица сначала секционируется по диапазонам ключей, а затем для каждого получившегося раздела применяется списковое секционирование. Кроме того, можно использовать методы составного секционирования “диапазон–диапазон”, “список–список”, “список–хеш” и “список–диапазон”.

 

Секционирование “диапазон–хеш”

Иногда удается секционировать таблицу по диапазонам ключей, но распределение получается не особенно равномерным. Эту схему секционирования можно улучшить,выполнив хеш-секционирование после диапазонного секционирования. Это позволит хранить данные более эффективно, хотя и с усложнением управления.

Секционирование “диапазон–хеш” сочетает в себе лучшие из схем диапазонного и хеш-секционирования. Диапазонное секционирование, как уже было показано, реализовать легко, а хеш-секционирование обеспечивает такие преимущества, как расслоение и параллелизм.

В листинге ниже приведен простой пример создания таблицы, секционированной по методу “диапазон–хеш”.


 

SQL> CREATE TABLE scout_gear (equipno NUMBER,equipname VARCHAR(32),price NUMBER)
2 PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)
3 SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
4 (PARTITION p1 VALUES LESS THAN (1000),
5 PARTITION p2 VALUES LESS THAN (2000),
6 PARTITION p3 VALUES LESS THAN (3000),
7* PARTITION p4 VALUES LESS THAN (MAXVALUE));
Table created.
SQL>

В этом примере таблица scout_gear сначала секционируется по диапазону значений столбца equipno — создаются четыре раздела на основе диапазонов. Эти четыре раздела затем секционируются на подразделы по значению столбца equipname с использованием схемы хеш-секционирования, в результате чего получается 32 подраздела. Обратите внимание на конструкцию SUBPARTITIONS в строке 3.

 

Секционирование “диапазон–список”

В методе секционирования “диапазон–список” таблица сначала секционируется на основе диапазонов ключей. Затем используется списковое секционирование для дальнейшего разбиения первого набора разделов посредством списка дискретных значений. В листинге ниже показан пример создания таблицы, секционированной по методу “диапазон–список”.


 

SQL> CREATE TABLE quarterly_regional_sales
2 (ticket_no NUMBER,
3 sale_year INT NOT NULL,
4 sale_month INT NOT NULL,
5 sale_day DATE,
6 destination_city CHAR(3),
7 start_city CHAR(3))
8 PARTITION BY RANGE(sale_day)
9 SUBPARTITION BY LIST (start_city)
10 (PARTITION q1_2004 VALUES LESS THAN (TO_DATE('1-APR-2004','DD-MON-YYYY'))
11 TABLESPACE t1
12 (SUBPARTITION q12004_northeast_sales VALUES ('NYC','BOS','PEN'),
13 SUBPARTITION q12004_southwest_sales VALUES ('DFW','ORL','HOU'),
14 SUBPARTITION q12004_pacificwest_sales VALUES ('SAN','LOS','WAS'),
15 SUBPARTITION q12004_southeast_sales VALUES ('MIA','CHA','ATL')
16 ),
17 PARTITION q2_2004 VALUES LESS THAN (TO_DATE('1-JUL-2004','DD-MON-YYYY'))
Alapati_1015-3C07.fm Page 288 Friday, October 17, 2008 4:43 PM
18 TABLESPACE t2
19 (SUBPARTITION q22004_northeast_sales VALUES ('NYC','BOS','PEN'),
20 SUBPARTITION q22004_southwest_sales VALUES ('DFW','ORL','HOU'),
21 SUBPARTITION q22004_pacificwest_sales VALUES ('SAN','LOS','WAS'),
22 SUBPARTITION q22004_southeast_sales VALUES ('MIA','CHA','ATL')
23 ),
24 PARTITION q3_2004 VALUES LESS THAN (TO_DATE('1-OCT-2004','DD-MON-YYYY'))
25 TABLESPACE t3
26 (SUBPARTITION q32004_northeast_sales VALUES ('NYC','BOS','PEN'),
27 SUBPARTITION q32004_southwest_sales VALUES ('DFW','ORL','HOU'),
28 SUBPARTITION q32004_pacificwest_sales VALUES ('SAN','LOS','WAS'),
39 SUBPARTITION q32004_southeast_sales VALUES ('MIA','CHA','ATL')
30 ),
31 PARTITION q4_2004 VALUES LESS THAN (TO_DATE('1-JAN-2005','DD-MON-YYYY'))
32 TABLESPACE t4
33 (SUBPARTITION q42004_northeast_sales VALUES ('NYC','BOS','PEN'),
34 SUBPARTITION q42004_southwest_sales VALUES ('DFW','ORL','HOU'),
35 SUBPARTITION q42004_pacificwest_sales VALUES ('SAN','LOS','WAS'),
36 SUBPARTITION q42004_southeast_sales VALUES ('MIA','CHA','ATL')
37 )
38* );
Table created.
SQL>

Приведенный оператор создает 16 подразделов в таблице с секционированием “диапазон–список”, причем в каждом табличном пространстве (t1, t2, t3, t4) будет содержаться по 4 подраздела. Всякий раз, когда в таблицу quarterly_regional_sales вставляется строка данных, Oracle сначала проверяет, попадает ли значение столбца секционирования для строки в определенный диапазон разделов. Затем Oracle отображает строку на подраздел внутри раздела, отображая значение столбца подраздела на соответствующий подраздел на основе значений из списка подразделов. Например,строка со значениями столбцов (9999, 2004, 10, 1, 'DAL', 'HOU') отображается на подраздел q32004_southwest_sales.

 

Таблицы с составным секционированием “интервал–список”

Для создания таблицы со списковыми подразделами необходимо использовать шаблон подразделов. В противном случае можно будет создать только один подраздел по умолчанию для каждого интервального раздела.

В примере, показанном в листинге ниже, таблица sales сначала секционируется по интервалу значений столбца time_id за каждый день. Затем таблица подвергается дальнейшему секционированию по списку значений столбца channel_id.


 

CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY RANGE(amount_sold)
SUBPARTITION TEMPLATE
( SUBPARTITION p_low VALUES LESS THAN (1000)
, SUBPARTITION p_medium VALUES LESS THAN (4000)
, SUBPARTITION p_high VALUES LESS THAN (8000)
, SUBPARTITION p_ultimate VALUES LESS THAN (maxvalue)
)
( PARTITION before_2000 VALUES LESS THAN
(TO_DATE('01-JAN-2000','dd-MON-yyyy')))
PARALLEL;

 

Составное секционирование “интервал–диапазон”

Как и в случае дополнительного спискового секционирования, если планируется создать диапазонные подразделы для последующего включения в интервальные разделы таблицы, секционированной по схеме “интервал–диапазон”, потребуется использовать шаблон подразделов. Без этого шаблона для каждого интервального раздела можно будет создать только по одному диапазонному подразделу с верхней границей MAXVALUE.

Пример, приведенный в листинге ниже, иллюстрирует создание таблицы с составным секционированием по методу “интервал–диапазон”. Интервальные разделы создаются по дням на основе столбца time_id, а диапазонные подразделы — на основе столбца amount_sold.


 

CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY LIST (channel_id)
SUBPARTITION TEMPLATE
( SUBPARTITION p_catalog VALUES ('C')
, SUBPARTITION p_internet VALUES ('I')
, SUBPARTITION p_partners VALUES ('P')
, SUBPARTITION p_direct_sales VALUES ('S')
, SUBPARTITION p_tele_sales VALUES ('T')
)
( PARTITION before_2000 VALUES LESS THAN TO_DATE('01-JAN-2000','dd-MON-yyyy')))
PARALLEL;

 

Операции обслуживания разделов

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

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

  • диапазонные и списковые разделы не могут быть объединены;
  • хеш-разделы не могут быть уничтожены, разделены или объединены;
  • только списковые разделы допускают модификацию разделов добавлением и удалением значений, включаемых в раздел.

 

Добавление разделов

К таблице ticket_sales можно добавить новый раздел для включения нового квалификатора, как показано ниже:

SQL> ALTER TABLE ticket_sales
ADD PARTITION sales_quarter5 VALUES LESS THAN
(TO_DATE('1-APR-2005','DD-MON-YYYY'))
TABLESPACE ticket_sales05; 

В этом примере добавляется новый раздел для первого квартала 2005 г., который следует за последним кварталом в исходной таблице.

 

Расщепление раздела

С помощью конструкции ADD PARTITION добавляются разделы к верхнему пределу существующей таблицы. Но что если нужно вставить новые данные в середину таблицы? Что если существующий раздел стал чересчур большим, и хотелось бы иметь разделы поменьше? Расщепление раздела обеспечивает распределение данных из существующего раздела между двумя новыми разделами.

Для разбиения раздела используется конструкция SPLIT PARTITION: 

SQL> ALTER TABLE ticket_sales
SPLIT PARTITION ticket_sales01 AT (2000) INTO
(PARTITION ticket_sales01A, ticket_sales01B);

 

Объединение разделов

Для комбинирования содержимого двух соседних разделов служит конструкция MERGE PARTITION. Например, объединить первые два раздела таблицы ticket_sales можно следующим образом: 

SQL> ALTER TABLE ticket_sales
MERGE PARTITIONS ticket_sales01, ticket_sales02 INTO PARTITION
ticket_sales02;

 

Переименование разделов

Разделы переименовываются точно так же, как таблицы, например: 

SQL> ALTER TABLE
RENAME PARTITION fight_sales01 TO quarterly_sales01;

 

Преобразование разделов

Конструкция EXCHANGE PARTITION позволяет преобразовать обычную несекционированную таблицу в раздел другой секционированной таблицы, например: 

SQL> ALTER TABLE ticket_sales
EXCHANGE PARTITION ticket_sales02 WITH ticket_sales03;

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

 

Удаление разделов

Удаление разделов выполняется легко, если в них не содержится никаких данных.

Вот пример:

 
SQL> ALTER TABLE ticket_sales
DROP PARTITION ticket_sales01;

Если в удаляемом разделе имеются какие-то данные, следует соблюдать осторожность в использовании конструкции UPDATE GLOBAL INDEXES с приведенным выше синтаксисом DROP PARTITION. В противном случае все глобально созданные индексы станут недействительными. Локальные индексы останутся, поскольку они отображаются только на затронутые разделы.

 

Перераспределение разделов

Таблицы с хеш-секционированием и списковым секционированием позволяют перераспределять их разделы. Перераспределение (содержимого) разделов (coalescing partitions) позволяет сократить количество разделов. В таблице с хеш-секционированием конструкция COALESCE PARTITION перераспределяет данные удаленного раздела по существующим разделам на основе хеш-функции. База данных выбирает указанный раздел для перераспределения и уничтожает его после реорганизации данных по остальным разделам. При секционировании по методу “диапазон–хеш” можно перераспределять разделы.

Ниже приведен пример перераспределения содержимого таблицы с хеш-секционированием, сокращающего количество разделов на единицу: 

SQL> ALTER TABLE ticket_sales
COALESCE PARTITION;

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


 

Вас заинтересует / Intresting for you:

THREADED_EXECUTION в Oracle 12...
THREADED_EXECUTION в Oracle 12... 3685 просмотров Андрей Васенин Thu, 01 Nov 2018, 12:58:50
Секционирование таблиц в Oracl...
Секционирование таблиц в Oracl... 50296 просмотров Валерий Михеичев Mon, 01 Feb 2021, 15:11:17
Секционирование Oracle 12c
Секционирование Oracle 12c 2369 просмотров Дэн Sun, 07 Oct 2018, 06:04:37
Хронология активных сеансов в ...
Хронология активных сеансов в ... 2767 просмотров Antoniy Tue, 21 Nov 2017, 13:18:46
Войдите чтобы комментировать

apv аватар
apv ответил в теме #9027 5 года 10 мес. назад
Системное и четкое изложение предметной области. Секционирование таблиц весьма актуальная тема не только для баз данных Oracle, но и для других СУБД. Данных много, очень много! Их нужно как-то упорядочивать, хранить и эффективно ими управлять!