Oracle 11g インターバル・パーティションに関する検証 その３
＜Oracle 11g インターバル・パーティションに関する検証 その３＞
ペンネーム： ミラニスタ
どのパーティションにも格納（マップ）されない新規レコードに対して、
Oracleが自動的に適切なパーティションを追加してくれるインターバル・パー
ティションを検証しています。
▼ 前回のおさらい
・数値間隔と時間間隔のインターバル・パーティションを作成した。
・パーティションの上限値をInsertした場合に、新規パーティションが自動的
に追加されることを確認した。
・新規パーティションの上限値は、既存パーティションの上限値にインターバ
ルを加算した値となっていることを確認した。
▼ あえて逆順にInsertするとどうなるか？
前回作成したインターバル・パーティション表を題材に検証してみます。
Case 1：
テーブル名 ： inter01（カラムは c1 numberのみ）
インターバル ： 10
第1パーティション
上限値 ： 11未満
テーブル作成直後の状態
+----------------+----+------------+
| PARTITION_NAME | C1 | HIGH_VALUE |
+================+====+============+
| SYS_P01 * | | 11 |
+----------------+-----------------+
* パーティション名はOracleが勝手に付けるので、必ずしも上のようになる
とは限りません。
通常レンジ・パーティション表には、キー値の昇順にレコードをInsertする
場合がほとんどだと思います。
Insert順 = ( 1, 11, 21, 31, 41, 51, 61, 71, 81, 91)
+----------------+----+------------+ | PARTITION_NAME | C1 | HIGH_VALUE | +================+====+============+ | SYS_P01 | 1 | 11 | <= 1件目Insert +----------------+-----------------+ | SYS_P02 | 11 | 21 | <= SYS_P01追加、2件目Insert +----------------+-----------------+ .................................................................. +----------------+-----------------+ | SYS_P10 | 91 | 101 | <= SYS_P10追加、10件目Insert +----------------+-----------------+
これを、わざと逆順にInsertするとどうなるでしょうか？
Insert順 = (91, 81, 71, 61, 51, 41, 31, 21, 11, 1)
従来の、レンジ・パーティション表のように
「HIGH_VALUE未満」のパーティションにレコードが格納される。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
という仕様ならば、以下のようになるはずです。
1) 初期状態
TABLE_NAME PARTITION_NAME HIGH_VALUE ---------- --------------- --------------- RANGE01 SYS_P01 11
2) 1件目（C1=91）をInsertしようとすると、マップするパーティションがな
いので、SYS_10をADD PARTITION
TABLE_NAME PARTITION_NAME HIGH_VALUE ---------- --------------- --------------- RANGE01 SYS_P01 11 RANGE01 SYS_P10 101 <= パーティション追加
3) 1件目のInsert成功！
4) 2件目（C1=81）をInsertしようとすると、今度はマップするパーティショ
ンがある（SYS_P10）のでInsertは成功！
5) 以後、3件目から9件目まで、SYS_10にInsertされる。
6) 10件目だけがSYS_P01にInsertされる。
実際に、（インターバルでない）通常のレンジ・パーティション表（RANGE01）
を作成し逆順にInsertさせてみました。
統計情報を取得（第2回参照）し、各パーティション毎のレコード件数（NUM_ROWS）
を確認したところ以下のようになりました。
TABLE_NAME PARTITION_NAME NUM_ROWS
---------- --------------- ----------
RANGE01 SYS_P01 1 <= 10件目Insert
RANGE01 SYS_P10 9 select
2 SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE
3 ,HEADER_BLOCK,BLOCKS
4 from DBA_SEGMENTS
5 where OWNER='INTPART'
6 order by PARTITION_NAME;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE HEADER_BLOCK BLOCKS
--------------- --------------- --------------- ------------ ----------
INTER01 SYS_P66 TABLE PARTITION 427 8
HEADER_BLOCKとはセグメント・ヘッダーのブロックIDです。この値とセグメ
ントを構成するブロック数（BLOCKS）を確認することで、領域確保の状況を把
握します。
1-2) テーブル作成直後のパーティションの状況（USER_TAB_PARTITIONSを確認）
SQL> select 2 TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE 3 from USER_TAB_PARTITIONS; TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE --------------- --------------- ------------------ ----------- INTER01 SYS_P66 1 11
PARTITION_POSITIONとは表内のパーティションの位置です。現在INTER01表に
はパーティションが一つしかないので「1」です。
2-1) 1件目のレコードをInsert（C1=91）
SQL> insert into INTER01 values (91); 1行が作成されました。
2-2) セグメントとパーティションの状況を確認
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE HEADER_BLOCK BLOCKS --------------- --------------- --------------- ------------ ---------- INTER01 SYS_P66 TABLE PARTITION 427 8 INTER01 SYS_P67 TABLE PARTITION 435 8 TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE --------------- --------------- ------------------ ----------- INTER01 SYS_P66 1 11 INTER01 SYS_P67 2 101
C1=91は第1パーティション（SYS_P66）の上限値（HIGH_VALUE<11）よりも大
きいので、新規パーティション（SYS_P67）がADDされました。（HIGH_VALUE insert into INTER01 values (81);
1行が作成されました。
3-2) セグメントとパーティションの状況を確認
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE HEADER_BLOCK BLOCKS --------------- --------------- --------------- ------------ ---------- INTER01 SYS_P66 TABLE PARTITION 427 8 INTER01 SYS_P67 TABLE PARTITION 435 8 INTER01 SYS_P68 TABLE PARTITION 443 8 TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE --------------- --------------- ------------------ ----------- INTER01 SYS_P66 1 11 INTER01 SYS_P67 3 101 INTER01 SYS_P68 2 91
通常のレンジ・パーティションであれば、パーティション（SYS_P67）の上
限値は（HIGH_VALUE<101）なので、（C1=81）のレコードはパーティション
（SYS_P67）に格納されてもおかしくないのですが、インターバル・パーティ
ションでは、（HIGH_VALUE<91）となるパーティション（SYS_P68）がちゃんと
追加されています。
冒頭に挙げたINTER01表の定義条件に再注目してください。
インターバル ： 10
念のため、前回紹介したDDLも掲載しておきます。
CREATE TABLE inter01(c1 number) PARTITION BY RANGE(c1) INTERVAL(10) (PARTITION VALUES LESS THAN(11));
この INTERVAL(10) が鍵となるようです。少し整理してみましょう。
各パーティションが格納する値の範囲（RANGE）を以下のように単純化して
考えてみます。（格納される値は正数という前提にします。）
PARTITION_NAME RANGE HIGH_VALUE --------------- -------- ----------- SYS_P66 1～ 10 11 SYS_P67 91～100 101
この状態では「81」という値を格納できるパーティションはありません。
そこで
PARTITION_NAME RANGE HIGH_VALUE --------------- -------- ----------- SYS_P66 1～ 10 11 SYS_P67 91～100 101 SYS_P68 81～ 90 91 insert into INTER01 values (61); 1行が作成されました。
SQL> insert into INTER01 values (51); 1行が作成されました。 ........................................ SQL> insert into INTER01 values (11); 1行が作成されました。 SQL> insert into INTER01 values (1); 1行が作成されました。 SQL> commit; コミットが完了しました。
3-2) セグメントとパーティションの状況を確認
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE HEADER_BLOCK BLOCKS --------------- --------------- --------------- ------------ ---------- INTER01 SYS_P66 TABLE PARTITION 427 8 INTER01 SYS_P67 TABLE PARTITION 435 8 INTER01 SYS_P68 TABLE PARTITION 443 8 INTER01 SYS_P69 TABLE PARTITION 451 8 INTER01 SYS_P70 TABLE PARTITION 459 8 INTER01 SYS_P71 TABLE PARTITION 467 8 INTER01 SYS_P72 TABLE PARTITION 475 8 INTER01 SYS_P73 TABLE PARTITION 483 8 INTER01 SYS_P74 TABLE PARTITION 491 8 INTER01 SYS_P75 TABLE PARTITION 499 8 10行が選択されました。 TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE --------------- --------------- ------------------ --------------- INTER01 SYS_P66 1 11 INTER01 SYS_P67 10 101 INTER01 SYS_P68 9 91 INTER01 SYS_P69 8 81 INTER01 SYS_P70 7 71 INTER01 SYS_P71 6 61 INTER01 SYS_P72 5 51 INTER01 SYS_P73 4 41 INTER01 SYS_P74 3 31 INTER01 SYS_P75 2 21 10行が選択されました。
パーティション名を見ればパーティションが作成された順番がわかり、セグ
メント・ヘッダーのブロックIDも直前のパーティションのブロックIDに8ブロッ
クだけ加算した値になっていることがわかります。
ところが、PARTITION_POSITIONはパーティションの作成順とは関係なく
HIGH_VALUEの値に対応して昇順にメンテナンスされています。なかなか賢いで
す！
▼ 通常表にInsertした場合と比べる
比較のために、通常表を作成し同じようなInsertをさせてみます。
SQL> CREATE TABLE NORML01(c1 number); 表が作成されました。 SQL> insert into NORML01 values (91); 1行が作成されました。 SQL> insert into NORML01 values (81); 1行が作成されました。 ........................................... SQL> insert into NORML01 values (11); 1行が作成されました。 SQL> insert into NORML01 values (1); 1行が作成されました。 SQL> commit; コミットが完了しました。
○ Insertしたデータの確認
Insertが終わったらデータを参照してみます。
各行の物理的な位置を確認するために、ROWIDも表示列に含めます。
また、autotraceで実行計画も確認しましょう。
まずは、通常表から。。。
SQL> select rowid,c1 from NORML01; ROWID C1 --------------------- ---------- AAARWP AAE AAAAH/ AAA 91 AAARWP AAE AAAAH/ AAB 81 AAARWP AAE AAAAH/ AAC 71 AAARWP AAE AAAAH/ AAD 61 AAARWP AAE AAAAH/ AAE 51 AAARWP AAE AAAAH/ AAF 41 AAARWP AAE AAAAH/ AAG 31 AAARWP AAE AAAAH/ AAH 21 AAARWP AAE AAAAH/ AAI 11 AAARWP AAE AAAAH/ AAJ 1 ↑^^^^ ↑^ ↑^^^^ ↑^ ↑ ↑ ↑ ブロック内の行 ↑ ↑ ブロック番号 ↑ データファイル番号 オブジェクト番号 10行が選択されました。
ROWID（拡張ROWID）は、4つの部分から構成されており、各部分の説明は上
の通りです。（例では見易いように各部分の間にスペースを空けています。）
各行は同じブロックにInsertされ、格納順にROWIDが割り当てられています。
問合せの結果も、格納順に返ってきています。
実行計画
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 30 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| NORML01 | 10 | 30 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
統計
----------------------------------------------------------
0 db block gets
8 consistent gets
10 rows processed
Operation列には「TABLE ACCESS FULL」が指定され、10行の結果を得るため
の読み込みブロック数(‘db block gets’+’consistent gets’)は8です。（実行
計画等の見方については割愛します。）
次に、インターバル・パーティション表。。。
SQL> select rowid,c1 from INTER01; ROWID C1 --------------------- ---------- AAARWF AAE AAAAGv AAA 1 AAARWO AAE AAAAH3 AAA 11 AAARWN AAE AAAAHv AAA 21 AAARWM AAE AAAAHn AAA 31 AAARWL AAE AAAAHf AAA 41 AAARWK AAE AAAAHX AAA 51 AAARWJ AAE AAAAHP AAA 61 AAARWI AAE AAAAHH AAA 71 AAARWH AAE AAAAG/ AAA 81 AAARWG AAE AAAAG3 AAA 91 10行が選択されました。
通常表の場合とはだいぶ様子が違います。
この結果から言えることは以下のようになります。
1. 各行は別々のオブジェクト（セグメント）すなわち別々のブロックに格納さ
れている。
2. 各行はブロックの先頭行に格納されている。（ブロック内の行=AAA）
3. ソート条件を指定していないのにC1値の昇順に結果が返ってきている。
C1列はパーティション・キー列ですが、特にこの列にインデックスは作成し
ていません。だからなぜC1列でソートされているのか気になります。
そもそも、パーティション・キーって暗黙的にインデックスが作成されるの
でしょうか？
実行計画
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 30 | 15 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 10 | 30 | 15 (0)| 00:00:01 | 1 |1048575|
| 2 | TABLE ACCESS FULL | INTER01 | 10 | 30 | 15 (0)| 00:00:01 | 1 |1048575|
-----------------------------------------------------------------------------------------------
統計
----------------------------------------------------------
0 db block gets
70 consistent gets
10 rows processed
実行計画、統計情報で気付いた点は
1. 「PARTITION RANGE ALL」というオペレーションが追加されている。
2. 通常表の場合と比べ Cost: 3 → 15、読み込みブロック数: 8 → 70 と
I/O数は増えている。
3. 実行計画にPstart、Pstopという列が追加されている。
といったところでしょうか？
だいぶ長くなってしまいましたので、今回は終わりにします。
次回は今回の検証内容をもう少し掘り下げて行きたいと思います。
A.C.ミラン、ロナウジーニョの得点でミラノダービー勝利！！
恵比寿より