Oracle 11g インターバル・パーティションに関する検証 その４
＜Oracle 11g インターバル・パーティションに関する検証 その４＞
ペンネーム： ミラニスタ
どのパーティションにも格納（マップ）されない新規レコードに対して、
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 って一体何なんでしょうか？
上着を着ても脱いでもどちらでもいい季節が好き！！
恵比寿より