Oracle 11g インターバル・パーティションに関する検証 その１
＜Oracle 11g インターバル・パーティションに関する検証 その１＞
ペンネーム： ミラニスタ
今週から、インターバル（時間隔）パーティションについて検証します。
インターバル・パーティションとは、一言で言えば「Oracleが新規レコード
の値を見て、新規パーティションが必要だったら勝手にパーティションを追加
してくれる。」機能です。
▼ ある事例から
本題に入る前に、筆者が以前経験したことに基づくフィクションをご紹介し
たいと思います。（もちろんOracle11g以前のお話です。）
あるサイトにおいて、特定の処理が以前と比べてだんだん遅くなっていると
いうことを聞き、調査をしました。
遅くなっているという処理は、ある大規模履歴表のうち直近の３ヶ月分を集
計するというものでした。
勘のいい読者の皆さんは、ここでレンジ・パーティションを使っているとい
うことにお気づきになったかと思いますが、当該表は１ヶ月単位でパーティ
ション化されているテーブルでした。
そのサイトはカットオーバー後すでに３年半以上が経過し、やっと安定稼動
してきたという状況だったのですが、その処理に関しては月ごとにパフォーマ
ンスが悪化していました。
データベースにログインして確認してみると、以下のようなパーティション
構成になっていました。（あくまでも実話に基づくフィクションですので、話
を簡単にするため、２００５年１月に運用を開始したということにします。）
USER_TAB_PARTITIONSからの情報
+----------------+--------------+ | PARTITION_NAME | HIGH_VALUE | +================+==============+ | P200501 | 2005/02/01 | +----------------+--------------+ | P200502 | 2005/03/01 | +----------------+--------------+ | P200503 | 2005/04/01 | +----------------+--------------+ ................................. +----------------+--------------+ | P200710 | 2007/11/01 | +----------------+--------------+ | P200711 | 2007/12/01 | +----------------+--------------+ | P200712 | MAXVALUE | | | | | | | | | | +----------------+--------------+ パーティション数：３６（３６ヶ月分）
ヒアリングしてみると、以下のようなことが判明しました。
・２００５年当時構築を担当された方は退職され、物理設計書等は整備されて
いない。
・最終パーティション（P200712）に含まれるレコード数（NUM_DISTINCT）は
その他のパーティションに比べ８倍以上となっていた。（２００８年８月に
調査）
どうやら、構築担当者は構築時に３年分のパーティションを作成したものの
運用手順を十分に引き継ぐことなく退職してしまい、残された運用担当者は月
ごとのパーティションをメンテナンスする運用なぞ夢にも思わず、最終パー
ティションに新規レコードがInsertされ続けてしまった。というのが事の真相
でした。
レンジ・パーティションの場合、Insertされるレコードはパーティション・
キー値がHIGH_VALUE未満のパーティションに格納されます。
これをパーティション定義で説明すると
CREATE TABLE
( )
PARTITION BY RANGE ()
( PARTITION P200501 VALUES LESS THAN (TO_DATE('2005-02-01','yyyy-mm-dd'))
, PARTITION P200502 VALUES LESS THAN (TO_DATE('2005-03-01','yyyy-mm-dd'))
..........................................................................
, PARTITION P200711 VALUES LESS THAN (TO_DATE('2007-12-01','yyyy-mm-dd'))
, PARTITION P200712 VALUES LESS THAN (MAXVALUE)
);
となります。
ディクショナリから実際の定義を確認すると以下のようになります。
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from USER_TAB_PARTITIONS;
TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- --------------- ----------------------------------------
PART_TBL P200501 TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART_TBL P200502 TO_DATE(' 2005-03-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
.........................................................................
PART_TBL P200711 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART_TBL P200712 MAXVALUE
^^^^^^^^
最終パーティションを
………………………………………………………………..
, PARTITION P200712 VALUES LESS THAN (TO_DATE(‘2008-01-01′,’yyyy-mm-dd’))
………………………………………………………………..
と定義してもよいのですが、
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions;
TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- --------------- ----------------------------------------
PART_TBL P200501 TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART_TBL P200502 TO_DATE(' 2005-03-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
.........................................................................
PART_TBL P200711 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART_TBL P200712 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
「2008-01-01」のレコードをInsertしようとした途端、
SQL> insert into PART_TBL values (TO_DATE('2008-01-01','yyyy-mm-dd'));
insert into PART_TBL values (TO_DATE('2008-01-01','yyyy-mm-dd'))
*
行1でエラーが発生しました。:
ORA-14400:
挿入されたパーティション・キーはどのパーティションにもマップされません
が発生してしまいます。つまり行き場のないレコードは格納できません。
恐らく構築担当者はエラーが発生しないようにこのような設定にしたのでし
ょう。（誰も元旦から障害対応したくないですからね。）
ただし、エラーが発生しないようにしたおかげで、”問題”に気付くのが遅
くなってしまいました。
問題というのは何でしょうか？
そうですね。せっかくパーティション化したテーブルなのに、新規レコード
が全て最終パーティションにInsertされてしまうため、最終パーティションが
どんどん肥大化してしまうことでした。
そこで、以下のように肥大化した最終パーティションを分割して、パーティ
ション化本来の目的を達成する必要があります。
+----------------+--------------+ +----------------+--------------+
| PARTITION_NAME | HIGH_VALUE | | PARTITION_NAME | HIGH_VALUE |
+================+==============+ +================+==============+
| P200711 | 2007/12/01 | | P200711 | 2007/12/01 |
+----------------+--------------+ +----------------+--------------+
| P200712 | MAXVALUE | | P200712 | 2008/01/01 |
| | | ====> +----------------+--------------+
| | | | P200801 | 2008/02/01 |
| | | +----------------+--------------+
+----------------+--------------+ | P200802 | 2008/03/01 |
+----------------+--------------+
| P200803 | 2008/04/01 |
+----------------+--------------+
.................................
+----------------+--------------+
| P200xxx | MAXVALUE |
+----------------+--------------+
パーティション分割は「ALTER TABLE … SPLIT PARTITION句」により可能で
す。（詳細はマニュアル等で確認してください。）
この事例の場合、パーティション分割で問題を解決することができると思い
ます。
しかし、将来追加されるデータに関しては、最終パーティションにデータを
格納してからパーティションを分割するより、予め空の新規パーティションを
追加しておく方がリスクの少ない運用ができるでしょう。
ということでパーティション追加についても確認しておきましょう。
▼ くせ者「MAXVALUE」
マニュアルには以下のような記述があります。
– レンジ・パーティション表へのパーティションの追加 –
「ハイエンド（既存の最後のパーティションの後ろのポイント）に新しいパー
ティションを追加するには、ALTER TABLE … ADD PARTITION文を使用します。
表の最初または中間にパーティションを追加するには、SPLIT PARTITION句を
使用します。」
-------------------------- Oracle Database VLDBおよびパーティショニング・ガイド 11gリリース1（11.1） E05787-01 --------------------------
テーブルに新規パーティションを追加するには、ALTER TABLE … の後に
ADD PARTITION VALUE LESS THAN '上限値' or MAXVALUE
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
を指定する必要があります。
その結果パーティションの上限値（USER_PART_COL_STATISTICS.HIGH_VALUE）
には指定した値が設定されます。
再び、マニュアルから。。。
「既存の上位パーティションにある各パーティション化キーのパーティション
の上限がMAXVALUEの場合、表にパーティションを追加できません。そのかわり、
split_table_partition句を使用して、表の始めまたは中間にパーティション
を追加します。」
-------------------------- Oracle Database SQL言語リファレンス 11g リリース1（11.1） E05750-02 --------------------------
ここで、整理してみましょう。
1. あるパーティションの上限値としてMAXVALUEを設定すると、そのパーティ
ションは必ず最終パーティションとなる。
2. 1.のパーティションの次に新規パーティションを追加することはできない
ので、パーティションが肥大したらsplit_table_partition句で適正なサイ
ズに分割する必要がある。
3. 上限値としてMAXVALUEを指定しない場合、最終パーティションの上限値を
超えるキー値のデータをInsertしようとすると、ORA-14400エラーが発生す
る。
4. ORA-14400エラーの発生を未然に防ぐには、MAXVALUEでない上限値を指定し
た新規パーティションを予め追加しておく運用が必要である。
いずれにせよ、パーティション表というのは、最初に定義した後そのまま放
置せずに、パーティションの分割や追加といったメンテナンスが必要になりま
す。。。というのがOracle10g以前のレンジ・パーティションの注意点でした。
▼ その後の顛末。。。
上に紹介した事例、その後どうなったかということですが、月毎の専用パー
ティションを作成するという（元来）の方針に沿った運用とするため
1. サービス停止
2. 最終パーティション(P200712)・データのエクスポート
3. 最終パーティションの削除(DROP PARTITION)
4. 正しいパーティションの再作成(P200712～P200808,ADD PARTITION)
5. 将来のための新規パーティション追加(P200809～,ADD PARTITION)
*最新パーティションにはMAXVALUEを指定しない
6. 3.によりステータスが「UNUSABLE」となったインデックスを再作成
7. データのインポート（正しいパーティションにデータが格納される）
8. 統計情報の取得
9. サービスの再開
というメンテナンス作業を実施しました。
手順どおりに実施すれば特にリスクの高い作業ではないのですが、エクス
ポートしたデータは１件でもインポート・ミスが許されないものであったため
それなりに緊張が強いられる作業でした。（あっ、これってフィクションのつ
もりでしたね。）
そして、新規パーティションの追加については、定期的に手動で実行してい
ただくようスクリプトを作成し、運用担当者に対する教育を行いました。
パーティション追加のスクリプトをcron等で月次処理として自動実行するよ
うな運用も当然考えられるのですが、万一スクリプトの実行に失敗した場合の
監視やリカバリの仕組みを考えなければならず、パーティション表の運用は意
外と手間がかかるということを実感した次第です。
▼ インターバル・パーティションというソリューション
Oracle11g となって実現された”メンテナンス・フリー”となる（レンジ）
パーティションが、今回検証する「インターバル・パーティション」です。
マニュアルには「時間隔パーティション」という名称で記述されています
が、特に時間軸でのみパーティション化するわけでもないので、ここでは
「インターバル・パーティション」という名前で呼んでいきたいと思います。
常にDBAがお守りをしているデータベースは全体から見ると少ないと思いま
す。DBAが介在することなく、パーティション表の運用がきちんとできるとい
うこの機能の意義は大きいと思います。
導入だけで随分長くなってしまいましたが、次回から「インターバル・パー
ティション」の全貌に迫っていきたいと思います。
最近、自宅に無線LAN導入。快適です！！
恵比寿より