Oracle 11gR2의 Zero-Size Unusable Indexとテーブル拡張(Table Expansion)

Christian AntogniniがここからOracle 11gR2の新機能の一つのZero-size Unusable Indexに対する簡単な紹介とオプティマィザに及ぶ影響について説明しています。

Zero-Sized Unusable Indexというのは使用不可インデックスまたはインデックスパーティションのセグメント空間を物理的に解除することを意味しています。次に簡単な例があります。

バージョンはOracle 11gR2です。

UKJA@UKJA1120> select * from v$version where rownum <= 1;
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

パーティションテーブルを作ってからローカルインデックスを作ります。

UKJA@UKJA1120> create table t1(c1 int, c2 int)
  2  partition by range(c1) (
  3        partition p1 values less than (10000),
  4        partition p2 values less than (20000),
  5        partition p3 values less than (30000),
  6        partition p4 values less than (maxvalue)
  7  );

Table created.

UKJA@UKJA1120> insert into t1 select level, level from dual connect by level <= 30000;

30000 rows created.

Elapsed: 00:00:00.14
UKJA@UKJA1120> 
UKJA@UKJA1120> create index t1_n1 on t1(c1) local;

Index created.

Elapsed: 00:00:00.17
UKJA@UKJA1120> 
UKJA@UKJA1120> @gather t1
UKJA@UKJA1120> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false);

最後のパーティションP4を除いて残りの三つのパーティションを使用不可の状態に変えます。

UKJA@UKJA1120> 
UKJA@UKJA1120> alter index t1_n1 modify partition p1 unusable;

UKJA@UKJA1120> alter index t1_n1 modify partition p2 unusable;

UKJA@UKJA1120> alter index t1_n1 modify partition p3 unusable;

USER_SEGMENTSビューを問い合わせてみると使用不可状態のインデックスパーティション自体が存在しないのが分かります。空間節約次元で望ましい動作方式と言えます。

UKJA@UKJA1120> select partition_name, bytes
  2  from user_segments where segment_name = 'T1_N1';

PARTITION_NAME            BYTES
-------------------- ----------
P4                     10485760

ここからもう一つの魔法のようなものが発生します。つぎのようにパーティションP3とP4をかかる条件節を持ったクエリを実行します。万一Oracle 10gだったら全表走査を選択するしかないでしょう。でもOracle 11gR2では次のようにUNION ALLに変化された完璧な実行計画が生成されます。

UKJA@UKJA1120> explain plan for
  2  select count(*) from t1
  3  where c1 between 29999 and 30001;

-------------------------------------------------------------
| Id  | Operation                 | Name    | Pstart| Pstop |
-------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |       |       |
|   1 |  SORT AGGREGATE           |         |       |       |
|   2 |   VIEW                    | VW_TE_2 |       |       |
|   3 |    UNION-ALL              |         |       |       |
|   4 |     PARTITION RANGE SINGLE|         |     4 |     4 |
|   5 |      INDEX RANGE SCAN     | T1_N1   |     4 |     4 |
|   6 |     PARTITION RANGE SINGLE|         |     3 |     3 |
|   7 |      TABLE ACCESS FULL    | T1      |     3 |     3 |
-------------------------------------------------------------

インデックスが正常的に存在するパーティションP4に対しては索引走査を、インデックスが存在しないパーティションP3に対しては全表走査を修行します。こんな動作方式はパーティション数に関わらず良く修行されます。

UKJA@UKJA1120> alter index t1_n1 rebuild partition p2;

UKJA@UKJA1120> explain plan for
  2  select count(*) from t1
  3  where c1 between 19999 and 30001;

--------------------------------------------------------------
| Id  | Operation                  | Name    | Pstart| Pstop |
--------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |       |       |
|   1 |  SORT AGGREGATE            |         |       |       |
|   2 |   VIEW                     | VW_TE_2 |       |       |
|   3 |    UNION-ALL               |         |       |       |
|   4 |     CONCATENATION          |         |       |       |
|   5 |      PARTITION RANGE SINGLE|         |     4 |     4 |
|   6 |       INDEX RANGE SCAN     | T1_N1   |     4 |     4 |
|   7 |      PARTITION RANGE SINGLE|         |     2 |     2 |
|   8 |       INDEX RANGE SCAN     | T1_N1   |     2 |     2 |
|   9 |     PARTITION RANGE SINGLE |         |     3 |     3 |
|  10 |      TABLE ACCESS FULL     | T1      |     3 |     3 |
--------------------------------------------------------------

この動作方式を見てこの一語を浮かべたらオプティマィザの動作方式についてある程度理解していると言えます。"Transformation!"

これを確認するため10053トレースを修行してみます。その結果です。

TE: Checking validity of table expansion for query block SEL$1 (#0)

***********************************
Cost-Based Table Expansion
***********************************
TE: Checking validity of TE for query block SEL$1 (#1)
TE: Checking validity of table expansion for query block SEL$1 (#1)

TE: after table expansion:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM  
(
 (SELECT 0 FROM "UKJA"."T1" "T1" 
  WHERE TBL$OR$IDX$PART$NUM("UKJA"."T1",0,0,65535,"T1".ROWID)>=4 AND 
  TBL$OR$IDX$PART$NUM("UKJA"."T1",0,0,65535,"T1".ROWID)<=4 
  AND "T1"."C1"<= 30001 AND NULL IS NULL 
  AND ("T1"."C1">=30000 OR NULL IS NOT NULL)) 
 UNION ALL  
 (SELECT 0 FROM "UKJA"."T1" "T1" 
  WHERE TBL$OR$IDX$PART$NUM("UKJA"."T1",0,0,65535,"T1".ROWID)>=3 
  AND TBL$OR$IDX$PART$NUM("UKJA"."T1",0,0,65535,"T1".ROWID)<=3 
  AND "T1"."C1">= 29999 AND "T1"."C1"<30000)) 
"VW_TE_1"

テーブル拡張(Table Expansion)という技法を通じて数個のパーティションをかかる条件節がUNION ALLに変形されたのが分かります。Oracle 11gR2で新しく追加されたこの技法は_OPTIMIZER_TABLE_EXPANSIONパラメターで制御されます。

UKJA@UKJA1120> alter session set "_optimizer_table_expansion" = false;

UKJA@UKJA1120> explain plan for
  2  select count(*) from t1
  3  where c1 between 19999 and 30001;

----------------------------------------------------------
| Id  | Operation                 | Name | Pstart| Pstop |
----------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |
|   1 |  SORT AGGREGATE           |      |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |     2 |     4 |
|   3 |    TABLE ACCESS FULL      | T1   |     2 |     4 |
----------------------------------------------------------

もう一つの注意するところはテーブル拡張(Table Expansion)もコストベースで定義されていることです。万一費用計算で不利な値が出たら上の例でも全表走査を選択されたかも知れません。

Oracle 11gR2にまたどんな隠れた改善点が見つかるか楽しみですね。