前回からの続きです。今回はパーティション表に索引を付与した状態で、表のパーティションに対してTRUNCATEした場合に、索引の状態がどうなるかを調べてみました。
前回の再掲ですが、今回の実機調査(19.22.0)で使用したパーティション表、ローカル索引、グローバル非パーティション索引、グローバルパーティション索引のDDLは以下となります。
-- パーティション表のDDL CREATE TABLE tab07( id NUMBER(10) NOT NULL, ym CHAR(6) NOT NULL, cls NUMBER(5), rnk NUMBER(5), memo CHAR(50) ) PARTITION BY RANGE (ym) ( PARTITION pt_2021 VALUES LESS THAN ('202201'), PARTITION pt_2022 VALUES LESS THAN ('202301'), PARTITION pt_2023 VALUES LESS THAN ('202401'), PARTITION pt_2024 VALUES LESS THAN ('202501'), PARTITION pt_2025 VALUES LESS THAN ('202601') ); -- ローカル索引のDDL CREATE UNIQUE INDEX idx01tab07 ON tab07(id, ym) LOCAL; -- グローバル非パーティション索引のDDL CREATE INDEX idx02tab07 ON tab07(rnk) GLOBAL; -- グローバルパーティション索引のDDL CREATE INDEX idx03tab07 ON tab07(cls) GLOBAL PARTITION BY RANGE (cls) ( PARTITION pt_00000 VALUES LESS THAN (05000), PARTITION pt_05000 VALUES LESS THAN (10000), PARTITION pt_10000 VALUES LESS THAN (15000), PARTITION pt_15000 VALUES LESS THAN (20000), PARTITION pt_20000 VALUES LESS THAN (MAXVALUE) );
4. 表パーティションのTRUNCATE
指定したパーティションのみのTRUNCATEは、以下のSQLで実行できます。前回調査したダイレクトパスロードのときは、グローバル索引が存在したことでエラーとなりましたが、パーティションのTRUNCATEについては実行することができました。
SQL> ALTER TABLE tab07 TRUNCATE PARTITION pt_2023; 表が切り捨てられました。
TRUNCATE後の索引の状態を確認してみると、ローカル索引(IDX01TAB07)はUSABLE(使用可能)のままでしたが、2つのグローバル索引(IDX02TAB07, IDX03TB07)のstatus列がUNUSABLE(使用不可)になっていました。
SQL> SELECT index_name, status, orphaned_entries FROM user_indexes ORDER BY 1; INDEX_NAME STATUS ORP --------------- -------- --- IDX01TAB07 N/A NO IDX02TAB07 UNUSABLE NO IDX03TAB07 N/A NO SQL> SELECT index_name, partition_name, status, orphaned_entries FROM user_ind_partitions ORDER BY 1,2; INDEX_NAME PARTITION_NAME STATUS ORP --------------- --------------- -------- --- IDX01TAB07 PT_2021 USABLE NO IDX01TAB07 PT_2022 USABLE NO IDX01TAB07 PT_2023 USABLE NO IDX01TAB07 PT_2024 USABLE NO IDX01TAB07 PT_2025 USABLE NO IDX03TAB07 PT_00000 UNUSABLE NO IDX03TAB07 PT_05000 UNUSABLE NO IDX03TAB07 PT_10000 UNUSABLE NO IDX03TAB07 PT_15000 UNUSABLE NO IDX03TAB07 PT_20000 UNUSABLE NO
結論として、表のパーティションがTRUNCATEされると、グローバル索引は全て使用不可になるため、再作成が必要となります。
ただ、これでは面倒なので、パーティションをTRUNCATEする際に、UPDATE INDEXES句を付与することで、グローバル索引のメンテナンスが実行され、パーティションTRUNCATE後の再作成作業が不要となります。
SQL> ALTER TABLE tab07 TRUNCATE PARTITION pt_2023 UPDATE INDEXES; 表が切り捨てられました。 SQL> SELECT index_name, status, orphaned_entries FROM user_indexes ORDER BY 1; INDEX_NAME STATUS ORP --------------- -------- --- IDX01TAB07 N/A NO IDX02TAB07 VALID YES IDX03TAB07 N/A NO SQL> SELECT index_name, partition_name, status, orphaned_entries FROM user_ind_partitions ORDER BY 1,2; INDEX_NAME PARTITION_NAME STATUS ORP --------------- --------------- -------- --- IDX01TAB07 PT_2021 USABLE NO IDX01TAB07 PT_2022 USABLE NO IDX01TAB07 PT_2023 USABLE NO IDX01TAB07 PT_2024 USABLE NO IDX01TAB07 PT_2025 USABLE NO IDX03TAB07 PT_00000 USABLE YES IDX03TAB07 PT_05000 USABLE YES IDX03TAB07 PT_10000 USABLE YES IDX03TAB07 PT_15000 USABLE YES IDX03TAB07 PT_20000 USABLE YES
グローバル非パーティション索引(IDX02TAB07)のstatus列がVALID、グローバルパーティション索引(IDX03TAB07)のstatus列がUSABLEとなっていることから、先程とは違い両グローバル索引がパーティションTRUNCATE後も使用可能であることがわかります。
ただしグローバル索引のorphaned_entries列がYESになっている点に注意が必要です。これは、Oracle12cからUPDATE INDEXES句指定時のグローバル索引のメンテナンス時間を短縮するために非同期化されたことで、グローバル索引の中にTRUNCATEされたレコードの情報が残存(索引スキャン時にヒットはしない様になっているイメージ)していることを表しています。マニュアル上の表現ではグローバル索引中に孤立データが、乱暴な表現ではグローバル索引中にゴミが残った状態の様なものなので、残置しておくと索引のスキャン効率が低下するため、クリーンアップする必要があります。
デフォルトでは、毎日2:00に自動実行される自動スケジューラジョブSYS.PMO_DEFERRED_GIDX_MAINT_JOBによって、このグローバル索引中のゴミがクリーンアップされ、orphaned_entries列がNOに戻ります。自動実行ではなく、手動でクリーンアップしたい場合は、以下の手段があります。
- SYS.PMO_DEFERRED_GIDX_MAINT_JOBスケジューラジョブの手動実行
- DBMS_PART.CLEANUP_GIDXプロシージャの実行
- ALTER INDEX文によるクリーンアップ処理の実行
3のALTER INDEX文によるクリーンアップについて、以降で触れたいと思います。
5. ALTER INDEX文によるグローバル索引のメンテナンス
ALTER INDEX文を用いた索引のメンテナンス方法として、大きく4種類があります。ALTER INDEXの構文としては以下のようになります。
※グローバルパーティション索引の場合は、パーティションの指定が必要となりますが、ここでは省略します。
ALTER INDEX <索引名> <指定句>;
指定句の部分に何を指定するかによって、索引のメンテナンス範囲が変わるので、その違いを以下に纏めてみました。「索引の高さ」はBツリー索引の高さの是正を、「リーフの縮小」は索引のリーフブロックのコンパクションを、「孤立データ」はグローバル索引における孤立データのクリーンアップの可否を示しています。
| No. | 指定句 | 索引の高さ | リーフの縮小 | 孤立データ |
|---|---|---|---|---|
| 1 | REBUILD | ○ | ○ | ○ |
| 2 | COALESCE | × | ○ | × |
| 3 | COALESCE CLEANUP | × | ○ | ○ |
| 4 | COALESCE CLEANUP ONLY | × | × | ○ |
パーティションTRUNCATEにおけるグローバル索引の孤立データ(所謂ゴミ)のクリーンアップを目的とした場合、指定句にCOALESCEだけを指定したALTER INDEX文の実行では目的を達成できません。実際に実行した場合のuser_indexesディクショナリビューのorphaned_entries列の値を確認すると、YESのままになっていました。
SQL> ALTER INDEX idx02tab07 COALESCE; 索引が変更されました。 SQL> SELECT index_name, status, orphaned_entries FROM user_indexes ORDER BY 1; INDEX_NAME STATUS ORP ------------------------------ -------- --- IDX02TAB07 VALID YES
指定句にCOALESCE CLEANUP ONLYを指定した場合は、orphaned_entries列の値がNOに変わったことから、孤立データのクリーンアップができていることが確認できました。
SQL> ALTER INDEX idx02tab07 COALESCE CLEANUP ONLY; 索引が変更されました。 SQL> SELECT index_name, status, orphaned_entries FROM user_indexes ORDER BY 1; INDEX_NAME STATUS ORP ------------------------------ -------- --- IDX02TAB07 VALID NO
6. さいごに
前回の内容含め、グローバル索引は性能面でメリットがありますが、パーティション単位でのダイレクトロードができなかったり、パーティションTRUNCATEによる索引メンテナンスが必要だったりと、メンテナンス性に難があることをご理解頂けたかと思います。グローバル索引のメリット・デメリットを理解した上で、索引設計をして頂ければと思います。
次の記事：マテリアライズドビューを使ったDB移行方法を調べてみた ～ その1 ～
前回の記事： 表パーティションに対する操作で索引がどうなるか調べてみた ～ その1 ～
