Oracle 11g インターバル・パーティションに関する検証 その2

<Oracle 11g インターバル・パーティションに関する検証 その2>
ペンネーム: ミラニスタ

インターバル・パーティションとは、一言で言えば「Oracleが新規レコード
の値を見て、新規パーティションが必要だったら勝手にパーティションを追加
してくれる。」機能です。

▼ 前回のおさらい

ある事例に基づいたフィクションを紹介し、レンジ・パーティションの特徴
と運用上の注意点を挙げました。

1. あるパーティションの上限値としてMAXVALUEを設定すると、そのパーティ
ションは必ず最終パーティションとなる。
2. 1.のパーティションの次に新規パーティションを追加することはできない
ので、パーティションが肥大したらsplit_table_partition句で適正なサイ
ズに分割する必要がある。
3. 上限値としてMAXVALUEを指定しない場合、最終パーティションの上限値を
超えるキー値のデータをInsertしようとすると、ORA-14400エラーが発生す
る。
4. ORA-14400エラーの発生を未然に防ぐには、MAXVALUEでない上限値を指定し
た新規パーティションを予め追加しておく運用が必要である。

インターバル・パーティションと言っても基本はレンジ・パーティションで
あり、新規パーティションを自動的にADD PARTITIONしてくれる発展型に過ぎ
ません。
今回はこのことを理解していただくために、基本的なDDLを紹介し、新規パー
ティションが追加される様子を確認していきます。

▼ とりあえず簡単なインターバル・パーティション表を作成してみる

それでは、一番単純なインターバル・パーティション表を作成してみましょ
う。

Case 1:
テーブル名 : inter01(カラムは c1 numberのみ)
インターバル : 10
第1パーティション
上限値 : 11未満

肝心なインターバルは数値の「10」を指定します。

DDLは以下のようになります。

  CREATE TABLE inter01(c1 number)
  PARTITION BY RANGE(c1)
  INTERVAL(10)                          <==ポイント!
  (PARTITION VALUES LESS THAN(11));

また、比較のために通常のレンジ・パーティション表も作成します。
固有上限値のパーティションを1つしか持たない場合(Case 2)と、第2パー
ティションとして上限値にMAXVALUEを指定した場合(Case 3)の2パターンを
作成します。

Case 2:
テーブル名 : range01(カラムは c1 numberのみ)
第1パーティション
上限値 : 11未満

  CREATE TABLE range01(c1 number)
  PARTITION BY RANGE(c1)
  (PARTITION VALUES LESS THAN(11));

Case 3:
テーブル名 : range02(カラムは c1 numberのみ)
第1パーティション
上限値 : 11未満
第2パーティション
上限値 : MAXVALUE

  CREATE TABLE range02(c1 number)
  PARTITION BY RANGE(c1)
  (PARTITION VALUES LESS THAN(11),
   PARTITION VALUES LESS THAN(MAXVALUE));

DDLを比較してみるとインターバル・パーティションというのは、レンジ・
パーティションにINTERVAL句が追加されただけの発展型と言えます。

○ 作成直後

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from USER_TAB_PARTITIONS;

TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- --------------------
INTER01         SYS_P44         11
RANGE01         SYS_P45         11
RANGE02         SYS_P46         11
RANGE02         SYS_P47         MAXVALUE

SQL> select * from INTER01;

レコードが選択されませんでした。

SQL> select * from RANGE01;

レコードが選択されませんでした。

SQL> select * from RANGE02;

レコードが選択されませんでした。

▼ 設定の上限値をInsertした場合の挙動を確認

結果はわかりきっていますが、第1パーティションのHIGH_VALUE「11」を持
つデータは第1パーティションには格納できません。(意味するところは
HIGH_VALUE < 11 なので)
この場合の挙動を確認しておきましょう。

○ RANGE01

SQL> insert into RANGE01 values (11);
insert into RANGE01 values (11)
            *
行1でエラーが発生しました。:
ORA-14400: 挿入されたパーティション・キーはどのパーティションにもマッ
プされません。

予想通りの結果ですね。ORA-14400エラーが発生しました。

○ RANGE02

SQL> insert into RANGE02 values (11);

1行が作成されました。

SQL> commit;

コミットが完了しました。

これは、問題なくInsertできますね。後でちゃんとパーティションSYS_P47
に格納されたかどうか確認します。

○ INTER01

さあ、いよいよインターバル・パーティション表です。

SQL> insert into INTER01 values (11);

1行が作成されました。

おおっ!パーティションSYS_P44の上限値のInsertでも問題なく実行できます。
恐らく新規パーティションが作成されたんでしょう。

▼ 追加されたパーティションを確認する

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from USER_TAB_PARTITIONS;

TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- --------------------
INTER01         SYS_P44         11
INTER01         SYS_P48         21   <==新規に追加されたパーティション
RANGE01         SYS_P45         11
RANGE02         SYS_P46         11
RANGE02         SYS_P47         MAXVALUE

「SYS_P48」という新規パーティションが追加され、HIGH_VALUEには第1パー
ティションのHIGH_VALUE=11にINTERVAL(10)を加算した「21」が設定されてい
ます。

ところで、Insertしたままで、まだcommit/rollbackしてませんでした。
単純にコミットするのは面白くないので、ロールバックさせてみます。つま
り、レコードが存在しない状態に戻ります。

SQL> rollback;

ロールバックが完了しました。

SQL> select * from INTER01;

レコードが選択されませんでした。

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from USER_TAB_PARTITIONS;

TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- --------------------
INTER01         SYS_P44         11
INTER01         SYS_P48         21
RANGE01         SYS_P45         11
RANGE02         SYS_P46         11
RANGE02         SYS_P47         MAXVALUE

レコードが存在しなくても、パーティションはそのまま残っています。
後で、該当するレコードがInsertされる時に備えてパーティションを残して
いるんでしょうね。

なんか、テーブルからレコードをDeleteしても、次に同じキー値をInsert
する時に備えて、インデックスのエントリがそのまま残るのと似ていますね。
<インデックスに関する検証 その7> 参照
https://www.insight-tec.com/mailmagazine/ora3/vol015.html

一旦作成されたものは極力再利用する。コストがかかる都度のメンテナンス
はしない(この場合パーティションの削除)。というOracleのポリシーを感じ
ます。

▼ パーティションに含まれる行数の確認

それでは、今後の検証のために各パーティションに含まれるレコード件数を
確認する方法を紹介しておきます。

dbms_stats.gather_table_statsプロシージャを実行することにより、テー
ブルの統計情報を取得し、パーティションに含まれる行数を確認します。

以下のSQLをテーブル毎に実行します。

execute dbms_stats.gather_table_stats(        -
ownname          => 'INTPART',                - -- 所有者
tabname          => 'INTER01',                - -- テーブル名
partname         => null,                     - -- パーティション名指定無し
estimate_percent => null,                     - -- COMPUTEモード
method_opt       => 'FOR ALL COLUMNS SIZE 1', - -- 列ヒストグラム無し
degree           => null,                     - -- デフォルトのパラレル度
granularity      => 'default',                - -- パーティション/グローバル統計
cascade          => true);                      -- 索引統計も収集

取得した統計情報は以下のSQL文で確認することができます。

SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS from USER_TAB_PARTITIONS
     order by 1,2;

TABLE_NAME      PARTITION_NAME    NUM_ROWS
--------------- --------------- ----------
INTER01         SYS_P44                  0
INTER01         SYS_P48                  0
RANGE01         SYS_P45                  0
RANGE02         SYS_P46                  0
RANGE02         SYS_P47                  1

▼ 時間インターバルのインターバル・パーティション表作成

実際にインターバル・パーティションを使う場面を想定した場合、時間イン
ターバルのパーティションの方が多いかもしれません。

今回は、最後に時間インターバルのインターバル・パーティション表作成法
を紹介して締めくくりたいと思います。

テーブル諸元:
  テーブル名          : tmint01(カラムは itime dateのみ)
  インターバル        : 1ヶ月
  第1パーティション
  上限値              : 2008/10/01未満

DDLは次のようになります。

  CREATE TABLE tmint01(itime date)
  PARTITION BY RANGE(itime)
  INTERVAL(numtoyminterval(1,'month')) 
  (PARTITION VALUES LESS THAN (to_date('2008/10/01','yyyy/mm/dd')));
  ポイントは、インターバルの指定方法です。(INTERVAL句)
  インターバルの単位によって関数を使い分けます
  「年」「月」            :NUMTOYMINTERVAL関数
  「日」「時」「分」「秒」:NUMTODSINTERVAL関数

この場合、1ヶ月なので

numtoyminterval(1,’month’)

と指定します。(詳細はSQL 言語リファレンス11g リリース1(11.1)を参照)

○ パーティションの確認

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from USER_TAB_PARTITIONS
     where TABLE_NAME='TMINT01';

TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- ----------------------------------------
TMINT01         SYS_P49         TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-M
                                M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

‘2008-10-01 00:00:00’未満のキー値を持つレコードが、パーティションSYS_P49
に格納されます。

▼ 上限値をInsertした場合の挙動を確認

時間インターバルのインターバル・パーティション表においても、上限値の
Insertに対して新規パーティションが追加されるか確認します。

SQL> insert into TMINT01 values (to_date('2008/10/01','yyyy/mm/dd'));

1行が作成されました。

SQL> commit;

コミットが完了しました。

TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- ----------------------------------------
TMINT01         SYS_P49         TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-M
                                M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TMINT01         SYS_P50         TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-M
                                M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HIGH_VALUEが’2008-11-01 00:00:00’となるパーティションSYS_P50が追加さ
れています。 ^^

これで、大体の感じはわかっていただけましたでしょうか?

次回はいろいろ変則的なことをやって遊んでみたいと思います。

彼岸でも蝉が鳴いてる温暖化。。。
恵比寿より