Oracle 11g インターバル・パーティションに関する検証 その２
＜Oracle 11g インターバル・パーティションに関する検証 その２＞
ペンネーム： ミラニスタ
インターバル・パーティションとは、一言で言えば「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://old.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が追加さ
れています。 ^^
これで、大体の感じはわかっていただけましたでしょうか？
次回はいろいろ変則的なことをやって遊んでみたいと思います。
彼岸でも蝉が鳴いてる温暖化。。。
恵比寿より