皆さんこんにちは、だいぶ涼しくなってきましたが体調はいかがでしょうか。 今回は、前回の索引に続いてパーティションの設計方法などについて具体的な例などを使用して説明しようと思います。これも第10回で説明しましたが、まだ難しいと思っている方が多いと思いますので、参考にして下さい。
■ 1. パーティションの決め方
まずは、パーティションの決め方について説明しましょう。 索引とは異なりデメリットが少ないので、できれば使用して欲しい機能ですが、どのように設計すれば良いか分からない方も多いと思います。特に、索引とどのように使い分けるのかなどは難しいと感じているのではないでしょうか。パーティションの効果は次の二つになり、どちらを重視するかが大事になります。
・パフォーマンス（パーティション・プルーニング、パーティション・ワイズ結合）
・管理性及び可用性（実行又は影響の範囲がパーティション単位になることで向上）
このコラムはパフォーマンス講座ですから、パフォーマンスをメインに説明しましょう。第10回では、どのような列でパーティションを作成すればよいかまでは説明できていなかったと思いますので、ここでパーティションを決めるための注意点について説明します。
（１）作成する列の候補
どの列の条件で頻繁に検索を行うか（またはどの列の条件で検索するのが重要か）を調べて、次の①～⑤のように検討をしていきます。ただし、更新が多い列は、パーティション移動のオーバーヘッドが発生するので、そのような列はできるだけパーティション・キーに使用しないようにします。
① 最も一般的なレンジ・パーティションを検討します。時間に依存している表（時系列データの表）には、その列のアクセス単位やメンテナンス単位からレンジ・パーティションを作成します。定期的に大量のロード（EXCHANGE PARTITION）をするようなデータでなければ、インターバル・パーティション（自動的に作成するレンジ・パーティション）を使用すると、管理が簡単になります（EXCHANGE PARTITIONは、存在しないパーティションには行えません）。
② 次に一般的なリスト・パーティションを検討します。カーディナリティの低い連続性のない列や条件が複数の値が対象の場合（INリストやOR演算子を使用している場合）には、それぞれの値または複数の値で別パーティションにするリスト・パーティションを作成します。
③ 最後に競合ポイントの緩和やパーティション・ワイズ結合のためにハッシュ・パーティションを検討します。OLTP環境でブロック競合などが多く発生している場合には、主キーでハッシュ・パーティションを作成します。ハッシュ結合の改善には、結合列でハッシュ・パーティションを作成してパーティション・ワイズ結合を行います。
④ 結合して絞り込むような（スター・スキーマなどの）列の場合には、結合列のパーティション、またはリファレンス・パーティションを作成します（「結合列のパーティション」を参照）。
⑤ 最後に、1つの列に絞れない場合やメンテナンスも考慮する必要がある場合には、コンポジット・パーティションを検討すると良いでしょう。
（２）パーティション・キー列の注意点
パーティション・プルーニングさせるためには、パーティション・キー列に多少注意が必要です。 索引と同様にパーティション・キー列で演算をする（関数を使用する）とパーティション・プルーニングが行われませんので注意して下さい。DATEデータ型を比較条件で使用する場合に、以下のようにパーティション列c1にTO_CHAR関数を指定するとパーティション・プルーニングされません（アクセスされた開始パーティション番号”Pstart”と終了パーティション番号”Pstop”に、すべてのパーティション”1～12”が出力されています）。
SQL> SELECT * FROM tab01 WHERE TO_CHAR(sdate,'YYYY/MM/DD') ='2012/08/01'; 実行計画 --------------------------------------<途中省略>------------------ | Id | Operation | Name | | Pstart| Pstop | --------------------------------------<途中省略>------------------ | 0 | SELECT STATEMENT | | | | | | 1 | PARTITION RANGE ALL | | | 1 | 12 | |* 2 | TABLE ACCESS FULL | TAB01 | | 1 | 12 |
また、データ型の変換が行われる場合は動的パーティション・プルーニングになりますので注意して下さい（動的プルーニングは実行計画の作成時にアクセスするパーティションを決定できないので、静的プルーニングより効率が悪くなります）。DATEデータ型を比較条件で使用する場合に、以下のように指定するとデータ型の暗黙変換が行われるため動的パーティション・プルーニングになります（PstartとPstopにKEYが出力されます）。
SQL> SELECT * FROM tab01 WHERE sdate = '2012/08/01'; 実行計画 -----------------------------------------<途中省略>------------------ | Id | Operation | Name | | Pstart| Pstop | -----------------------------------------<途中省略>------------------ | 0 | SELECT STATEMENT | | | | | | 1 | PARTITION RANGE SINGLE | | | KEY | KEY | |* 2 | TABLE ACCESS FULL | TAB01 | | KEY | KEY |
静的パーティション・プルーニングをさせるためには、以下のようにパーティション列のデータ型と一致するようにデータ型を明示的に変換する必要があります。以下は静的パーティション・プルーニングになっています（PstartとPstopにアクセスしたパーティション番号”8”が出力されます）。
SQL> SELECT * FROM tab01 WHERE sdate = TO_DATE('2012/08/01','YYYY/MM/DD');
実行計画
-----------------------------------------<途中省略>------------------
| Id | Operation | Name | | Pstart| Pstop |
-----------------------------------------<途中省略>------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PARTITION RANGE SINGLE | | | 8 | 8 |
|* 2 | TABLE ACCESS FULL | TAB01 | | 8 | 8 |
（３）複数列パーティション
パーティションも索引と同様に複数列で作成することが可能です（ただし、リスト・パーティションには使用できません）。索引のように一意性を高めるために行うのとは異なり、パーティション単位をもっと細分化したいときなどに使用します。一般的には、最初の列の値が複数のパーティションにまたがるように複数列パーティションを作成すると良いでしょう。例えば、列c1, c2でパーティションを作成するような場合には以下のように作成します（このとき列c1は値以下になり、列c2は値未満になりますので注意して下さい。つまり、パーティションp1の範囲はc1が100以下でc2が50未満となります）。このとき、列c1を使用した条件で検索されると単一列パーティションと同様に静的パーティション・プルーニングになります。
SQL> CREATE TABLE tab01 (…)
2 PARTITION BY RANGE (c1,c2)
3 (PARTITION p1 VALUES LESS THAN (100,50),
4 PARTITION p2 VALUES LESS THAN (100,MAXVALUE),
5 PARTITION p3 VALUES LESS THAN (200,50),
6 PARTITION p4 VALUES LESS THAN (200,MAXVALUE),
... ；
最初の列がWHERE句の条件にない場合は（最初の列が指定されても2番目以降の列でパーティションが特定される場合も）、以下のように動的パーティション・プルーニングになりますので、上手く利用するとパーティション・プルーニングされる列を増やすことができます（索引スキップ・スキャンとは異なりLIKE 演算子の後方一致条件などではパーティション・プルーニングされませんので注意して下さい）。
SQL> SELECT * FROM tab01 WHERE c2 = 10; 実行計画 -----------------------------------------------<途中省略>------------------ | Id | Operation | Name | | Pstart| Pstop | -----------------------------------------------<途中省略>------------------ | 0 | SELECT STATEMENT | | | | | | 1 | PARTITION RANGE MULTI-COLUMN | | |KEY(MC)|KEY(MC)| |* 2 | TABLE ACCESS FULL | TAB01 | |KEY(MC)|KEY(MC)|
複数列パーティションはあまり複雑にすると分かりづらくなるので、パーティション単位をもっと細かく分割するとき、または均等に分割するためには一つの列だけでは行えないときなどに使用するのが良いと思います。
（４）索引とパーティション
パーティションも索引もデータアクセスを絞り込むために使用することができますが、パーティションはシーケンシャル・アクセスの実行が可能だということが違いになります。この二つについては、基本は次のように使い分けると良いと思います。
・大きな範囲で頻繁に検索される列はパーティション・キーにします。
・小さい範囲（または一意）で頻繁に検索さえる列は索引にします。ただし、更新が頻繁に行われる処理では（例えばOLTPシステムなどでは）ハッシュ・パーティションを使用してデータを均等に分割することで、ブロック競合を少なくするという使い方も効果があることを知っておきましょう（ただし、等価条件でないと効果がありません）。
・同じ列で両方（大きな範囲と小さな範囲）の検索を行われるときはパーティション表とローカル同一キー索引を作成します。
・パーティション・キー以外の列で検索が行われるときはグローバル索引を作成します。
・常にパーティションキーと別の列が同時に条件指定されているときはローカル非同一キー索引を作成します。
売上履歴テーブルの例を使用してもう少し説明しましょう。この売上履歴テーブルに以下のSQL文を実行するとして考えていきましょう。
① SELECT * FROM 売上履歴 WHERE 日付 = TO_DATE('2012/08/01','YYYY/MM/DD') AND c1 = x;
② SELECT * FROM 売上履歴
WHERE 日付 BETWEEN TO_DATE('2012/07/01','YYYY/MM/DD') AND TO_DATE('2012/07/31','YYYY/MM/DD')
AND c2 = x;
③ SELECT * FROM 売上履歴
WHERE 日付 BETWEEN TO_DATE('2012/02/01','YYYY/MM/DD') AND TO_DATE('2012/07/31','YYYY/MM/DD')
AND c3 = x;
④ SELECT * FROM 売上履歴 WHERE c1 =x;
まずは、パーティションから考えていきます。頻繁に検索条件として使用している列は日付ですので、日付をパーティション・キーにすることにして、パーティション単位を検討します。日付の条件は、一日、一ヶ月、半年、指定なしがあります。このときにパーティションの条件に使用するのは、範囲が最も小さいものを使用します。あまりにもデータ件数が少ないものは索引とどちらが効率が良いか検討して決めます。これには、条件にヒットする件数が索引として効果があるかの判断が必要になります（一般的には5%未満ぐらいとか言われている値です）。この場合は、１日単位のレンジ・パーティションが良いと思います。
次に、索引を考えていきます。メンテナンスのことを考慮して基本はローカル索引を検討します。パーティション単位より小さい条件でアクセスされることはありませんので、日付に索引を作成する必要はありません。②と③は複数のパーティション（一ヶ月間）にアクセスしますので、列c2とc3に索引を作成するのであればグローバル索引を作成します。問題は①と④で使用されている列c1です。索引を作成するのであれば④はグローバル索引（パーティション・プルーニングされないため）、①はローカル索引がベストになります（パーティション・プルーニングされるため）。これは、どちらの処理を優先するかで決める必要があります。
■ 2. 結合列のパーティション
比較条件の列をパーティション化することで、パーティション・プルーニングが発生するため効果的なのは分かると思いますが、結合列についてはどうでしょうか。パーティション・ワイズ結合に効果があるのは第20回で説明しましたが、パーティション・プルーニングとしての効果があることを知らない方も多いと思いますので、ここで説明しておきます。
時系列に管理しているが時間軸は別テーブルの場合の例を使用して説明しましょう（スター・スキーマを使用している場合はこのように設計する場合が多いと思います）。一般には複数の軸によるスタースキーマになりますが、ここでは簡単にするために売上履歴テーブルと時間テーブルだけを使用して、次のSQL文を実行するような場合を考えてみましょう。
SQL> SELECT * FROM sales_h S,time T
2 WHERE S.time_cd = T.time_cd AND T.ym = TO_DATE('201201','YYYYMM');
絞り込みは時間テーブルで行われているため、件数の多い売上履歴テーブルを静的パーティション・プルーニングすることができません。動的パーティション・プルーニングが行われる場合もありますが、Oracle Database 11gからの次の機能を使用することで更に効果的に売上履歴テーブルをパーティション・プルーニングすることができます。
・リファレンス・パーティション
・ブルーム・フィルタリング(Bloom-Filter)
（１）リファレンス・パーティション
リファレンス・パーティションは、参照整合性制約を使用して親表のパーティションと同じパーティションを行うことができる機能です（親表の列を使用してパーティション化することが可能です）。つまり、親表の検索条件でパーティション・プルーニングすることが可能です。以下の図では時間テーブルの列”年月”を使用して売上履歴テーブルをパーティション化しています。
リファレンス・パーティションを作成するSQL文は以下のようになります（PARTITION BY REFERENCEを指定します）。
SQL> CREATE TABLE time (… ,
2 CONSTRAINT time_pk PRIMARY KEY (time_cd))
3 PARTITION BY RANGE (ym)
4 (PARTITION 201201 VALUES LESS THAN (TO_DATE('201202','YYYYMM')),
5 PARTITION 201202 VALUES LESS THAN (TO_DATE('201203','YYYYMM')),
...
PARTITION 999999 VALUES LESS THAN (MAXVALUE));
SQL> CREATE TABLE sales_h (… ,
2 CONSTRAINT sales_fk FOREIGN KEY (time_cd) REFERENCES time (time_cd))
3 PARTITION BY REFERENCE (sales_fk);
リファレンス・パーティションを使用すると実行計画は以下のようになります（テーブルsales_hが静的パーティション・プルーニングされています）。リファレンス・パーティションに多少の制限（外部キーにはNOT NULL制約を設定する必要があるなど）がありますので、使用するときは注意して下さい。
SQL> SELECT * FROM sales_h S,time T
2 WHERE S.time_cd = T.time_cd AND T.ym = TO_DATE('201201','YYYYMM');
実行計画
-------------------------------------------<途中省略>------------------
| Id | Operation | Name | | Pstart| Pstop |
-------------------------------------------<途中省略>------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PARTITION RANGE SINGLE | | | 1 | 1 |
| 2 | HASH JOIN | | | | |
| 3 | TABLE ACCESS FULL | TIME | | 1 | 1 |
|* 4 | TABLE ACCESS FULL | SALES_H | | 1 | 1 |
（２）ブルーム・フィルタリング
ブルーム・フィルタリングは、結合キーでパーティションすることで（以下のSQL文のように）、結合時にパーティションがフィルタリングされます。これは、最初のテーブルをスキャンして結合列の値をビットマップ化したフィルターを作成します（パラレル実行のときは結合プロセスが作成を行い、スキャン・プロセスに転送します）。もう一つのテーブルをスキャンするときにフィルターとして使用することで、アクセスするパーティションを絞り込んで結合を行います。
SQL> CREATE TABLE time (…)
2 PARTITION BY RANGE (ym)
3 (PARTITION 201201 VALUES LESS THAN (TO_DATE('201202','YYYYMM')),
4 PARTITION 201202 VALUES LESS THAN (TO_DATE('201203','YYYYMM')),
...
PARTITION 999999 VALUES LESS THAN (MAXVALUE));
SQL> CREATE TABLE sales_h (…)
2 PARTITION BY RANGE (time_cd)
3 (PARTITION 201201 VALUES LESS THAN ('20120201'),
4 PARTITION 201202 VALUES LESS THAN ('20120301')),
...
PARTITION 999999 VALUES LESS THAN (MAXVALUE));
ブルーム・フィルタリングが行われると実行計画は以下のようになります。ジョイン・フィルターが作成されて（PART JOIN FILTER CREATE）、スキャンするときにそれを使用して対象外の行（不要なパーティション）を排除することができます（PARTITION RANGE JOIN-FILTER）。このときのPstartとPstopには作成されたフィルター”BF0000”が出力されます
SQL> SELECT * FROM sales_h S,time T
2 WHERE S.time_cd = T.time_cd AND T.ym = TO_DATE('201201','YYYYMM');
実行計画
-------------------------------------------------<途中省略>------------------
| Id | Operation | Name | | Pstart| Pstop |
-------------------------------------------------<途中省略>------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | HASH JOIN | | | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | | | |
| 3 | PARTITION RANGE SINGLE | | | 1 | 1 |
|* 4 | TABLE ACCESS FULL | time | | 1 | 1 |
| 5 | PARTITION RANGE JOIN-FILTER | | |:BF0000|:BF0000|
| 6 | TABLE ACCESS FULL | sales_h | |:BF0000|:BF0000|
ただし、ビットマップを作成してフィルタリングするので、すべてをスキャン時に排除できない場合もありますが、Oarcle Database 10g以前より効果的なパーティション・プルーニングが行われます。以前では副問合せを使用するSQLに変換できるときは副問合せによる動的パーティション・プルーニング（PARTITION RANGE SUBQUERY）か、ネステッド・ループ結合による動的パーティション・プルーニング（PARTITION RANGE ITERATOR）が行われます。
■ 3. おわりに
今回はパーティションの設計とプルーニングについて説明しました。また機会があれば他のことについても説明したいと思います。次回も頑張りますのでよろしくお願いします。それでは、次回まで、ごきげんよう。