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

 

皆さんこんにちは、今年の冬は大雪で寒さも厳しかったので、やっと過ごしやすい気候になってきて嬉しいですね。
今回は、Oracle Database 12cR2（Oracle12cR2）で追加されたパーティションの機能について説明しようと思います。パーティションは、大規模データのパフォーマンスにとても有効で、簡単にパフォーマンスを改善できる機能ですので、新しい機能も含めてもっと有効に使用して欲しい機能です。そのため、追加された機能の中で、パーティションを使いやすくする「リスト・パーティションの強化」と「パーティション・メンテナンスの新機能」について説明しますので、参考にしてください。

1. リスト・パーティションの強化
まずは、リスト・パーティションの強化から説明します。
リスト・パーティションは、あまり使用する機会が多くないと思いますが、連続的なデータでない列、頻繁に使用される個別値が少ない列、カーディナリティが低い列などでアクセスが分割されているため、レンジ・パーティションでは効果的に分割（均等に分割）できないときに使用します。そのため、より使いやすくするために、以下の機能がOracle12cR2から追加になりました。

  • 自動リスト・パーティション
  • 複数列リスト・パーティション

（１）自動リスト・パーティション
自動的に作成するパーティション機能は、よく使用するレンジ・パーティションのインターバル・パーティション（時間隔パーティション）だけでしたが、Oracle12cR2からリスト・パーティションでも自動的に作成できるようになりました。パーティション化で問題になるのは、CREATE TABLE文やADD PARTITION文が複雑になることだと思うので、自動的に行ってくれるのは嬉しいことです。ただし、注意点として以下のようなルールがあるので、効果的に使用できるか検討する必要があります。

  • すべての個別値が別パーティションになる
  • DEFAULTパーティションは指定できない
  • サブ・パーティションとしては使用できない（インターバルも同じ）
  • 第22回で説明したリファレンス・パーティションとして使用できない（インターバルは使用できる）
  • パーティション名はシステムが生成するので、パーティションの特定に’FOR (<値>)’を使用する（インターバルも同じ）

このようなルールで使用できる場合、自動的に作成される自動リスト・パーティションやインターバル・パーティションは便利な機能なので、使用を検討してみてください。
自動リスト・パーティションの作成は、以下のようにPARTITION句にAUTOMATICと最低一つのパーティションを指定するだけです（この例は、ブランドが’BMW’だけのパーティションが作成されていて、それ以外のブランドが追加されると、別パーティションとして作成されます）。

    SQL> CREATE TABLE sales (“ブランド” VARCHAR2(50), “モデル” VARCHAR2(50), …)
      2  PARTITION BY LIST (“ブランド”) AUTOMATIC
      3  (PARTITION p1 VALUES (‘BMW’)) ;

自動リスト・パーティションには、以下のようにリスト・パーティションから移行することもできるので、現在使用中のリスト・パーティションも簡単に移行できます。ただし、DAFAULTパーティションがあると移行できません。

    SQL> ALTER TABLE sales SET PARTITIONING AUTOMATIC ;

また、パーティションが追加されると、以下のように最初に指定したパーティション’p1’を削除することもできます（これまでのインターバル・パーティションは、ORA-14758エラーになり削除できませんでしたが、Oracle12cR2からはできるように改善されています ）。

    SQL> ALTER TABLE sales DROP PARTITION p1 ;

（２）複数列リスト・パーティション
複数列パーティションは、レンジ・パーティションとハッシュ・パーティションだけでしたが、Oracle12cR2からリスト・パーティションも最大16個の複数列で作成できるようになりました。一つの列では、均等に分割するのが難しいとき（個別値の件数が偏っているときなど）に、効果的にパーティションが使用できるようになります。自動リスト・パーティションやリファレンス・パーティションとしても使用することができます。
複数列リスト・パーティションは、以下のように指定します（この例は、regionだけでは均等に分割できないので、channelと一緒にパーティション化しています）。

    SQL> CREATE TABLE sales (region VARCHAR2(50), channel VARCHAR2(50), …)
      2  PARTITION BY LIST (region, channel) 
      3  (PARTITION p1 VALUES ('USA','ダイレクト'),
      4   PARTITION p2 VALUES ('USA','パートナー'),
      … ) ;

複数列リストに対して、複数の個別値を指定することもできます。以下のようにそれぞれの個別値の組合せをカッコで指定します。

    SQL> CREATE TABLE sales (region VARCHAR2(50), channel VARCHAR2(50), …)
      2  PARTITION BY LIST (region, channel) 
      3  (PARTITION p1 VALUES (('JAPAN','パートナー'),('JAPAN','Web')),
    … ) ;

リスト-リスト・パーティションが2レベルの対称関係にあるのに対し、複数列リスト・パーティションは一致するときだけ次の列を評価するという動作になります。そのため、最初の列を指定しないときには、第22回で説明した動的パーティション・プルーニング’KEY(MC)’が動作するので、複数列の列順に注意してください。

2. パーティション・メンテナンスの新機能
次に、パーティション・メンテナンスで追加された新機能について説明します。
パーティション・メンテナンスも実施しやすくするために、以下のようなオンライン操作や実行を簡略化する機能などがOracle12cR2から拡張されています。

  • オンライン・パーティション・メンテナンス操作の追加
  • フィルタ付きパーティション・メンテナス操作
  • 読取り専用パーティション
  • 交換用の表作成
  • DDLによるカーソル無効化の削減

（１）オンライン・パーティション・メンテナンス操作の追加
パーティション・メンテナンスは、実施するタイミングを悩むことが多いですが、オンライン操作が増えたことで、メンテナンスがしやすくなりました。以下のパーティション操作が、Oracle12cR2からオンラインで可能になっています。

・パーティション表へのオンライン表変換
これまではオフラインだけでしたが、以下のようにONLINE句を指定できるようになりました。そのため、後からパーティション化するのがしやすくなります。

    SQL> ALTER TABLE <表名> MODIFY <パーティション指定> ONLINE ;   

・オンライン・パーティション分割
パーティション分割（SPLIT PARTITION）は、パーティションのデータが増えて時間が掛かるようになってきた場合に行います。これもオンラインではできませんでしたが、以下のようにONLINE句を指定できるようになり、より柔軟に運用できるようになります。

    SQL> ALTER TABLE <表名> SPLIT PARTITION <パーティション名>
      2   INTO (PARTITION <パーティション名> VALUES LESS THAN (<値>),…) ONLINE ;

・ヒープ表（通常表）のオンライン移動
これまでパーティション移動だけでしたが、ヒープ表全体もオンラインでできるようになりました（これまでは索引構成表だけが指定できました）。以下のように非パーティション表をオンラインで移動できるようになります。パーティション・メンテナンスではないですが、オンライン操作ということで載せておきました。

    SQL> ALTER TABLE <表名> MOVE ONLINE TABLESPACE <表領域名> COMPRESS ;

（２）フィルタ付きパーティション・メンテナス操作
データのメンテナンス（フィルタ条件の指定）とパーティションのメンテナンスを、Oracle12cR2から同時に行うことができるようになりました。
パーティション操作（MOVE、SPLIT、MERGE）に対して、データのメンテナンス（不要なデータの削除）も行うことができ、パーティションの管理性が向上しています。また、MOVEとSPLIT操作は、オンラインで行うこともできます（このとき削除されるデータへのDMLは無効となります）。
以下のように、対象のデータに対するフィルタ条件（INCLUDING ROWS WHERE）を指定することができます（この例は、パーティション’2015_q3’のorder_stateが’open’のデータだけを表領域’archive’に移動し、残りのデータは削除されます）。SPLIT PARTITIONとMERGE PARTITIONに対しても対象外のデータは削除されます。

    SQL> ALTER TABLE orders MOVE PARTITION 2015_q3 TABLESPACE archive
      2  INCLUDING ROWS WHERE order_state = ‘open’ ;

（３）読取り専用パーティション
これまでパーティションのデータを変更させたくないときには、読取り専用表領域を使用する必要がありましたが、Oracle12cR2から以下のようにパーティション単位に指定できるようになり、不適切なデータの変更を完全に防ぐことができるようになりました。

    SQL> CREATE TABLE sales (region VARCHAR2(50), channel VARCHAR2(50), …)
      2  PARTITION BY LIST (region, channel)
      3  (PARTITION p1 VALUES (‘USA’,’ダイレクト’) READ ONLY,
      … ) ;

読取り専用表領域では、完全にはデータ不変性を保証できませんでしたが（以下のSQLなどは許されましたが）、読取り専用パーティションではそのようなことが発生しなくなります。

    SQL> ALTER TABLE <表名> SET UNUSED (<列名>) ;  -- 列に未使用マークを付ける
    SQL> ALTER TABLE <表名> DROP PARTITION <パーティション名>;

また、以下のようなデータの中身に影響しない操作は禁止されないので、より柔軟な使い方ができるようになっています。

    SQL> ALTER TABLE <表名> MOVE COMPRESS ;
    SQL> ALTER TABLE <表名> MERGE PARTITIONS <パーティション名,…> INTO PARTITION <パーティション名> ;

（４）交換用の表作成
パーティション交換（EXCHANGE PARTITION）は、パーティション表のロードで便利な機能ですが、これに使用する表は簡単に作成できない場合があったので、Oracle12cR2から簡単に作成できるようになりました。
これまでパーティション表と完全一致する空表を、以下のようにCTAS（Create Table As Select）で作成していましたが、これには使用不可（UNUSED）の列、非表示（INVISIBLE）の列、仮想式の列、ファンクション索引式の列、他の内部設定や属性などが含まれませんでした。

    SQL> CREATE TABLE emp_ex TABLESPACE test AS SELECT * FROM emp WHERE 1=2 ;

以下のように、交換用の表（FOR EXCHANGE WITH TABLE）と指定することで、完全一致した空表を簡単に作成できるように改善されています。

    SQL> CREATE TABLE emp_ex TABLESPACE test FOR EXCHANGE WITH TABLE emp ;

（５）DDLによるカーソル無効化の削減
最後に、パーティション操作でも行われる、DDLによる共有カーソル無効化の削減について説明します。
共有カーソルがINVALID（無効化）になると、ハード解析による性能ダウンが発生するので、多くの共有カーソルを無効化するのは好ましくありません。ただし、第27回で説明したように、共有カーソルはDDL（表や索引の変更）やオプティマイザ統計の収集などで無効化になるので、実行するタイミングに注意が必要でした。そのため、Oracle Database 10gからは、オプティマイザ統計の収集にDBMS_STATS.AUTO_INVALIDATE（Oracleが無効化の時機を決定する ）が追加されましたが、その他については対応していませんでした。
Oracle12cR2からは、DDLに対して即時に共有カーソルの無効化をしていたのが、同時に行わないようになりました。このとき可能なものは無効化を回避し（TRUNCATE PARTITIONやMOVE PARTITIONなど）、避けられないものはローリング無効化を行います（無効化を遅延させて分散します）。ただし、索引の削除や使用禁止（UNUSABLE）に対しては、その索引を使用している共有カーソルは即時に無効化されます。
以下のように、DDLの新しいオプション’DEFERRED INVALIDATION’を指定することで、共有カーソル無効化の調整が行われます。このオプションを指定しない場合は、初期化パラメータCURSOR_INVALIDATIONの値が使用されます（デフォルトはIMMEDIATEで、即時に無効化です）。

    SQL> DROP INDEX <索引名> [DEFERRED|IMMEDIATE] INVALIDATION ;

これが使用できるのは、以下のDDLになります。

  • パーティション表に対するALTER TABLEとTRUNCATE TABLE
  • 表に対するパラレル度の変更（ALTER TABLE … PARALLEL）
  • 索引に対するDDL（CREATE INDEX, DROP INDEX, ALTER INDEX … UNUSABLE, ALTER INDEX … REBUILD）

分かりやすいように、CREATE INDEX文とMOVE PARTITION文の例を実行してみます。CREATE INDEX文を実行すると、実行計画に影響する可能性があるので、共有カーソルの無効化を行う必要があります。そのため、以下のように’DEFERRED INVALIDATION’を指定しないと、共有カーソルは無効化されています（V$SQL.INVALIDATIONSがカウントアップします）。

    SQL> CREATE INDEX ix_demo01 ON demo (c1);
    索引が作成されました。

    SQL> SELECT sql_text,invalidations,loads,is_rolling_invalid FROM v$sql WHERE sql_text LIKE ‘S%DEMO%’;

    SQL_TEXT            INVALIDATIONS      LOADS IS_ROLLING_INVALID
    ——————- ————- ———- ——————
    SELECT * FROM DEMO              1          1 N

‘DEFERRED INVALIDATION’を指定することで、V$SQL.INVALIDATIONSがカウントアップされずに、共有カーソルのローリング無効化が行われます（V$SQL.IS_ROLLING_INVALID=’Y’になります）。

    SQL> CREATE INDEX ix_demo01 ON demo (c1) DEFERRED INVALIDATION;
    索引が作成されました。

    SQL> SELECT sql_text,invalidations,loads,is_rolling_invalid FROM v$sql WHERE sql_text LIKE ‘S%DEMO%’;

    SQL_TEXT            INVALIDATIONS      LOADS IS_ROLLING_INVALID
    ——————- ————- ———- ——————
    SELECT * FROM DEMO              0          1 Y

また、MOVE PARTITION文では、以下のように’DEFERRED INVALIDATION’を指定しないと変わりませんが、実行計画に影響しないので、’DEFERRED INVALIDATION’を指定すると、共有カーソルのローリング無効化も行われません（V$SQL.IS_ROLLING_INVALID=’N’になります）。

    SQL> ALTER TABLE demo MOVE PARTITION p1;
    表が変更されました。

    SQL> SELECT sql_text,invalidations,loads,is_rolling_invalid FROM v$sql WHERE sql_text LIKE ‘S%DEMO%’;

    SQL_TEXT            INVALIDATIONS      LOADS IS_ROLLING_INVALID
    ——————- ————- ———- ——————
    SELECT * FROM DEMO              1          1 N

    SQL> ALTER TABLE demo MOVE PARTITION p1 DEFERRED INVALIDATION;
    表が変更されました。

    SQL> SELECT sql_text,invalidations,loads,is_rolling_invalid FROM v$sql WHERE sql_text LIKE ‘S%DEMO%’;

    SQL_TEXT            INVALIDATIONS      LOADS IS_ROLLING_INVALID
    ——————- ————- ———- ——————
    SELECT * FROM DEMO              0          1 N   

これによりDDLを多く実行するアプリケーションでも、パフォーマンスが改善されるようになりますが、すべてのDDLが調整される訳ではないので、DDLはあまり実行しないようにしてください。

3. おわりに
今回はOracle Database 12cR2のパーティションについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。

ページトップへ戻る▲ 

 

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