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

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

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

▼ 前回のおさらい

Case 1:
テーブル名 : INTER01(カラムは c1 numberのみ)
インターバル : 10
第1パーティション
上限値 : 11未満
所有者 : INTPART

というインターバル・パーティション表INTER01に対して

Insert順 = (91, 81, 71, 61, 51, 41, 31, 21, 11, 1)

という値をInsertし、各値にマップするパーティションが作成されていること
を確認しました。(わざと逆順にInsert)

  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

その後、INTER01表に対する全件検索を実施すると、

  SQL> select c1 from INTER01;
  
          C1
  ----------
           1
          11
          21
          31
          41
          51
          61
          71
          81
          91

  10行が選択されました。

のように、Insert順ではなく、パーティション・キーでソートされた結果とな
りました。

パーティション・キーには暗黙的にインデックスが作成されて、このような
結果になったのでしょうか?
といった疑問が湧いてきたところで前回は終わりました。

▼ 100~1の100件をInsertしてみる。

前回は、1パーティションに1件となるようなInsertでしたが、今度は1パー
ティションに10件Insertされるよう100~1の100件をInsertしてみることにし
ます。

1. 表の削除
INTER01表を一旦削除します。

SQL> drop table INTER01 purge;

2. SQLトレース取得の有効化
10046イベントを設定して、SQLトレースを取得できるようにします。これ
は、パーティション追加操作によってどんな内部SQLが発行されているかを
確認するためです。

  SQL> alter session set events '10046 trace name context forever, level 4';

level 4 はバインド変数の内容をトレースファイルに出力します。

3. インターバル・パーティションの再作成
INTER01表を前回と同様に再作成します。

  SQL> CREATE TABLE inter01(c1 number)
    2  PARTITION BY RANGE(c1)
    3  INTERVAL(10)
    4  (PARTITION VALUES LESS THAN(11));

4. レコードのInsert(100~1の100件)

  SQL> insert into inter01 values (100);
  SQL> insert into inter01 values (99);
  SQL> insert into inter01 values (98);
  ......................................
  SQL> insert into inter01 values (3);
  SQL> insert into inter01 values (2);
  SQL> insert into inter01 values (1);
  SQL> commit;

5. パーティションおよびオブジェクトの情報確認
まず、USER_TAB_PARTITIONSから、Insert直後のパーティションの状況を確
認してみましょう。

  SQL> select
    2   TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE
    3  from USER_TAB_PARTITIONS
    4  where TABLE_NAME = 'INTER01'
    5  order by PARTITION_NAME;
  
  TABLE_NAME  PARTITION_NAME   PARTITION_POSITION HIGH_VALUE
  ----------- ---------------- ------------------ ------------
  INTER01     SYS_P142                          1 11
  INTER01     SYS_P143                         10 101
  INTER01     SYS_P144                          9 91
  INTER01     SYS_P145                          8 81
  INTER01     SYS_P146                          7 71
  INTER01     SYS_P147                          6 61
  INTER01     SYS_P148                          5 51
  INTER01     SYS_P149                          4 41
  INTER01     SYS_P150                          3 31
  INTER01     SYS_P151                          2 21
  
  10行が選択されました。

第1パーティション(SYS_P142)の他に、4.のInsertによりSYS_P143~
SYS_P151のパーティションが追加され、全部で10個のパーティションとなった
ことがわかります。
また、HIGH_VALUE(パーティション上限値)に対応した、表内のパーティ
ションの位置(PARTITION_POSITION)も確認しておきましょう。

さらに、USER_OBJECTSからINTER01表に関するオブジェクト情報を確認しま
しょう。
パーティション表の場合、OBJECT_NAMEはテーブル名、SUBOBJECT_NAMEはパー
ティション名を示します。
一番右端の列は、ディクショナリ・オブジェクト番号ですが、後で内部SQL
の解析に必要となるため、ここでメモっておきます。

  SQL> select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,OBJECT_ID
    2  from USER_OBJECTS
    3  where OBJECT_NAME='INTER01';
  
  OBJECT_NAME   SUBOBJECT_NAME   OBJECT_TYPE       OBJECT_ID
  ------------- ---------------- ---------------- ----------
  INTER01                        TABLE                 71216
  INTER01       SYS_P142         TABLE PARTITION       71217
  INTER01       SYS_P143         TABLE PARTITION       71218
  INTER01       SYS_P144         TABLE PARTITION       71219
  INTER01       SYS_P145         TABLE PARTITION       71220
  INTER01       SYS_P146         TABLE PARTITION       71221
  INTER01       SYS_P147         TABLE PARTITION       71222
  INTER01       SYS_P148         TABLE PARTITION       71223
  INTER01       SYS_P149         TABLE PARTITION       71224
  INTER01       SYS_P150         TABLE PARTITION       71225
  INTER01       SYS_P151         TABLE PARTITION       71226
  
  11行が選択されました。

6. 2.で有効化したSQLトレースを無効化します。

SQL> alter session set events '10046 trace name context off';

▼ Insertしたデータの確認

前回と同様に、Insertしたデータを参照してみます。
各行の物理的な位置を確認するために、ROWIDも表示列に含めます。

  SQL> select rowid,c1 from INTER01;
  
  ROWID                      C1   該当パーティション
  ------------------ ---------- ================================
  AAARYxAAEAAAAGuAAA         10         ↑
  AAARYxAAEAAAAGuAAB          9         |
  AAARYxAAEAAAAGuAAC          8         |
  AAARYxAAEAAAAGuAAD          7         |
  AAARYxAAEAAAAGuAAE          6  SYS_P142 (PARTITION_POSITION=1)
  AAARYxAAEAAAAGuAAF          5         |
  AAARYxAAEAAAAGuAAG          4         |
  AAARYxAAEAAAAGuAAH          3         |
  AAARYxAAEAAAAGuAAI          2         |
  AAARYxAAEAAAAGuAAJ          1         ↓
                                ================================
  AAARY6AAEAAAAH2AAA         20         ↑
  AAARY6AAEAAAAH2AAB         19         |
  AAARY6AAEAAAAH2AAC         18         |
  .............................  SYS_P151 (PARTITION_POSITION=2)
  AAARY6AAEAAAAH2AAH         13         |
  AAARY6AAEAAAAH2AAI         12         |
  AAARY6AAEAAAAH2AAJ         11         ↓
                                ================================
  AAARY5AAEAAAAHuAAA         30         ↑
  AAARY5AAEAAAAHuAAB         29         |
  AAARY5AAEAAAAHuAAC         28         |
  ....................................................
  AAARY0AAEAAAAHGAAH         73         |
  AAARY0AAEAAAAHGAAI         72         |
  AAARY0AAEAAAAHGAAJ         71         ↓
                                ================================
  AAARYzAAEAAAAG+AAA         90         ↑
  AAARYzAAEAAAAG+AAB         89         |
  AAARYzAAEAAAAG+AAC         88         |
  .............................  SYS_P144 (PARTITION_POSITION=9)
  AAARYzAAEAAAAG+AAH         83         |
  AAARYzAAEAAAAG+AAI         82         |
  AAARYzAAEAAAAG+AAJ         81         ↓
                                ================================
  AAARYyAAEAAAAG2AAA        100         ↑
  AAARYyAAEAAAAG2AAB         99         |
  AAARYyAAEAAAAG2AAC         98         |
  AAARYyAAEAAAAG2AAD         97         |
  AAARYyAAEAAAAG2AAE         96  SYS_P143 (PARTITION_POSITION=10)
  AAARYyAAEAAAAG2AAF         95         |
  AAARYyAAEAAAAG2AAG         94         |
  AAARYyAAEAAAAG2AAH         93         |
  AAARYyAAEAAAAG2AAI         92         |
  AAARYyAAEAAAAG2AAJ         91         ↓
                                ================================
  100行が選択されました。

結果を簡単にまとめてみます。

・第1パーティションからPARTITION_POSITIONの順番に各パーティションにア
クセスされている。
・各パーティションの中ではC1値の降順(つまりレコードの格納順)に行が取
り出されている。

もし、C1列に暗黙的なインデックスが作成されているとすれば、このような
結果とならずに、C1:1→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|
  ----------------------------------------------------------------

マニュアルにはレンジ・パーティションのアクセスに関し、以下のような記
述があります

======================================================================
Oracle Databaseパフォーマンス・チューニング・ガイド
11gリリース1(11.1)
E05743-01

12.9.1 EXPLAIN PLANによるレンジ・パーティション化およびハッシュ・パー
       ティション化の表示の例

「パーティション行ソースは、表アクセス行ソースの上に作成されます。これ
が、アクセスされるパーティションのセットについて繰り返されます。...述
語がプルーニングに使用されていないので、パーティション・イテレータは
すべてのパーティション(ALL)を対象とします。」
======================================================================

これを今回の例に当てはめて考えると。。。

1. 全件検索なのですべてのパーティションがアクセス対象となる
(Operation: PARTITION RANGE ALL)
⇒ SYS_P142 → SYS_P151 → … → SYS_P144 → SYS_P143 の順番で
(PARTITION_POSITION順に)すべてのパーティションが対象となる。
2. アクセス対象となっている各パーティションのセットについて、全件検索
(Operation: TABLE ACCESS FULL)が繰り返し実行される。
3. それぞれの結果がマージされ、全体の結果として返される。

ということのようです。

さて、今度は右端2列に注目しましょう。

Pstartは「PARTITION_START」、Pstopは「PARTITION_STOP」の意味です。
それぞれ「PARTITION RANGE」操作における、開始パーティション、終了パー
ティションを示します。

「PARTITION RANGE ALL」の場合 Pstart=1 というのは理解できるのですが、
Pstop=1048575 というのはどういうことでしょうか?
この例でのPARTITION_POSITIONの最大値は「10」だったはずです。従って
Pstop=10 とならなければおかしいと思いませんか?
何かの間違いなんでしょうか?無駄なアクセスが発生したりしていれば問題
です。

この謎を解くために、上記マニュアル抜粋にも出てきた、プルーニング(Pruning)
という機能に注目したいと思います。
この言葉の意味を辞書で確認すると「枝打ち、剪定」とあります。つまり、
「不必要な部分を除外する。」というような意味になります。
そこで、絞り込み条件を付けて、特定のパーティションのみにアクセスする
ようにしてみます。

○ 絞り込み条件を指定(C1は56以上65以下)

  SQL> select c1 from INTER01
    2  where c1 between 56 and 65;
  
          C1   該当パーティション
  ---------- ================================
          60         ↑
          59         |
          58  SYS_P147 (PARTITION_POSITION=6)
          57         |
          56         ↓
            ================================
          65         ↑
          64         |
          63  SYS_P146 (PARTITION_POSITION=7)
          62         |
          61         ↓
            ================================

  10行が選択されました。

  実行計画
  ---------------------------------------------------------------------
  | Id  | Operation                | Name    | Rows  || Pstart| Pstop |
  ---------------------------------------------------------------------
  |   0 | SELECT STATEMENT         |         |    11 ||       |       |
  |   1 |  PARTITION RANGE ITERATOR|         |    11 ||     6 |     7 |
  |*  2 |   TABLE ACCESS FULL      | INTER01 |    11 ||     6 |     7 |
  ---------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------

   2 - filter("C1">=56 AND "C1"<=65)

今度は、第6と第7パーティションのみにアクセスされ(Operation: PARTITION RANGE ITERATOR)
他のパーティションはプルーニングされています。
これは、Pstart=6, Pstop=7 からわかります。

これで「PARTITION RANGE ALL」における Pstop=1048575 の謎が。。。まだ
全然わかりませんね。

参考:1048575 = 2の20乗 – 1

次回は、SQLトレースと内部表の解析から Pstop=1048575 の謎に迫っていき
ます。この謎を解明すればインターバル・パーティションにおける新規パーティ
ション割当の仕組みがわかるかもしれません。
それにしても PARTITION_POSITION って一体何なんでしょうか?

上着を着ても脱いでもどちらでもいい季節が好き!!
恵比寿より