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

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

どのパーティションにも格納(マップ)されない新規レコードに対して、
Oracleが自動的に適切なパーティションを追加してくれるインターバル・パー
ティションを検証しています。

▼ 前回のおさらい

○ インターバル・パーティションの謎(その1)
USER_PART_TABLES.PARTITION_COUNT列になぜパーティション数が格納されて
いないのだろうか?「1048575(0xFFFFF)」というのはどのような数なのだろう
か?

○ インターバル・パーティションの謎(その2)
「表におけるパーティションの位置」になぜ「2147483648(0x80000000)」か
ら始まる値が設定されているのだろうか?

このうち、謎(その2)に関しては、「2147483648(0x80000000)」から始ま
る値は、パーティションの絶対位置、つまり有限個のパーティション1つ1つ
に対して、パーティション・キー値の順番に割り当てられる「絶対番号」であ
ることがわかりました。

パーティションの(絶対)番号と何番目のパーティションかという相対位置
の実装については、以下のようになります。

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#の値)が使用される。

マニアックな世界で恐縮ですが、詳しくはバックナンバーを参照してくださ
い。

▼ インターバル・パーティションの謎(その1)に迫る!!

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によって追加さ
れた2つのパーティションが確認できます。
この時点で、パーティション番号は最大値に達しているはずですので、次の
値を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)という考え方の紹介をして
本連載の結びとしたいと思います。

次の3連休は家族サービスの予定です!
恵比寿より