Oracle 11g インターバル・パーティションに関する検証 その７
＜Oracle 11g インターバル・パーティションに関する検証 その７＞
ペンネーム： ミラニスタ
どのパーティションにも格納（マップ）されない新規レコードに対して、
Oracleが自動的に適切なパーティションを追加してくれるインターバル・パー
ティションを検証しています。
▼ 前回のおさらい
○ インターバル・パーティションの謎（その１）
USER_PART_TABLES.PARTITION_COUNT列になぜパーティション数が格納されて
いないのだろうか？「1048575(0xFFFFF)」というのはどのような数なのだろう
か？
○ インターバル・パーティションの謎（その２）
「表におけるパーティションの位置」になぜ「2147483648(0x80000000)」か
ら始まる値が設定されているのだろうか？
このうち、謎（その２）に関しては、「2147483648(0x80000000)」から始ま
る値は、パーティションの絶対位置、つまり有限個のパーティション１つ１つ
に対して、パーティション・キー値の順番に割り当てられる「絶対番号」であ
ることがわかりました。
パーティションの（絶対）番号と何番目のパーティションかという相対位置
の実装については、以下のようになります。
1. インターバル・パーティション表において、パーティション・キー値から
Insertされるレコードが格納されるパーティション（第nパーティション）
が決まる。
2. 該当する第nパーティションというのは、第1パーティションのHIGH_VALUE
値とインターバル値によって（恐らく数学的に）決まる。
3. 第nパーティションが存在すれば、当該レコードはそのままInsertされるが、
もし存在しない場合は新規にパーティションが追加される。
4. 新規パーティション追加時、内部表sys.tabpart$表へのInsert文が発行さ
れる。
5. その際、tabpart$.part#列には
part# = 2147483648(0x80000000) + (n – 2)
となる絶対値がセットされる。
6. パーティション表に対する問合せでは、パーティション表内の相対位置
(tabpartv$.part#の値)が使用される。
マニアックな世界で恐縮ですが、詳しくはバックナンバーを参照してくださ
い。
▼ インターバル・パーティションの謎（その１）に迫る！！
USER_PART_TABLES.PARTITION_COUNT列について、前回までの検証で明らかに
なったことをおさらいしておきます。
1. partobj$.partcnt列は、USER_PART_TABLES.PARTITION_COUNT列に相当し、
「表に含まれるパーティション数」を示す。（リファレンス・マニュアル
から）
2. この列には、パーティション表作成時およびパーティション追加時に値が
追加・更新されるが、その値は常に「1048575(0xFFFFF)」である。
つまり、現在のパーティション数とは関係なく、常に「1048575(0xFFFFF)」
が設定されている。
このように、パーティション数と関係なくUSER_PART_TABLES.PARTITION_COUNT
の値に「1048575(0xFFFFF)」が設定されるというのは、インターバル・パーティ
ション特有の仕様なのでしょうか？
そこで、通常のレンジ・パーティション表にパーティションを追加した際の
SQLトレースを取得し、内部SQLとバインド変数を比較してみましょう。
==========================================================
テーブル名 ： RANGE01（カラムは c1 numberのみ）
第1パーティション
上限値 ： 11未満
==========================================================
SQL> CREATE TABLE RANGE01 (c1 number) 2 PARTITION BY RANGE(c1) 3 (PARTITION VALUES LESS THAN(11)); 表が作成されました。 SQL> SELECT 2 TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE 3 FROM USER_TAB_PARTITIONS 4 WHERE TABLE_NAME = 'RANGE01' 5 ORDER BY PARTITION_NAME; TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE ------------ -------------- ------------------ ------------ RANGE01 SYS_P204 1 11 SQL> SELECT OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,OBJECT_ID 2 FROM USER_OBJECTS 3 WHERE OBJECT_NAME='RANGE01'; OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID -------------- ------------------ ------------------ ---------- RANGE01 TABLE 71896 RANGE01 SYS_P204 TABLE PARTITION 71897
このレンジ・パーティション表に対して、手動でパーティションを追加して
いきます。
SQL> ALTER TABLE RANGE01 ADD PARTITION VALUES LESS THAN (21); 表が変更されました。 SQL> ALTER TABLE RANGE01 ADD PARTITION VALUES LESS THAN (31); 表が変更されました。 ................................................................... SQL> ALTER TABLE RANGE01 ADD PARTITION VALUES LESS THAN (91); 表が変更されました。 SQL> ALTER TABLE RANGE01 ADD PARTITION VALUES LESS THAN (101); 表が変更されました。 SQL> select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,OBJECT_ID 2 from USER_OBJECTS 3 where OBJECT_NAME='RANGE01'; OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID -------------- ------------------ ------------------ ---------- RANGE01 TABLE 71896 RANGE01 SYS_P204 TABLE PARTITION 71897 RANGE01 SYS_P205 TABLE PARTITION 71898 RANGE01 SYS_P206 TABLE PARTITION 71899 RANGE01 SYS_P207 TABLE PARTITION 71900 RANGE01 SYS_P208 TABLE PARTITION 71901 RANGE01 SYS_P209 TABLE PARTITION 71902 RANGE01 SYS_P210 TABLE PARTITION 71903 RANGE01 SYS_P211 TABLE PARTITION 71904 RANGE01 SYS_P212 TABLE PARTITION 71905 RANGE01 SYS_P213 TABLE PARTITION 71906 11行が選択されました。
（SQLトレースの取得要領については割愛します。）
内部SQLとバインド変数について、前々回の検証と同様にまとめてみます。
(1) SQL ID : bs0dxshj8kpnh
insert into partobj$ (obj#, parttype, partcnt, partkeycols, flags,
defts#, defpctfree, defpctused, definitrans, defmaxtrans,
deftiniexts, defextsize, defminexts, defmaxexts, defextpct,
deflists, defgroups, deflogging, spare1, spare2, parameters,
defmaxsize)
values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13,
:14, :15, :16, :17, :18, :19,
:20 * 4294967296 + :21 * 65536 + :22 * 256 + :23,
:24, :25)
partobj$表に対するInsert文で、パーティション表作成時に1回だけ実行さ
れます。バックナンバーを見返していただければわかりますが、インターバ
ル・パーティション用の内部SQLとは微妙に違っています。
○ バインド変数値まとめ（一部のみ）
obj#(Bind#1) = 71896 parttype(Bind#2) = 1 partcnt(Bind#3) = 1 partkeycols(Bind#4) = 1
partcntの値に注目してください。パーティション作成時のパーティション
数「1」が設定されています。
(2) SQL ID : 0rbcqb9pa39d1
update partobj$ set partcnt = :1 where obj# = :2
partobj$表に対するUpdate文で、パーティション追加時毎に実行されます。
○ バインド変数値まとめ *No.は実行回次
No. partcnt(Bind#1) obj#(Bind#2)
--- --------------- ------------
1 2 71896
2 3 71896
3 4 71896
4 5 71896
5 6 71896
6 7 71896
7 8 71896
8 9 71896
9 10 71896
partcnt値が、パーティション追加時に1ずつカウントアップして更新されて
いることがおわかりでしょうか？
つまり、通常のレンジ・パーティション表において、
USER_PART_TABLES.PARTITION_COUNTの値は、正確なパーティション数を表して
います。
では、なぜ、インターバル・パーティション表では正確なパーティション数
を保持せずに「1048575(0xFFFFF)」という値をPARTITION_COUNT値としているの
でしょうか？
実は、Oracle10gR1までのパーティションの最大数は64000だったのですが、
Oracle10gR2から「1048575(0xFFFFF)」に拡張されました。
「1048575」に関して種明かしを引き伸ばしてきたのですが、「最大パーティ
ション数」と言うのが答えです。
マニュアルには以下のような記述があります。
パーティション表
表は最大1024K-1（=104875）のパーティションに分割できます。
-------------------------- Oracle Database 概要 10gリリース2（10.2） B19215-02 --------------------------
通常のレンジ・パーティション表に比べインターバル・パーティション表が
大きく異なるのは
・USER_PART_TABLES.PARTITION_COUNT列に、正しいパーティション数ではなく
最大パーティション数（1048575）が常に設定されている。
という点にあることがわかりました。
連載第4回でご紹介しましたが、10個のパーティションに合計100件のレコー
ドを持つインターバル・パーティション表への全件検索を行った際の実行計画
は、以下のとおりとなりました。
実行計画 ---------------------------------------------------------------- | Id | Operation | Name | Rows || Pstart| Pstop | ---------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 || | | | 1 | PARTITION RANGE ALL| | 100 || 1 |1048575| | 2 | TABLE ACCESS FULL | INTER01 | 100 || 1 |1048575| ----------------------------------------------------------------
Pstopは、アクセスされるパーティション範囲の終了パーティションなので、
正しくは「10」となるはずなのに、なぜか「1048575」が設定されている。
というのが、疑問を持ったそもそものきっかけでした。
なぜ、このようなことになっているのでしょう？一緒に理由を考えてみま
しょう！
通常のレンジ・パーティションは「まず初めにパーティションありき」の
パーティションです。レコードは予め用意されたパーティションにInsertさ
れます。
従って、DBAは手動でパーティションを追加しておく必要がありますし、今
いくつのパーティションがあるかということも、DBAは知っています。
一方、インターバル・パーティションは「初めにデータありき」のパーティ
ションです。
どんなパーティションが作成されるかは、「データだけが知っている」ので
す。
必ずしも時系列に順番のデータがInsertされるわけではありませんし、DBA
が寝ている間でもパーティション数が刻々と変わっている可能性があります。
そもそも、USER_TAB_PARTITIONSビューの行数を数えれば、パーティション
数はわかるので、USER_PART_TABLES.PARTITION_COUNT列を正確なパーティショ
ン数でリアルタイムに更新する必然性もありません。
だから、「PARTITION_COUNT列は常に最大パーティション数（1048575）に固
定してしまえ！」とインターバル・パーティションの開発者が考えたとしても
不思議ではありません。
さらに、割り切って考えると、パーティション追加時の Update文は同じ値
で更新しているので、あえて実行する必要はないかもしれません。
奇しくも「パーティション数は有限である。」という事実を再認識したわけ
ですが、64000から拡張されたとはいえ、1048575という最大値はどう捉えれば
よいのでしょうか？
例えば、1ヶ月で1パーティションを消費すると仮定すると、パーティション
数の限界に達するのは 1,048,575 / 12 = 約87,400年後 ということになりま
す。
1日1パーティションであっても、1,048,575 / 365 = 約2,873年後です。
これは、十分実用的な値であると言えます。
▼ いきなり最大パーティション数を超える値をInsertしてみる！！
最大パーティション数は十分大きい！ということはわかったのですが、それ
以上のキー値がInsertされる確率は0ではありません。
冒頭のおさらいで確認したとおり、インターバル・パーティションにおいて
は、レコードが格納されるパーティション（第nパーティション）は、パーティ
ション・キー値によって機械的（数学的）に決まり、パーティションの絶対位
置を示す tabpart$.part#列には
part# = 2147483648(0x80000000) + (n – 2)
となる値が設定されているようだ。というところまで解明しました。
上記のように最大パーティション数が「1048575(0xFFFFF)」であるならば、
この値を超えるパーティション・キー値をInsertしたらどうなるでしょうか？
確認してみましょう。
==========================================================
テーブル名 ： INTER02（カラムは c1 numberのみ）
インターバル ： 1
第1パーティション
上限値 ： 2未満
==========================================================
インターバル値を「1」、第1パーティションの上限値（HIGH_VALUE）を CREATE TABLE INTER02(c1 number)
2 PARTITION BY RANGE(c1)
3 INTERVAL(1)
4 (PARTITION VALUES LESS THAN(2));
表が作成されました。
○ 値のInsert
SQL> INSERT INTO INTER02 VALUES (1048574); 1行が作成されました。 SQL> INSERT INTO INTER02 VALUES (1048575); 1行が作成されました。 ここまでは、問題なくInsertできますね。 SQL> select 2 TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE 3 from USER_TAB_PARTITIONS 4 where TABLE_NAME = 'INTER02' 5 order by PARTITION_NAME; TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE --------------- --------------- ------------------ ------------ INTER02 SYS_P201 1 2 INTER02 SYS_P202 2 1048575 INTER02 SYS_P203 3 1048576
テーブル作成時に作成されたパーティションの他に、Insertによって追加さ
れた２つのパーティションが確認できます。
この時点で、パーティション番号は最大値に達しているはずですので、次の
値をInsertさせて、挙動を確認してみます。
SQL> INSERT INTO INTER02 VALUES (1048576);
INSERT INTO INTER02 VALUES (1048576)
*
行1でエラーが発生しました。:
ORA-14401: 挿入されたパーティション・キーが指定されたパーティション
の範囲外です。
やはり、最大値「1048575」を超える値は格納することができませんでした。
パーティション数は「3」なのに、もうこれ以上のパーティション追加はで
きないということになりました。
▼ 内部SQLとバインド変数を確認する
それでは、トレースファイルを参照して、上記DML実行時の内部SQLとバイン
ド変数を確認します。
その前に、テーブルおよびパーティションのオブジェクト番号を確認してお
きましょう。
SQL> SELECT OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,OBJECT_ID 2 FROM USER_OBJECTS 3 WHERE OBJECT_NAME='INTER02'; OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID -------------- ---------------- ------------------ ---------- INTER02 TABLE 71892 INTER02 SYS_P201 TABLE PARTITION 71893 INTER02 SYS_P202 TABLE PARTITION 71894 INTER02 SYS_P203 TABLE PARTITION 71895
SQL ID : 9hp6m1g7j275b
insert into tabpart$ (obj#, dataobj#, bo#, part#, hiboundlen,
hiboundval, ts#, file#, block#, pctfree$, pctused$, initrans,
maxtrans, flags, analyzetime, samplesize, rowcnt, blkcnt,
empcnt, avgspc, chncnt, avgrln, bhiboundval)
values
(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15,
:16, :17, :18, :19, :20, :21, :22,
EMPTY_BLOB()) returning bhiboundval into :23
・初期パーティション作成時およびパーティション追加時に発行される内部SQL
・パーティション数だけ発行される。
○ バインド変数値（一部のみ）*No.は実行回次
No. obj#(Bind#1) bo#(Bind#3) part#(Bind#4)
--- ------------ ----------- -------------
1 71893 71892 10
2 71894 71892 2148532220
3 71895 71892 2148532221
事前に想定していたとおり、part#の最大値を超える、「2148532222」とい
うパーティション絶対番号に対応するパーティション・キー値「1048576」を
Insertすることはできませんでした。（成功したDMLのみがトレースに出力さ
れているため。）
繰り返しになりますが、インターバル値が1ということは通常有り得ないの
で、インターバル・パーティションのパーティション番号が知らない間に最大
値に達してしまったというようなことは、恐らくないと考えてよいでしょう。
今回は、これで終わりです。
次回は、インターバル・パーティションに関連して、情報ライフサイクル
管理（ILM:Information Lifecycle Management）という考え方の紹介をして
本連載の結びとしたいと思います。
次の３連休は家族サービスの予定です！
恵比寿より