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

 

皆さん、明けましておめでとうございます。今年もよろしくお願いします。今年の冬は寒さが厳しいですが、素敵な一年になると良いですね。
今年最初の内容は、第22回の続きとしてパーティションについて説明します。パーティションは、大規模なテーブルの性能向上に有効な機能ですが、グローバル索引の使用やメンテナンスに難しい部分もあり、バッチ処理などの性能が問題になる場合があります。そこで、これまで説明していなかったグローバル索引やパーティションのメンテナンスについて、Oracle Database 12c（Oracle12c）の新機能を含めて説明しますので、参考にしてください。

1. グローバル索引

まずは、グローバル索引について説明しましょう。
第10回でパーティション索引のメンテナンスで説明したように、データの削除や索引のメンテナンスなどを効果的に行うために、ローカル索引を使用してパーティションごとにメンテナンスを行いますが、パーティション・プルーニングしないと全パーティションに索引アクセスしてしまいます。そのため、オンライン処理などの処理時間が短いものでは、グローバル索引を作成するような場合もあります。ただし、グローバル索引は、パーティション・メンテナンスによって再構築が必要になるので、索引設計やメンテナンスが難しくなってしまいます。そのため、グローバル索引のメンテナンスには、以下のような拡張が行われているので、どのように使用できるか少しまとめてみました。

  • （１）索引の再構築
  • （２）索引の自動更新（Oracle9iから、Oracle Database 10gで拡張）
  • （３）索引の非同期メンテナンス（Oracle12cから）
  • （４）パーティション表の部分索引（Oracle12cから）

（１）索引の再構築
パーティション表は、様々なパーティション操作を行うことができますが、このときグローバル索引はUNUSABLE（索引が使用禁止）になります（グローバル・パーティション索引は、すべてのパーティションがUNUSABLEになります）。そのため、このような操作を行うとグローバル索引の再構築が必要になります。しかし、このときのグローバル索引は、再構築されるまで索引アクセスができないので、テーブルのサイズが大きいと長時間その索引アクセスができなくなります。また、グローバル・パーティション索引は、以下のように1文で再構築することができないので、パーティションが多い場合などは再構築するのも大変になります。

SQL> ALTER INDEX tab1_ix1 REBUILD;

ORA-14086: パーティション索引全体を再構築することはできません


パーティション索引については、以下のようにパーティションごとに再構築する必要があります。

SQL> ALTER INDEX tab1_ix1 REBUILD PARTITION p01_ix1;
SQL> ALTER INDEX tab1_ix1 REBUILD PARTITION p02_ix1;
…


そのため、パーティションのサイズが小さい（テーブルに対するサイズの割合が少ない）ときは、グローバル索引をUNUSABLEにしない方が効果的な場合もあります。DROP PARTITIONやTRUNCATE PARTITIONは、以下のようにデータを削除してから行うことで、UNUSABLEにしないようにできます。

SQL> DELETE FROM tab1 PARTITION (p01);
SQL> ALTER TABLE tab1 DROP PARTITION p01;

 

（２）索引の自動更新
小さいサイズのパーティションでは、UNUSABLEにしない方が効果的ですが、データを削除する操作（DROP、TRUNCATE）以外にはできません。そのため、Oracle9iからパーティション操作でグローバル索引をUNUSABLEにしないように、グローバル索引の自動更新が追加されています。以下のようにALTER TABLE文にUPDATE GLOBAL INDEXES句を指定することで、パーティション・メンテナンス時にグローバル索引もメンテナンスします。

SQL> ALTER TABLE tab1 TRUNCATE PARTITION p01 UPDATE GLOBAL INDEXES;


これは、パーティション・メンテナンス後に索引を再構築する必要がなくなり、索引の更新中でも影響を受けないパーティションに索引アクセスできますが、パーティションのメンテナンス時間は増えてしまいます。そのため、この時間は、索引の自動更新を行わずにメンテナンスして、すべての索引を再構築した場合の時間と比較する必要があります。一般的には、パーティションのサイズがテーブルのサイズの5%未満であれば、索引の自動更新を行った方が処理時間は短くなります。ただし、索引の再構築には、索引の利用効率が向上するという効果があるので、このようなことも含めて検討する必要があります。
パーティションのデータが移動する操作（COALESCE、MERGE、SPLIT、MOVEなど）については、ローカル索引でもそのパーティションがUNUSABLEになります。そのため、Oracle Database 10gからローカル索引もメンテナンスされるように、以下のUPDATE INDEXES句（索引の自動更新）が追加されているので、どちらも更新したい場合はこれを使用してください。ただし、ローカル索引については、内部的に再構築が実行されるので、DMLが実行されないときに行うようにしてください。

SQL> ALTER TABLE tab1 MOVE PARTITION p01 UPDATE INDEXES;


索引構成表については、UPDATE INDEXES句が使用できないので、UPDATE GLOBAL INDEXES句を使用する必要があります。

 

（３）索引の非同期メンテナンス
大きいサイズのパーティションでは、索引の自動更新によるメンテナンス時間が長くなってしまいます。また、再構築するとその間は索引アクセスができません。そのため、Oracle12cからデータを削除する操作（DROP、TRUNCATE）については、グローバル索引を非同期にメンテナンス（メタデータのみ更新）することで、メンテナンス時間を短縮しています。
この機能は、索引の自動更新を指定したときに動作して、以下のようにビュー’USER_INDEXES’などの列’ORPHANED_ENTRIES’が’YES’になります。索引の自動更新を指定しない場合には、これまでのようにグローバル索引は更新されないので、索引を再構築する必要があります（以下の列’STATUS’がUNUSABLEになります）。

tsushima-43-1

このとき索引メンテナンスしていませんが、以下のように索引アクセスすることが可能です。これは、以下のようにTBL$OR$IDX$PART$NUMファンクションを使用して実現しています。

SQL> SELECT * FROM tab1 WHERE c2 = 1;
実行計画
——————————————————————————-
| Id  | Operation                                  | Name     | Pstart| Pstop |
——————————————————————————-
|   0 | SELECT STATEMENT                           |          |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TAB1     | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                         | TAB1_IX1 |       |       |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   2 – access(“C2″=1)
       filter(TBL$OR$IDX$PART$NUM(“TAB1″,0,8,0,”TAB1”.ROWID)=1)


ただし、データ行が存在しないキーが存在するので、多少のオーバーヘッドは発生します。そのため、後からメンテナンスする必要があるので、自動スケジューラ・ジョブ’SYS.PMO_DEFERRED_GIDX_MAINT_JOB’によって、索引のメンテナンスが実行されるようになっています（デフォルトでは、毎日午前2:00に実行されて、グローバル索引をクリーンアップします）。それから、以下を行うことで強制的に索引をメンテナンスすることも可能です。

  • PL/SQLプロシージャDBMS_PART.CLEANUP_GIDX（必要な索引をすべて行うことも特定のテーブルだけでも可能）
  • ALTER INDEX REBUILD [PARTITION]（クリーンアップではなく再構築を行う）
  • ALTER INDEX [PARTITION] COALESCE CLEANUP（親がない索引ブロックをクリーンアップする）

ただし、ALTER INDEX文（2番目と3番目）は、クリーンアップが必要な索引を調べる必要があります。索引のメンテナンスが必要かは、列’ORPHANED_ENTRIES’が’YES’かで確認することが可能です。 この機能は、以下のような制限があるので注意してください。

  • ヒープ表（通常の表）のみ使用できる（索引構成表は使用できない）
  • オブジェクト型やドメイン索引を含む表はサポートされない
  • ユーザーSYSでは実行されない

（４）パーティション表の部分索引
最後に、グローバル索引をローカル索引のように使用できる機能について説明します。
索引を効果的に使用するには、不要なパーティションなどに索引を使用しないことで、索引のメンテナンス・コストを削減することです。ただし、グローバル索引は、ローカル索引のように、対象パーティションの索引をUNUSABLEにすることができません。そのため、Oracle12cから表パーティションごとに索引を作成できるように、CREATE TABLE文とCREATE INDEX文を以下のように拡張しています（ただし、一意索引は、部分索引にすることができません）。

  • CREATE TABLE文のINDEXING句（索引付けプロパティで、デフォルトは’ON’です）
  • CREATE INDEX文のINDEXING PARTIAL（部分索引の指定で、デフォルトは全索引のINDEXING FULLです）

以下は、パーティション’p99’に対して、索引付けプロパティを’OFF’（索引を作成しない）にして、グローバル非パーティション索引（パーティション表に対する通常の索引）を部分索引として作成している例です。これで索引付けプロパティが’ON’のパーティション（p01、p02、p03）だけ索引が作成されます。

SQL> CREATE TABLE tab1 (c1 NUMBER, c2 NUMBER, c3 CHAR(1000))
  2  PARTITION BY RANGE (c1) (
  3   PARTITION p01 VALUES LESS THAN (100),
  4   PARTITION p02 VALUES LESS THAN (200),
  5   PARTITION p03 VALUES LESS THAN (300),
  6   PARTITION p99 VALUES LESS THAN (MAXVALUE) INDEXING OFF);
SQL> CREATE INDEX tab1_ix1 ON tab1 (c2) GLOBAL INDEXING PARTIAL;


この指定の確認は、以下のようにビュー（USER_TAB_PARTITIONSやUSER_INDEXESなど）で行うことができます。

tsushima-43-2

  これを使用することで、第34回で説明した表拡張をグローバル索引でも使用することが可能です。以下のSQLは、パーティション表’tab1’の索引付けプロパティが’ON’と’OFF’のパーティション（p03とp99）にアクセスしています。実行計画では、表拡張によって索引付けプロパティが’ON’のパーティション’p03’だけが、索引アクセスになっています。ただし、グローバル非パーティション索引なので、索引のパーティション・プルーニングは行いません（TBL$OR$IDX$PART$NUMファンクションを使用して、アクセスするパーティションを特定しています）。

tsushima-43-3

  ローカル索引では、UNUSABLEを設定するのと同じ動作になるので、どちらを使用しても問題ありませんが、パーティション表に索引の有無を指定できるので、運用性を考えるとこちらの方が良いと言えます。


それから、索引付けプロパティの変更は、ALTER TABLE文のMODIFY PARTITIONを使用することで可能です。以下の例は、パーティション’p99’の索引付けプロパティを’OFF’から’ON’に変更しています。このとき索引も更新されるので、再構築する必要はありません（ローカル索引とグローバル索引のどちらも対象パーティションだけが更新されます）。

SQL> ALTER TABLE tab1 MODIFY PARTITION p99 INDEXING ON;
SQL> SELECT … FROM user_tab_partitions WHERE table_name = 'TAB11' AND partition_name = 'P99';

PARTITION_NAME INDEXING
-------------- --------
P99            ON


索引付けプロパティを’OFF’にする場合には、ローカル索引はUNUSABLEになりますが、グローバル索引は非同期メンテナンスが動作するので、メタデータだけが更新されます（以下のように列’ORPHANED_ENTRIES’が’YES’になります）。そのため、グローバル索引を使用しても問題ないようになっています。

tsushima-43-4

これによって、グローバル索引でもパーティションごとに索引が使用できるので、よりグローバル索引が利用しやすくなっています。例えば、バッチ処理で大量に更新されるパーティションは、グローバル索引も含めて索引を使用しないことで、索引をメンテナンスしないようにできます。また、ローカル索引が影響しない操作（EXCHANGE PARTITIONなど）は、索引作成時にローカル索引はINDEXING FULL、グローバル索引はINDEXING PARTIALにするような使い方も可能です。

2. パーティション・メンテナンスのOracle Database 12cの新機能

次に、パーティションのメンテナンスでOracle12cから拡張された便利な機能について説明しましょう。
最近では、24時間運用などによりメンテナンス時間やバッチ時間を確保できないなど、パーティション表を使用してもメンテナンスが難しくなっていると思います。そのため、できるだけ短時間でメンテナンスする必要がありますが、サイズが大きいパーティションや複数のパーティションをメンテナンスするような場合などは長時間になってしまい、その間パーティションにアクセスできません（または読込み専用になります）。そこで、メンテナンスを効果的に行うために、Oracle12cから以下の機能が追加になっているので説明します。

  • （１）オンラインでのパーティション移動
  • （２）複数パーティションのメンテナンス

（１）オンラインでのパーティション移動
パーティションの移動は、データの移動や表圧縮などで使用しますが、そのパーティションが読取り専用になってしまうので（パーティションに対して第18回で説明したTMエンキューを排他モードで取得するからです）、誰も変更しないようなときに実行する必要がありました。Oracle12cからは、以下のようにONLINE句を指定することで、読取り専用が解除されます。このときグローバル索引に対しても非同期メンテナンスが動作するので、索引のメンテナンス時間も短縮されます（以下のように列’ORPHANED_ENTRIES’が’YES’になります）。

tsushima-43-5

 

これも索引メンテナンスをしていませんが、以下のように移動したパーティションも索引アクセスすることが可能です（非同期メンテナンスと同じように、TBL$OR$IDX$PART$NUMファンクションを使用します）。

SQL> SELECT * FROM tab1 WHERE c2 = 1 AND c1 < 100;
実行計画
———————————————————————————
| Id  | Operation                                  | Name     | | Pstart| Pstop |
———————————————————————————
|   0 | SELECT STATEMENT                           |          | |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TAB1     | |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                         | TAB1_IX1 | |       |       |
———————————————————————————
Predicate Information (identified by operation id):
—————————————————
   1 – filter(“C1″<100)
   2 – access(“C2″=1)
       filter(TBL$OR$IDX$PART$NUM(“TAB1″,0,8,0,”TAB1”.ROWID)=1)


ONLINE句を指定しない場合には、グローバル索引のメンテナンスは今まで通りの動作になります（これも非同期で行っても良い気がしますが、ONLINE句を使用してくださいということでしょう）。それから、この機能には、以下のような制限があるので注意してください。

  • SYSが所有するテーブルや索引構成表などには指定できない（索引構成表はALTER TABLE文のMOVE ONLINEで可能）
  • パラレルDMLとダイレクト・パス・インサートは使用できない（表を排他ロックするから）
  • サブ・パーティションを含むパーティションには指定できない（サブ・パーティション単位で指定可能）

（２）複数パーティションのメンテナンス
メンテナンスを考慮した時系列のパーティションにしている場合には、複数のパーティションを同時にメンテナンスするようなことは、サブ・パーティションをメンテナンスする以外はあまりないと思います（あるパーティションのすべてのサブ・パーティションをメンテナンスするような場合には、パーティション単位に行うことができるので問題にはなりません）。ただし、時系列のパーティション以外では、バッチ処理などで複数のパーティションのデータを再作成する場合などがありますが、一つのパーティションしか行うことができないので、効果的ではありませんでした。また、ALTER TABLE文を同時に実行することもできません（詳細は「パーティションのメンテナンスについて」を参照してください）。そのため、Oracle12cから以下の操作に対して、複数パーティションを同時にメンテナンスすることが可能になっています（赤字が拡張されたところです）。特に、TRUNCATE PARTITIONとDROP PARTITIONは、ミニ・チェックポイントが発生するので、これを使用する効果が大きいです。

— 複数パーティションの追加
ALTER TABLE tab1 ADD PARTITION p01 VALUES LESS THAN (100), 
                     PARTITION p02 VALUES LESS THAN (200), 
                     PARTITION p03 VALUES LESS THAN (300);
— 複数パーティションの削除
ALTER TABLE tab1 DROP PARTITIONS p01, p02, p03;
— 複数パーティションのTRUNCATE
ALTER TABLE tab1 TRUNCATE PARTITIONS p01, p02, p03;
— 複数パーティション（3つ以上）のマージ
ALTER TABLE tab1 MERGE PARTITIONS p01, p02, p03 INTO PARTITION p04;
— 複数パーティション（3つ以上）への分割
ALTER TABLE tab1 SPLIT PARTITION p04 INTO (PARTITION p01 VALUES LESS THAN (100),
                                           PARTITION p02 VALUES LESS THAN (200),
                                           PARTITION p03 VALUES LESS THAN (300));


その他の操作（COALESCE、MOVE、EXCHANGE）には指定できませんが、MOVE PARTITIONはオンライン処理が可能、EXCHANGE PARTITIONはメタデータだけの変更になるので、COALESCE PARTITION以外は問題ないと思います（COALESCE PARTITIONは、ハッシュ・パーティションからパーティション数を1つ削減する操作になるので、使用する機会はあまり多くないと思います）。索引のメンテナンスは、単一パーティションの操作と同じ動作になるので、同じような注意が必要です。

パーティションのメンテナンスについて
ご存知ない方のために、ここでパーティションのメンテナンスについて簡単に説明します。 DDLは、第27回で説明したように、共有カーソルをINVALIDにする必要があるので、ライブラリ・キャッシュ上のオブジェクトに対して、’library cache lock’を排他モードで取得します。通常のDDLは、瞬時に終了するのであまり気にする必要はありませんが、第15回で説明したようにTRUNCATE TABLE文はミニ・チェックポイント（オブジェクト・レベルのチェックポイント）を行います（これはDROP TABLE文でも行います）。そのため、ダーティ・ブロックが多いと長時間そのオブジェクトにアクセスすることができなくなります（ハードパース時に’library cache lock’を共有モードで取得するからです）。これは、ALTER TABLE文のパーティション・レベルでも同じように、オブジェクトに対して’library cache lock’を排他モードで取得して、TRUNCATE PARTITIONとDROP PARTITIONではミニ・チェックポイントを行います。そのため、大きなパーティションだと長時間になってしまう場合があります（MOVE PARTITIONについては、新しいデータ領域にコピーするので、読取り専用でアクセスできるようになっています）。そして、このときの排他制御のレベルは、TMエンキューを排他モードで取得するのはパーティション・レベルですが、’library cache lock’を排他モードで取得するのはテーブル・レベルになります（これを知らない方が多いように思います）。そのため、複数のパーティションを同時に行っても’library cache lock’待ちになってしまうことに注意してください。

3. おわりに

今回はパーティションのグローバル索引とメンテナンスについて説明しましたが、少しは参考になりましたでしょうか。今年も頑張りますのでよろしくお願いします。 それでは、次回まで、ごきげんよう。

ページトップへ戻る▲ 

 

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