津島博士のパフォーマンス講座　 第10回 パーティションについて

津島博士のパフォーマンス講座　Indexページ ▶▶

皆さんこんにちは、今回で連載は第10回になりますが結構続いているので驚いています。これからも頑張りますのでよろしくお願いします。
今回は、第1回で簡単に説明したパーティションについてもう少し詳しく説明しようと思います。だいたい良さは知っているけど、どのように使うのか良く分からない方も多いのではないでしょうか。索引とパーティションを組み合わせて効果的に利用しようと思うと意外と難しかったりします。索引もパーティションもパフォーマンスを向上するためにとても良い機能です。できれば両方を効果的に使用することがベストだと思います。そこで、今回は「パーティションの利用方法について」まとめて説明しますので、参考にして下さい。

■1．パーティションとは
まずは、パーティションについて簡単に説明します。第1回でも説明しましたが、テーブルをデータベース内部で複数の領域（指定キー）に分割する機能がパーティションです。そのため、テーブルは1つとしてデータを分割されるように管理できるので、以下に示すように効率よくアクセスできます。これはテーブルと索引のどちらも可能です。

• パーティション・プルーニング
  分割されたパーティションのサブセットにのみアクセスすれば結果が得られる場合、表全体ではなくサブセットのみに絞りこんでアクセスします。これは絞り込むという意味では索引と同じですが、フルスキャンが行えるので選択率が高い場合でも有効です。
• パーティション単位のメンテナンス
  テーブル全体に影響を及ぼすことなくメンテナンスが実施できる（特にパーティションの削除はDROP PARTITIONで可能になる）。
• パーティション・ワイズ結合
  パーティション化された2つの表をパラレル結合の時にオーバーヘッドが少なくなるように最適化される。

（1）パーティション・テーブル
そもそもテーブルをパーティション化する方法には以下のようにいくつかあります。それぞれには得意、不得意がありますので、それらを意識して効率よく設計する必要があります。

• レンジ
  連続データ（時系列データなど）でのパーティション・プルーニング及びメンテナンス
• ハッシュ
  均等分割による競合回避、等価条件に対するパーティション・プルーニング
• リスト
  非連続データ（都道府県ごとなど）でのパーティション・プルーニング及びメンテナンス
• コンポジット（レンジ・ハッシュなどの二つの組合せ）
  メンテナンス性と均等分割（レンジ-ハッシュ）などを同時に行うことができる
• インターバル（Oracle Database 11gから）
  レンジ・パーティションを自動的に追加してくれる
• リファレンス（Oracle Database 11gから）
  参照整合性制約を指定された親テーブルのパーティションと同じにできる
• バーチャルカラム（Oracle Database 11gから）
  存在しないデータでパーティション化できる

（2）パーティション索引
テーブルがパーティション化されている場合に、そのテーブルに作成する索引には以下のような種類があります。これは、何を優先するかで決定することになります。例えば、メンテナンス性を優先したい場合にはローカル・パーティション索引、パーティション・プルーニングによる性能向上を優先したいのであればグローバル・パーティション索引（ローカル索引でも可能なものもあります）などとなると思います。ただし、デメリットもあることを知って使用して下さい。

• ローカル・パーティション索引（テーブルと同じ方法でパーティション化されている）
  – 同一キー索引（パーティションキーと索引が同じ）
  – 非同一キー索引（パーティションキーと索引が異なる）
• グローバル・パーティション索引（テーブルと異なるキーでパーティション化されている）
• グローバル・非パーティション索引（パーティション・テーブルに対してパーティション化されていない）

　「パーティションとは」の説明はここまでにします。このような説明の資料は他に沢山あると思いますので、ここでは詳細については省略します。

ここからは、「どのように作成するのか」を説明しようと思います。

■2. どのように作成するのか
それでは、どのようにパーティション（パーティション索引）を作成すれば良いか説明します。基本は、以下に示すようなことを目的に作成しますが、なかなか難しいと思われているのではないでしょうか。これは、いろいろなことを（あれも実現したい、これも実現したいと）考えてしますからだと思います。
これはパフォーマンス・チューニングなどでよくお話しすることなのですが、すべてを実現することは無理だということです。何かを速くすると別の何かが遅くなるようなトレードオフがあるからです。そのため、性能を向上させる優先順位を決めてから検討して下さいということです。これが難しかったりするのですが、これができなければ良いシステムは構築できないと思います。

＜パーティション作成の目的＞

• メンテナンス性の向上
• パーティション・プルーニング（プルーニング）
• パーティション・ワイズ結合
• パラレル索引スキャン
• 索引スキャンの性能向上（Bツリーの階層を削減することによる）

　そうはいってもなかなか決められないのが現実だと思いますので、ここに簡単な指針をまとめてみました。

1. まずは、大規模システムかです。大規模システムではメンテナンス性を重視した方が良いと思います。データのメンテナンスは必ず行わなければいけない処理だと思います。大規模なシステム（特に大規模なテーブル）では、このメンテナンスの処理は無視できない程の時間になります。つまり、主パーティションはメンテナンス重視の（メンテナンス単位での）レンジかリストが良いということです。索引アクセスが必要であればローカル・パーティション索引（ローカル索引）も作成します（このとき索引は作り過ぎないようにして下さい。そのために当然ながらSQL文の優先順位が必要です）。このパーティション（テーブルとローカル索引）だけで、プルーニングや索引スキャンの性能向上が実現されるSQL文も沢山あると思います。
2. 次に、パラレル処理が必要なテーブルかです。もしそうであればパーティション・ワイズ結合やパラレル索引スキャンを検討しましょう（ただし、これよりプルーニングの方が効果が大きい場合もあることを忘れずに）。どのようにするかというと副パーティションに優先順位の高い結合列（一つのテーブルが複数のテーブルと別々の列で結合するような場合があると思いますので、その中で優先順位が高いもの）でハッシュ・パーティションを行うと良いです。これは、ハッシュ・パーティションは均等にデータ分割するのに優れているパーティションだからです。レンジなどでも均等に分割できる場合はレンジなどでも構いません。
   これで、パーティション・ワイズ結合やパラレル索引スキャン（ただし、索引をローカル索引として作成している必要があります）が動作すると思います。このときにすべての索引がパラレル索引スキャンで良いかですが、常にパラレル処理が行われるのであれば問題ないと思います。もし非パラレル処理を優先する索引の場合は、その索引列にグローバル・パーティション索引を作成すると良いと思います（主パーティションと副パーティションでプルーニングされるSQL文であれば、ローカル索引でも問題ありません）。
3. 最後に、パラレル処理が必要でない（またはパーティション・ワイズ結合やパラレル索引スキャンの優先順位が低い）、アクセスする件数が少ない処理（OLTPなど）のテーブルです。この場合は、プルーニングを検討することになると思いますが、これもSQL文の優先順位を決めて行うということです。優先順位の高いSQL文がプルーニングされないようでしたら、そのSQL文がプルーニングされるように副パーティションを作成します（ここでは、大規模テーブルを前提に説明していますが、そうでないテーブルやメンテナンスを重視しないテーブルは、主パーティションとしても良いと思います）。このときに、既に副パーティションまで決まっていたらグローバル・パーティション索引を作成すれば良いということです。ただし、この場合は索引アクセスすることが前提になります。

　ここにまとめたものはあくまで一般的なシステムを例にした指針ですので、それぞれのシステムで検討することは異なってくると思いますが、参考にしていただければと思います。

　それでは、それぞれのパーティション索引についてもう少し詳しく説明していきます。

（1）ローカル・パーティション索引
ローカル索引は、テーブルと同じパーティション構造になるのでメンテナンスが頻繁に行われる場合（再構築など）に有効です。パーティション・キー列と索引キー列が同じか（パーティション・キーが索引の先頭からすべて含んでいるか）どうかで、以下の表のように同一キー索引と非同一キー索引の二つがあります。つまり、索引アクセスするとプルーニングもされるのが同一キー索引です。

通常のプルーニングが発生する場合はローカル索引で問題ないと思いますが、プルーニングが発生しないSQL文（WHERE句にテーブルのパーティションキーが指定されていない非同一キー索引）には、すべてのパーティション索引がアクセスされてしまいますので、パーティションが無い場合よりパフォーマンスは低下してしまいますから注意して下さい。これは、それぞれの索引のパーティションを順番にアクセスしてしまうからです（それぞれのパーティションでBツリーなどが作成されているため）。このような場合はグローバル索引を作成することを検討して下さい。

それから、非同一キー索引はパラレル索引スキャンに有効です。これは、パラレル索引スキャンは複数のパーティションにアクセスしないと動作しないから、非同一キー索引は索引アクセスした場合に複数パーティションにアクセスされる可能性が高いからです。このとき、プルーニングされるSQL文（WHERE句に索引とパーティションキー列の条件がある場合）であればアクセスする件数が少なくなるので、パラレルでなくても問題ないと考えても良いと思います。

（2）グローバル・パーティション索引
この索引は同一キー索引だけです。これは、ローカル索引だとプルーニングしないSQL文を、索引キーでプルーニングするように作成するものですから、同一キー索引だけになります。そのため、以下のように日付でレンジ・パーティションされているテーブルにWHERE句で商品番号の条件が指定されているSQL文でも索引アクセスでプルーニングを発生させることができます。つまり、索引アクセスをする場合は、このパーティション索引を使用すると様々な列でプルーニングを行わせることができるということです（フル・スキャンによってプルーニングを行える条件はコンポジット・パーティションを使っても二つまでです）。

SQL> CREATE TABLE tab1 ( ... ) PARTITION BY RANGE (日付) ... ;
SQL> SELECT * FROM tab1 WHERE 商品番号 = xx

（3）グローバル・非パーティション索引
パーティション索引はBツリー階層を低くするために使用するので、大規模なテーブルではこの索引はあまり使用しないと思います（Bツリーのアクセス効率が悪くなるので）。ただし、パーティション索引をプルーニングしない（すべてのパーティションに順番にアクセスする）よりは、多少Bツリーの階層が高くても1回の索引アクセスの方が効率が良くなるため、そのような場合に使用します。例えば、アクセス範囲が特定できないためパーティション条件が決定できない（指定してもアクセスするパーティションが一つにならない）などのような場合に作成することになると思います。

（4）主キーの作成について
メンテナンス性を重視する場合は、主キー（一意索引）にローカル索引を使用しますが、このとき一意性を確保するために、索引キー列にパーティション・キー列を含める必要があります（これは同一索引キーを別パーティションに格納しないためです）。そのため、主キーとパーティションキーが同一でない場合は、以下のようにパーティションキー列を索引キーのサブセットにしないと作成できません（ただし、これは’c1’だけで一意にする必要があるときは使用できません）。つまり、ローカル索引にするには、主キーで主パーティション化しないと、効果的に作成できないということです。Oracle Database 11gからは、様々な組み合わせのコンポジット・パーティションを作成できますので、主キー以外の列でパーティション化するときには副パーティションの使用を検討して下さい。

SQL> CREATE TABLE tab1 ( ... ) PARTITION BY RANGE (c2) ... ;
SQL> CREATE UNIQUE INDEX tab1_pk ON tab1 (c1,c2) LOCAL;
SQL> ALTER TABLE tab1 ADD CONSTRAINT tab1_pk PRIMARY KEY (c1,c2) USING INDEX tab1_pk;

　それから、以下のSQL文などで制約にどの索引が使用されているかを確認できます（この例は上記制約の出力です）。

SQL> SELECT constraint_name, constraint_type, B.index_name B.locality, B.alignment
  2  FROM dba_constraints A, user_par_indexes B
  2  WHERE A.index_name = B.index_name AND A.table_name = 'TAB1';

CONSTRAINT_NAME                C INDEX_NAME                     LOCALI ALIGNMENT
------------------------------ - ------------------------------ ------ ------------
TAB1_PK                        P TAB1_PK                        LOCAL  NON_PREFIXED

■3. パーティション索引のメンテナンス
最後に、パーティション索引の効率良いメンテナンス方法について説明します。これは、よくパーティションを作成して、パーティション単位にデータを挿入することで、メンテナンスを効率よく行いましょうといいますが、索引を含めて効率よく行うのは慣れていないとなかなか難しいです（グローバル・パーティション索引、ローカル索引でも主キーが作成されているときなど）。これは、知ってそうであまり知っていない方も多いと思いますので、簡単に説明します。

例えば、レンジ・パーティションで時系列（日別）にデータが格納され、毎日データがロードされて一番古いデータを削除するような運用があったとします。どのように運用したら効率よくできると思いますか。基本はデータを大量挿入する場合はダイレクト・パス・インサート（データロードの場合は外部表を使用して）を行うと効率よく行えます。索引のメンテナンスはインサート終了後にパラレル・ダイレクト・パス・インサートであれば索引作成もパラレルで行われます（SQL*Loaderのダイレクト・ロードの場合はシリアルで索引作成を行いますので注意して下さい）。でも手動で明示的に索引を作成した方が効率よく作成できると思います（索引は1個とは限りませんから）。ダイレクト・パス・インサートの最後の索引作成でリソース（CPU, I/O）を効率よく使用していない場合は手動で作成することを検討して下さい。

いくつか方法はありますが、EXCHANGE PARTITION（瞬時に終了します）が一番簡単かと思います。以下に主キーが設定されている場合の外部表を使用したロードの手順例を示します。

SQL> CREATE TABLE tab1w (c1 number NOT NULL, c2 ....);                                ...【1】
SQL> INSERT /*+ APPEND */ INTO tab1w SELECT * FROM ext_tab1;                          ...【2】
SQL> CREAT UNIQUE INDEX tab1w_pk ON tab1w (c1) NOLOGGING PARALLEL xx;                 ...【3】
SQL> ALTER TABLE tab1w ADD CONSTRAINT tab1w_pk PRIMARY KEY (c1) USING INDEX tab1w_pk 
[ENABLE NOVALIDATE]; ...【4】
SQL> ALTER TABLE tab1 ADD PARTITION pxxx;                                             ...【5】
SQL> ALTER TABLE tab1 EXCHANGE PARTITION pxxx WITH TABLE tab1w [INCLUDING INDEXES];   ...【6】
SQL> ALTER TABLE tab1 DROP PARTITION pxxx;                                            ...【7】
SQL> DROP TABLE tab1w;

【1】ロードするテーブルを作成する（主キーの列にNOT NULL制約を付加する）。
【2】【1】のテーブルに外部表からダイレクト・パス・インサートする。
【3】主キーの列に一意索引を（パラレルで）作成する。
【4】主キーを付加します。
【5】パーティションを追加する。
【6】パーティション・テーブルのパーティションとロードしたテーブルをEXCHANGE PARTITIONする。
（主キー以外の索引も交換する場合はINCLUDING INDEXESを指定する）。
【7】一番古いパーティションを削除する。

　主キー制約をENABLE VALIDATE（使用可能）にすると妥当性チェック処理によってNOT NULL検証が行われますので（ENABLEのデフォルトがこれです）、多少時間がかかってしまいます。このオーバーヘッドを回避するためには、主キーを使用可能にする前にNOT NULL制約を付けておきます（これは、一意索引ではNOT NULL制約は保障されないからです）。または使用可能にENABLE NOVALIDATEを行います（これを行うと既に格納されているデータの妥当性チェック処理が行われなくなります）。このようなことを知っておくと便利だと思います。

グローバル索引の場合は、索引全体を再作成する必要があります。そのため、索引の数が多くなると再作成のための時間が大変になるますので、特にグローバル索引は最小限にするようにして下さい。

それから、索引がある状態でパーティションのメンテナンス（SPLIT PARTITION,MERGE PARTITIONなど）を行うと索引はUNUSABLE状態になり、使用できない状態になります。この状態になると再作成しないと使用することはできません。以下のようにALTER TABLE文にUPDATE INDEXESを付けると同時に索引もメンテナンスさせることができますので、索引を再構築する必要がなくなりますので知っておくと良いでしょう。

SQL> ALTER TABLE tab1 SPLIT PARTITION pxxx AT (xx) [UPDATE INDEXES];

■4. おわりに
今回はパーティションについて私なりにまとめた内容を説明しました。まだ暑いですが皆さま体調に気を付けて下さい。次回も頑張りますのでよろしくお願いします。

それでは、次回まで、ごきげんよう。

ページトップへ戻る▲ 

津島博士のパフォーマンス講座　Indexページ ▶▶