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

投稿日: 2008年10月08日

<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 って一体何なんでしょうか?

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