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

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

どのパーティションにも格納(マップ)されない新規レコードに対して、
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&gt; 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.ミラン、ロナウジーニョの得点でミラノダービー勝利!!
恵比寿より