Oracle Database 11g： DBAと開発者のための主要な新機能 著者：Arup Nanda

完全なパーティション化

Oracle Database 11gでのパーティション化の選択は、ほぼ無制限です。

Oracle Database 11gのダウンロード

"分割統治" - これこそ、Oracle データベースのパーティション化を説明するのにもっとも適した言葉です。バージョン8以降、表や索引を複数のセグメントにパーティション化して、異なる表領域に配置できるようになりました。表は論理要素的には一つですが、個々のパーティションは別々のセグメントとして保存されます。これによって、データの操作が容易になります。

Oracle Database 11gの参照パーティション化、インターバル・パーティション化、仮想列のパーティション化、拡張コンポジット・パーティション化などの拡張機能によって、無制限のパーティション化設計を実現して管理性を高めることができます。

パーティション化の基本やパーティション化列またはパーティション化スキームの選択に関して学習する際は、2006年9/10月発行の『Oracle Magazine』に掲載されている 筆者の記事をご覧ください。

拡張コンポジット・パーティション化

Oracle8i Databaseで導入されたパーティション化スキームであるコンポジット・パーティション化を使用すると、パーティションからサブパーティションを作成できるので、表をさらに細分化できます。ただし、このリリースでは、レンジ・パーティション化された表をハッシュ・サブパーティション化できるだけです。さらにOracle9i Databaseの拡張コンポジット・パーティション化では、レンジ-リスト・サブパーティション化も可能です。

これらのパーティション化スキームは、ほとんどの状況に対応しています。たとえば、パーティション化の対象となる2つの特別な列（消費税を計算するための状態を示した2桁のコードを格納するstate_codeと製品を識別する3桁の数値のproduct_code）を含む多くの列を持つSALES表を利用します。ユーザーは、2つの列を同じようにフィルタリングして表への問合せを実行します。アーカイブ要件もこの2つの列に基づきます。パーティション化決定の原則を適用すると、この2つの列はパーティション化キーとして最適な選択肢となります。

Oracle Database 11gを使用すると、問題を簡単に解決できます。このリリースは、レンジ-ハッシュおよびレンジ-リスト・コンポジット・パーティション化のみに制限されません。つまり、選択がほぼ無制限になります。あらゆる組合せのコンポジット・パーティションを作成できます。

この例の場合、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)
   )
)

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 11gでは、次のタイプのコンポジット・パーティションを作成できます。

• Range-range
• Range-hash
• Range-list
• List-range
• List-hash
• List-list

参照パーティション化

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')
);

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
);

Oracle Database 11gの参照パーティション化と呼ばれる新しい機能を使用すればこれが可能になります。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);

SALES表のパーティションでパーティション境界を確認する場合

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES';

PARTITION_NAME  HIGH_VALUE
--------------- -------------------------------
PA
PB

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

パーティションの必要性を検出して自動的にパーティションを作成できれば、それは非常に効率的です。Oracle Database 11gのインターバル・パーティション化という機能を使用すれば、可能になります。ここではパーティションと境界は定義せず、各パーティションの境界の間隔を定義します。インターバル・パーティション化の例は、以下のとおりです。

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'))
);
                              

SQL> insert into sales6 values (1,'01-jun-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

ここで、2007年5月1日などの最大値よりも低い値を入力する場合を仮定します。パーティション間隔が月単位なので、固有のパーティションが作成されるはずです。

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

特定の表領域にパーティションを格納する場合、store in句を使用して実行できます。

interval (numtoyminterval(1,'MONTH'))
store in (TS1,TS2,TS3)

アプリケーション開発者が特定のパーティションを指定するにはどうすればよいでしょう。名前を取得できるかもしれませんが、エラーが発生する可能性があります。特定のパーティションへのアクセスを容易にするため、Oracle Database 11gはパーティション指定について、新しい構文を提供しています。

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

  SALES_ID SALES_DT
---------- ---------
         1 01-MAY-07

この方法で表を作成した後、DBA_PART_TABLESビューのPARTITIONING_TYPE列には、INTERVALが表示されます。

システム・パーティション化

まれではあるものの、想定されるユースケースです。論理的な方法でパーティション化できない表があるとします。結果として大規模な表になり、拡張索引メンテナンスやその他の操作が必要になるなどの問題が生じます。

このため、開発者は解決手段を提供します。表をとにかくパーティション化できるならば、開発者は知的な方法によるパーティションへの書込みを約束します。これにより、アプリケーションは、特定のレコードを格納するパーティションを制御できます。DBAは、そのパーティションを定義する必要があります。次に例を示します。

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

SQL> select partitioning_type
  2  from user_part_tables
  3  where table_name = 'SALES3';

PARTITION
---------
SYSTEM

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES3';

PARTITION_NAME HIGH_VALUE
-------------- ---------------------
P1               
P2

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;

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

論理的な方法で表をパーティション化できない場合、システム・パーティション化によって大きな利点が得られます。パーティション化の利点を活用して、開発者はレコードを格納するパーティションを自由に決定できるのです。

単一パーティションの表領域のトランスポート

CT、NYなどの複数のパーティションを含むSALES5表を使用するとします。

SQL> select partition_name, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'SALES5';


PARTITION_NAME TABLESPACE_NAME
-------------- ---------------
CT             TS1
NY             TS2

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

SQL> drop table scott.sales5;
 
Table dropped.
 
SQL> drop tablespace ts1 including contents;
 
Tablespace dropped.

これは、Oracle Database 11gのpartition_optionsと呼ばれるData Pump Importの新しいコマンドライン・オプションによって可能になります。departition値を指定すると、Oracle Data Pumpは、エクスポートされたパーティションから新しい表を作成します。この方法によってパーティションを"中止"する意味合いから、departition（パーティション解除）という名前になっています。次に動作を確認します。

$ 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> select segment_name
  2  from dba_segments
  3  where tablespace_name = 'TS1';
 
SEGMENT_NAME
-----------------
SALES5_CT

仮想列のパーティション化

SQL> desc sales
 Name                                      Null?    Type
 ----------------------------------------- -------- ------
 SALES_ID                                  NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 SALES_AMT                                          NUMBER

売上に基づいた消去およびアーカイブできるパーティション化スキームでこの表をパーティション化するとします。売上には4つのカテゴリがあります。

Oracleデータベースの以前のバージョンでは、sale_categoryという新しい列を表に持ち、列を移入するためにトリガーを使用しました。しかし、新しい列によって、トリガー動作による別のパフォーマンスの問題が発生する可能性がありました。

仮想列と呼ばれるOracle Database 11gの新しい機能を使用すると、表に格納されずに実行時に計算される列を作成できます。この列でパーティション化することも可能です。この機能によって、この表のパーティション化が容易になります。

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.

SQL> select * from sales partition (p_low);

  SALES_ID    CUST_ID  SALES_AMT SALE_C
---------- ---------- ---------- ------
         1          1        100 LOW

仮想列のパーティション化は、列自体が存在しなくてもビジネス用の適切なパーティションを作成できます。ここでは、非常に簡単な計算の仮想列を使用しましたが、複雑にすることもできます。この場合、仮想列のパーティション化の価値はさらに高まります。

Partition Advisor

結論

• 参照パーティション化によって、すべての子表に列が存在しなくても、同じデータベースで関連する表を同一レベル・パーティション化できます。
• インターバル・パーティション化は、間隔を定義した後でOracleデータベースが常に保守する非常に優れた自動機能を実現します。
• レンジ-レンジ、リスト-レンジ、リスト-ハッシュ、リスト-リストの拡張コンポジット・パーティション化は、効率的なパーティション化の選択と管理性の新しい可能性を提供します。
• Oracle Data Pumpを使用すると、単一パーティションをトランスポートしてプラグインできます。アーカイブおよび保持に非常に便利な機能です。
• 仮想列のパーティション化によって、ビジネス・フローを模倣する最適なパーティション化戦略を設計できます。

"Oracle Database 11g：DBAと開発者のための主要な機能"ホームページに戻る Arup Nanda ( arup@proligence.com) は、Oracle データベーステクノロジのあらゆる分野で12年以上の経験を持つ優秀なOracle DBAであり、2003年の『Oracle Magazine』で"DBA of the Year"に選ばれました。Arupは、Oracle関連のイベントの講演や雑誌の寄稿を頻繁に行っている Oracle ACE Directorです。また、『 RMAN Recipes for Oracle Database 11g: A Problem Solution Approach』を含む4冊の書籍を共同で執筆しています。