Oracle 11g インターバル・パーティションに関する検証 その６
＜Oracle 11g インターバル・パーティションに関する検証 その６＞
ペンネーム： ミラニスタ
どのパーティションにも格納（マップ）されない新規レコードに対して、
Oracleが自動的に適切なパーティションを追加してくれるインターバル・パー
ティションを検証しています。
▼ 前回のおさらい
インターバル・パーティション表に対して、100件のレコードをInsertした
場合に発行されている内部SQLおよびバインド変数を確認しました。
今回の振り返りでは、前回謎としていたバインド変数値を16進数でも表記し
てみます。
(1) SQL ID : なし
CREATE TABLE inter01(c1 number) PARTITION BY RANGE(c1) INTERVAL(10) (PARTITION VALUES LESS THAN(11))
・インターバル・パーティション表作成のDDL本体
(2) SQL ID : d1updcq3y3087
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, spare3, interval_str, interval_bival)
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, :26,
:27, :28)
・パーティション表作成時に発行される内部SQL
・sys.part$に1レコード追加する。
○ バインド変数値（一部のみ）
obj# = 71216 parttype = 1 partcnt = 1048575(0xFFFFF) partkeycols = 1 interval_str = "10"
(3) 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# part# part#(Hex) hiboundval
--- ----- ---------- ---------- ----------
1 71217 10 0xA "11"
2 71218 2147483656 0x80000008 "101"
3 71219 2147483655 0x80000007 "91"
4 71220 2147483654 0x80000006 "81"
5 71221 2147483653 0x80000005 "71"
6 71222 2147483652 0x80000004 "61"
7 71223 2147483651 0x80000003 "51"
8 71224 2147483650 0x80000002 "41"
9 71225 2147483649 0x80000001 "31"
10 71226 2147483648 0x80000000 "21"
(4) SQL ID : 0rbcqb9pa39d1
update partobj$ set partcnt = :1 where obj# = :2
・新規パーティションが追加される度に発行されるSQL
○ バインド変数値 *No.は実行回次
No. partcnt partcnt(Hex) obj#
--- ------- ------------ -----
1 1048575 0xFFFFF 71216
2 1048575 0xFFFFF 71216
3 1048575 0xFFFFF 71216
4 1048575 0xFFFFF 71216
5 1048575 0xFFFFF 71216
6 1048575 0xFFFFF 71216
7 1048575 0xFFFFF 71216
8 1048575 0xFFFFF 71216
9 1048575 0xFFFFF 71216
1. partobj$.partcnt列は、USER_PART_TABLES.PARTITION_COUNT列に相当し、 「表に含まれるパーティション数」を示す。（リファレンス・マニュアル から） 2. この列には、パーティション表作成時およびパーティション追加時に値が 追加・更新されるが、その値は常に「1048575(0xFFFFF)」である。 つまり、現在のパーティション数とは関係なく、常に「1048575(0xFFFFF)」 が設定されている。 3. tabpart$.part#は、「表におけるパーティションの位置」のはずだが、第2 パーティション以降は、「2147483648(0x80000000)」から始まる値がInsert される。 4. tabpart$.part#値はhiboundval(=HIGH_VALUE)の大小に対応しており、パー ティションの作成順（Insert順）とは関係ない。
○ インターバル・パーティションの謎（その１）
USER_PART_TABLES.PARTITION_COUNT列になぜパーティション数が格納されて
いないのだろうか？「1048575(0xFFFFF)」というのはどのような数なのだろう
か？
○ インターバル・パーティションの謎（その２）
「表におけるパーティションの位置」になぜ「2147483648(0x80000000)」か
ら始まる値が設定されているのだろうか？
だんだん、ややこしくなってきたと思われるかもしれませんが、謎解きに
我慢してお付き合いください。
▼ tabpart$とtabpartv$？
うっかりしたら見落としそうな違いですが、Insertの対象となっていたのが
「tabpart$」、USER_TAB_PARTITIONSを構成しているのが「tabpartv$」です。
両者の違いは何でしょう？ちょっと気になります。 ^
まず、USER_TAB_PARTITIONS.PARTITION_POSITION はsys.tabpartv$.part#に
相当することがわかったので、tabpartv$に対する問合せを行ってみましょう。
それでは、OBJ#の範囲を 71217～71226 としてtabpartv$を検索してみます。
SQL> select
2 OBJ#
3 ,BO#
4 ,PART#
5 ,HIBOUNDVAL
6 from tabpartv$
7 where OBJ# between 71217 and 71226;
OBJ# BO# PART# HIBOUNDVAL
---------- ---------- ---------- ---------------
71217 71216 1 11
71226 71216 2 21
71225 71216 3 31
71224 71216 4 41
71223 71216 5 51
71222 71216 6 61
71221 71216 7 71
71220 71216 8 81
71219 71216 9 91
71218 71216 10 101
10行が選択されました。
PART#は前述のとおりUSER_TAB_PARTITIONS.PARTITION_POSITIONですが、特に
ソート順を指定したわけでもないのに1～10の昇順に行が返ってきています。
念のため、この問合せの実行計画を確認してみます。
実行計画
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | VIEW | TABPARTV$ |
| 2 | WINDOW SORT | |
| 3 | TABLE ACCESS FULL| TABPART$ |
-----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJ#">=71217 AND "OBJ#" select
2 OBJ#
3 ,BO#
4 ,PART#
5 ,HIBOUNDVAL
6 from tabpart$
7 where OBJ# between 71217 and 71226;
OBJ# BO# PART# HIBOUNDVAL
---------- ---------- ---------- ---------------
71217 71216 10 11
71218 71216 2147483656 101
71219 71216 2147483655 91
71220 71216 2147483654 81
71221 71216 2147483653 71
71222 71216 2147483652 61
71223 71216 2147483651 51
71224 71216 2147483650 41
71225 71216 2147483649 31
71226 71216 2147483648 21
10行が選択されました。
実行計画
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TABPART$ |
|* 2 | INDEX RANGE SCAN | I_TABPART_OBJ$ |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJ#">=71217 AND "OBJ#" select
2 row_number() over (partition by bo# order by part#) part#
3 from tabpart$
4 where OBJ# between 71217 and 71226;
PART#
----------
1
2
3
4
5
6
7
8
9
10
10行が選択されました。
実行計画
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | WINDOW SORT | |
| 2 | TABLE ACCESS BY INDEX ROWID| TABPART$ |
|* 3 | INDEX RANGE SCAN | I_TABPART_OBJ$ |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJ#">=71217 AND "OBJ#" insert into inter02 values (101);
1行が作成されました。
SQL> insert into inter02 values (201); 1行が作成されました。 SQL> insert into inter02 values (301); 1行が作成されました。 SQL> commit; コミットが完了しました。 SQL> insert into inter03 values (1001); 1行が作成されました。 SQL> insert into inter03 values (2001); 1行が作成されました。 SQL> insert into inter03 values (3001); 1行が作成されました。 SQL> commit; コミットが完了しました。
○ tabpart$.part#列に入るバインド変数値まとめ
テーブル名 パーティションNo. part#値
---------- ----------------- -----------
inter02 1 10
2 2147483648
3 2147483649
4 2147483650
inter03 1 10
2 2147483648
3 2147483649
4 2147483650
インターバル・パーティション表にとって、もっとも重要な「インターバル」
を変えても、tabpart$.part#列値が
第1パーティション： part# = 10 第2パーティション： part# = 2147483648(0x80000000) 第3パーティション： part# = 2147483648(0x80000000) + 1 第4パーティション： part# = 2147483648(0x80000000) + 2 ......................................................
となる仕様は変わらないようです。
言い換えれば、この値はパーティションの「絶対位置」と考えることができ
ます。
この辺で整理しておきましょう。
1. インターバル・パーティション表において、パーティション・キー値から
Insertされるレコードが格納されるパーティション（第nパーティション）
が決まる。
2. 該当する第nパーティションというのは、第1パーティションのHIGH_VALUE
値とインターバル値によって（恐らく数学的に）決まる。
3. 第nパーティションが存在すれば、当該レコードはそのままInsertされるが、
もし存在しない場合は新規にパーティションが追加される。
4. 新規パーティション追加時、内部表sys.tabpart$表へのInsert文が発行さ
れる。
5. その際、tabpart$.part#列には
part# = 2147483648(0x80000000) + (n – 2)
となる絶対値がセットされる。
6. パーティション表に対する問合せは、tabpart$表を元表とするtabpartv$
ビューの持つ、row_number関数によって変換された、パーティション表に
おける相対位置が使用される。
（=USER_TAB_PARTITIONS.PARTITION_POSITION）
=====
○ インターバル・パーティションの謎（その２）
「表におけるパーティションの位置」になぜ「2147483648(0x80000000)」か
ら始まる値が設定されているのだろうか？
=====
これに関しては、設定->絶対位置、利用->相対位置 という仕組みがある
ことがおわかりになったでしょうか？
ちょっと、難しかったですかね？
=====
○ インターバル・パーティションの謎（その１）
USER_PART_TABLES.PARTITION_COUNT列になぜパーティション数が格納されて
いないのだろうか？「1048575(0xFFFFF)」というのはどのような数なのだろう
か？
=====
こちらについては、残念ながら次回までのお楽しみとしたいと思います。
勘のいい読者の皆さんはもうお分かりでしょう！！
ヒント：第nのnってどこまで？
次回は、謎（その１）を解き明かし、通常のレンジ・パーティションとの違
いを確かめてみます。
週末は、日光まで日帰りで紅葉を見に行ってきました！
恵比寿より