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

В Oracle Database 11 g выбор способа секционирования теперь практически не ограничен.

"Разделяй и властвуй " - этот фигуральный принцип никогда не был проиллюстрирован лучше, чем в возможностях секционирования в Oracle Database. Начиная с версии 8, таблицу или индекс можно разделить на несколько секций, которые затем поместить в различные табличные пространства. Таблица по-прежнему является логической сущностью, в то время как отдельные секции хранятся как отдельные сегменты, что позволяет легко манипулировать данными.

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

Расширенное смешанное секционирование

При смешанном секционировании - эта схема известна с Oracle8i Database - можно создавать подсекции секций, позволяя ещё больше измельчать таблицу. Однако в этой версии можно было создавать подсекции таблиц с диапазонными секциями только хэш-методом. В Oracle9i смешанное секционирование было расширено до подсекций диапазон-список.

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

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

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

В Oracle Database 11g можно решить проблему очень легко. Эта версия не ограничена смешанным секционированием по схеме диапазон-хэш или диапазон-список. Напротив, выбор совершенно неограничен, создавать можно смешанные секции в любых комбинациях.

В этом примере можно выбрать LIST- секционирование таблицы по product_code, так как этот столбец имеет более дискретные значения и затем создать подсекции по state_code тоже по списку.  Демонстрационный код ниже показывает, как это сделать:

create table sales

(

   sales_id     number,

   product_code number,

   state_code   varchar2(2)

)

partition by list (product_code)

subpartition by list (state_code)

(

   partition p101 values (101)

   (

      subpartition p101_ct values ('CT'),

      subpartition p101_ny values ('NY'),

      subpartition p101_def values (default)

   ),

   partition p201 values (201)

   (

      subpartition p201_ct values ('CT'),

      subpartition p201_ny values ('NY'),

      subpartition p201_def values (default)

   )

)

Варианты не ограничиваются теми, что здесь показаны. Можно также создать смешанные секции LIST-RANGE. Предположим, в примере выше код продукта не дискретный, а входит в диапазон. Вы можете создать секции по списку по столбцу state_code и затем подсекции по product_code. Вот демонстрационный код, который это делает.

create table sales1

(

   sales_id     number,

   product_code number,

   state_code   varchar2(2)

)

partition by list (state_code)

subpartition by range (product_code)

(

   partition CT values ('CT')

   (

      subpartition ct_100 values less than (101),

      subpartition ct_200 values less than (201)

   ),

   partition NY values ('NY')

   (

      subpartition NY_100 values less than (101),

      subpartition NY_200 values less than (201)

   )

)

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

В итоге, можно создавать следующие типы смешанных секций, возможные в Oracle Database 11 g :

• Диапазон-диапазон
• Диапазон-хэш
• Диапазон-список
• Список-диапазон
• Список-хэш
• Список-список

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

Вот типичная проблема в проектировании схем секционирования: не все таблицы имеют одинаковые столбцы для секционирования. Предположим, вы создаёте систему продаж с двумя простыми таблицами, sales и customers:

create table customers

(

   cust_id   number primary key,

   cust_name varchar2(200),

   rating    varchar2(1) not null

)

partition by list (rating)

(

   partition pA values ('A'),

   partition pB values ('B')

);

Таблица sales создана, как показано ниже. Это подчинённая таблица для таблицы customers.

create table sales

(

   sales_id    number primary key,

   cust_id     number not null,

   sales_amt   number,

   constraint  fk_sales_01

    foreign key (cust_id)

    references customers

);

В идеале надо бы секционировать таблицу sales так же, как таблицу customers: секции по списку значений столбца rating. Однако тут возникает серьёзная проблема: в таблице sales нет столбца rating! Поэтому как секционировать её по несуществующему столбцу?

В Oracle Database 11 g можно использовать новую возможность Reference Partitioning (Ссылочное секционирование). Вот пример, показывающий, как применить его к таблице sales:

create table sales

(

   sales_id    number primary key,

   cust_id     number not null,

   sales_amt   number,

   constraint  fk_sales_01

    foreign key (cust_id)

    references customers

)

partition by reference (fk_sales_01);

В этом случае создаются секции, идентичные таблице-мастеру, customers. Заметьте, что столбца rating нет, хотя таблица секционирована по этому столбцу. В выражении partition by reference (fk_sales_01) указано название внешнего ключа в описании секции. Oracle Database 11 g показывает, что секционирование выполнено по схеме таблицы-мастера - в данном случае, customers. Заметьте, что ограничение целостности по столбцу cust_id. - NOT NULL; это требование к ссылочному секционированию.

Проверим границы секций таблицы sales:

SQL> select partition_name, high_value

 2 from user_tab_partitions

 3 where table_name = 'SALES';

PARTITION_NAME HIGH_VALUE

--------------- -------------------------------

PA

PB

Значение high value пусто, а это означает, что границы наследуются из таблицы-мастера. Секции имеют такие же названия, как и таблица-мастер. Тип секционирования можно проверить, выполнив запрос по представлению user_part_tables. Специальный столбец ref_ptn_constraint_name показывает название ограничения целостности для внешнего ключа.

SQL> select table_name, partitioning_type, ref_ptn_constraint_name

 2 from user_part_tables

 3 where table_name in ('CUSTOMERS','SALES');

TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME

------------------------------ --------- --------------------------

CUSTOMERS LIST

SALES REFERENCE FK_SALES_01

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

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

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

create table sales6

(

   sales_id    number,

   sales_dt    date

)

partition by range (sales_dt)

(

   partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd')),

   partition p0702 values less than (to_date('2007-03-01','yyyy-mm-dd'))

);

ORA-14400: inserted partition key does not map to any partition

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

Не проще ли будет, если бы Oracle как-нибудь автоматически распознал необходимость новых секций и создал их? Oracle Database 11 g это умеет для Interval Partitioning (интервального секционирования). В примере ниже описаны не секции, не их границы, а только интервал, который описывает границы каждой секции. Вот демонстрационный пример интервального секционирования:

create table sales6

(

 sales_id number,

 sales_dt date

)

partition by range (sales_dt)

interval (numtoyminterval(1,'MONTH')) 

(

 partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))

);

Заметьте: интервал следует за интервалом. Это из инструкции Oracle по созданию интервалов для каждого месяца. Создаётся также начальная секция p0701, для января 2007. Теперь, предположим, что вставляется запись за июнь 2007:

SQL> insert into sales6 values (1,'01-jun-07');

1 row created.

Oracle ошибку не возвращает; наоборот, он успешно выполняет предложение. И где же тогда вставленная запись? Секция p0701 не может содержать такую запись, а секция за июнь 2007 не описывалась. Однако проверим секции таблицы на этот раз:

SQL> select partition_name, high_value

 2 from user_tab_partitions

 3 where table_name = 'SALES6';

PARTITION_NAME HIGH_VALUE

--------------- ----------------------------------------------------------------

P0701 TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C

 ALENDAR=GREGORIA

SYS_P41 TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C

 ALENDAR=GREGORIA

Заметьте, что секция SYS_P1 с верхним значением 1 июля 2007 будет накапливать данные до конца июня. Эта секция создана динамически Oracle и имеет системное сгенерированное название.

Теперь предположим, что вводится значение меньше максимального, например 1 мая 2007. Оно идеально соответствует его собственной секции, так как интервал секции, это месяц.

SQL> insert into sales6 values (1,'01-may-07');

1 row created.

SQL> select partition_name, high_value

 2 from user_tab_partitions

 3 where table_name = 'SALES6';

PARTITION_NAME HIGH_VALUE

--------------- ----------------------------------------------------------------

P0701 TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C

 ALENDAR=GREGORIA

SYS_P41 TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C

 ALENDAR=GREGORIA

SYS_P42 TO_DATE(' 2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C

 ALENDAR=GREGORIA

Заметьте, что новая секция, SYS_P42, имеет верхнюю границу 1 июня - такая секция может содержать данные за май 2006. Эта секция создана делением секции SYS_P41 (за июнь). Таким образом, Oracle автоматически создаёт и управляет секциями, когда описана схема интервального секционирования. Если секции необходимо создавать в отдельных табличных пространствах, следует использовать выражение store in:

interval (numtoyminterval(1,'MONTH'))

store in (TS1,TS2,TS3)

тогда секции сохраняются в табличных пространствах TS1, TS2 и TS3 по очереди по кругу.
Как разработчик приложения может обратиться к какой-либо секции? Один из известных способов, по названию, может быть невозможным, и даже, если вы знаете, часто приводящим к ошибкам. Для обеспечения доступа к некоторой секции Oracle Database 11 g предлагает новый синтаксис запросов:

SQL> select * from sales6 partition for (to_date('15-may-2007','dd-mon-yyyy'));

 SALES_ID SALES_DT

---------- ---------

 1 01-MAY-07

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

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

Хотя Oracle предполагает, что лишь немногие будут использовать на практике эту возможность, я хочу описать её, потому что очень уж она хороша.

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

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

create table sales3

(

 sales_id number,

 product_code number,

 state_code number

)

partition by system

(

 partition p1 tablespace users,

 partition p2 tablespace users

);

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

SQL> select partition_name

 2 from user_segments

 3 where segment_name = 'SALES3';

PARTITION_NAME

------------------------------

P1

P2

При создании локального индекса, он секционируется таким же способом.

SQL> create index in_sales3_state on sales3 (state_code) local;

Index created.

SQL> select partition_name

 2 from user_segments

 3 where segment_name = 'IN_SALES3_STATE';

PARTITION_NAME

------------------------------

P1

P2

Тип секционирования можно проверить по user_part_tables:

SQL> select partitioning_type

 2 from user_part_tables

 3 where table_name = 'SALES3';

PARTITION

---------

SYSTEM

Результат показывает SYSTEM, что, конечно же, обозначает системное секционирование. Отметим то обстоятельство, что столбец high_value имеет значение NULL для таблиц такого типа.

SQL> select partition_name, high_value

 2 from user_tab_partitions

 3 where table_name = 'SALES3';

PARTITION_NAME HIGH_VALUE

-------------- ---------------------

P1

P2

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

SQL> insert into sales3 values (1,101,1);

insert into sales3 values (1,101,1)

 *

ERROR at line 1:

ORA-14701: partition-extended name or bind variable must be used for DMLs on

tables partitioned by the System method

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

SQL> insert into sales3 partition (p1) values (1,101,1);

1 row created.

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

SQL> delete sales3 where state_code = 1;

Oracle должен просканировать все секции, чтобы увидеть, где расположена строка. Чтобы этого избежать, следует написать так:

SQL> delete sales3 partition (p1) where state_code = 1;

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

Табличное пространство транспортируется с одной секцией

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

Предположим, что есть таблица SALES5 с секциями CT, NY и т.д.

SQL> select partition_name, tablespace_name

 2 from user_tab_partitions

 3 where table_name = 'SALES5';

PARTITION_NAME TABLESPACE_NAME

-------------- ---------------

CT TS1

NY TS2

Теперь необходимо переместить секцию CT с помощью команды, показанной ниже:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.4.0 - Beta

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Oracle Database Vault options

Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA tables=scott.sales5:ct transportable=

 always directory=data_pump_dir dumpfile=p_ct.dmp

Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

****************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

 /home/oracle/oracle/admin/PROBE2/dpdump/p_ct.dmp

******************************************************************************

Datafiles required for transportable tablespace TS1:

 /home/oracle/oradata/PROBE2/PROBE2/ts1_01.dbf

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 16:05:55

Теперь можно взять два файла - p_ct.dmp и ts1_01.dmp - в другой системе и попытаться подключить их к базе данных. Для изучения давайте попробуем подключить их к той же самой базе данных. Сначала нужно удалить таблицу, а затем табличное пространство ts1.

SQL> drop table scott.sales5;

Table dropped.

SQL> drop tablespace ts1 including contents;

Tablespace dropped.

Теперь подключим табличное пространство к базе данных. Тут, однако, возникает небольшая проблема: таблица sales5 больше не существует, а экспортирована была только одна секция (ct), а не вся таблица. И как же теперь импортировать эту секцию несуществующей таблицы?

В Oracle Database 11 g новая опция командной строки Data Pump Import, которая называется partition_options, делает это возможным. Если указать значение departition, Data Pump создаст новую таблицу из экспортированной секции. По ходу дела этот метод "удаляет" секцию, поэтому он соответственно называется десекционированием. Давайте посмотрим, как он работает.

 $ impdp partition_options=departition dumpfile=p_ct.dmp   transport_datafiles='/home/oracle/oradata/PROBE2/PROBE2/ts1_01.dbf'  

Import: Release 11.1.0.4.0 - Beta on Sunday, 10 June, 2007 21:58:08

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.4.0 - Beta

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Oracle Database Vault options

Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_04" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_04": /******** AS SYSDBA partition_options=

 departition dumpfile=p_ct.dmp transport_datafiles=/home/oracle/oradata/PROBE2/PROBE2/ts1_01.dbf

Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK

Job "SYS"."SYS_IMPORT_TRANSPORTABLE_04" successfully completed at 21:58:23

Этот SQL создаёт таблицу sales5_ct, которая ни что иное, как секция ct таблицы SALES5, экспортированной  ранее с перемещаемым табличным пространством. Название таблицы, как можно видеть, это комбинация названий таблицы и секции. Наличие соответствующего сегмента можно увидеть в представлении DBA_SEGMENTS.

SQL> select segment_name

 2 from dba_segments

 3 where tablespace_name = 'TS1';

SEGMENT_NAME

-----------------

SALES5_CT

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

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

Давайте рассмотрим другую распространённую проблему. В таблице sales есть следующие столбцы:

SQL> desc sales

 Name                                      Null?    Type

 ----------------------------------------- -------- ------

 SALES_ID                                  NOT NULL NUMBER

 CUST_ID                                   NOT NULL NUMBER

 SALES_AMT                                          NUMBER

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

Эту таблицу необходимо секционировать по столбцу sale_category, но вот проблема: столбца sale_category нет. Он в основном зависит от столбца sale_amt. И как же можно секционировать эту таблицу?

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

В Oracle Database 11 g , новая возможность Virtual Columns (виртуальных столбцов) позволяет создать столбец, который не хранится в таблице, а вычисляется во время работы. По этому столбцу можно также выполнять секционирование. Использование этой возможности слегка "покачает" секционирование этой таблицы.

create table sales

(

   sales_id      number,

   cust_id       number,

   sales_amt     number,

   sale_category varchar2(6)

   generated always as

   (

      case

         when sales_amt <= 10000

            then 'LOW'

         when sales_amt > 10000

            and sales_amt <= 100000

            then case

               when cust_id < 101 then 'LOW'

               when cust_id between 101 and 200 then 'MEDIUM'

               else 'MEDIUM'

            end

         when sales_amt > 100000

            and sales_amt <= 1000000

            then case

               when cust_id < 101 then 'MEDIUM'

               when cust_id between 101 and 200 then 'HIGH'

               else 'ULTRA'

            end

         else 'ULTRA'

      end

    ) virtual

)

partition by list (sale_category)

(

   partition p_low values ('LOW'),

   partition p_medium values ('MEDIUM'),

   partition p_high values ('HIGH'),

   partition p_ultra values ('ULTRA')

)

SQL> insert into sales (sales_id,cust_id,sales_amt) values (1,1,100);

1 row created.

SQL> insert into sales (sales_id,cust_id,sales_amt) values (2,1,1500);

1 row created.

SQL> insert into sales (sales_id,cust_id,sales_amt) values (3,102,1500);

1 row created.

SQL> insert into sales (sales_id,cust_id,sales_amt) values (4,102,10000);

1 row created.

SQL> commit;

Commit complete.

Заметьте, что значение sale_category не вводилось. А если проверить записи в секции p_low, там будет корректная запись:

SQL> select * from sales partition (p_low);

  SALES_ID    CUST_ID  SALES_AMT SALE_C

---------- ---------- ---------- ------

         1          1        100 LOW

Запись помещена в соответствующую секцию.

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

Помощник по секционированию

Надо полагать, что больше всего споров при проектировании секционирования возникает при выборе схемы секционирования и столбца (-ов) секционирования. Эта задача пусть лучше остаётся бывалым профессионалам, занимающимся экстенсивным анализом объёма работы, и даже они могут не сделать это правильно. Получить помощь в Oracle Database 11 g можно в виде нового помощника Partition Advisor (помощник по секционированию), который анализирует данные и примеры доступа к предполагаемым схемам секционирования. Об этом инструменте можно больше прочитать в его руководстве по установке.

Заключение

Секционирование всегда было одним из наиболее полезных средств, а в Oracle Database 11 g оно становится ещё поле полезным:

• Ссылочное секционирование позволяет синхронно разделать на секции связанные таблицы одной базы данных, даже если столбцов нет в подчинённых таблицах.
• Интервальное секционирование реализовано так, как было весьма желательно для принципа сделал-и-забыл - описывается интервал и в дальнейшем Oracle берёт на себя заботу по поддержке.
• Расширения смешанного секционирования до диапазон-диапазон, список-диапазон, список-хэш и список-список демонстрируют новые возможности для большей свободы выбора секционирования и управления им.
• Data Pump теперь позволяет перемещать и подключать единственную секцию, возможность, которая очень полезна в архивировании и хранении.
• Наконец, можно проектировать наилучшую из возможных стратегий секционирования, которая отражает бизнес-потоки путём секционирования по виртуальным столбцам.