津島博士のパフォーマンス講座　Indexページ ▶▶

 

皆さんこんにちは、今年の冬は本当に寒いですが、温度差も大きく体調を崩しやすいので、インフルエンザやノロ・ウイルスなどにも気を付けてください。
今回は、第33回の続きとしてオプティマイザ関連の説明で同様に行った、索引スキャンのI/OとSQL自動変換（これは第29回の続きになります）について説明しますので、参考にしてください。

1. 索引スキャンのI/Oについて
まずは、バージョン・アップで少しずつ拡張されている索引スキャンのI/Oについて説明しましょう。
索引スキャンは、少ないデータをアクセスするには効果的ですが、データが多くなるとそうではなくなってきます。これは、シングル・ブロックでI/Oを行うので、アクセスするデータ・ブロック数によって、比例的に待機時間が増えてしまうからです。一般的には、キャッシュ・ヒット率を上げることでI/O時間を削減しますが、これで改善するのも限界があるので、第13回で説明したような機能によって索引スキャンを改善しています。それの代表的なものが以下の二つのアクセスになります。

  • Pre-fetchアクセス
    索引範囲スキャンするときのデータ・ブロックを複数ブロックまとめて先読みします。これでI/Oを同時に行うことで、I/O待機を削減しますが、ランダムI/O効率を改善できる訳ではありません。そのため、クラスタ化係数（索引の順番とデータの順番の違い）が大きいと、I/O競合でかえって性能が悪くなる場合もあります。
  • Batchアクセス
    索引範囲スキャンするときのクラスタ化係数を改善するために、ROWIDをデータ・ブロック順に並べ替えてまとめてアクセスします。これによって、更にI/O待機を削減しますが、結果が索引の順番を保障できなくなります。

これもオプティマイザによって最適に決定されます。ここからは、表結合や索引スキャンでどのように使用されるか説明していきます。

(1)ネステッド・ループ結合
ネステッド・ループ結合のときの索引スキャンから説明します。
ネステッド・ループ結合は、一般的に内部表にアクセスするときに索引スキャンを行いますが、多くのデータにアクセスする場合もあるので、索引スキャンの性能に影響してしまいます。そのため、以下の機能によってI/O性能を改善しています。

  • Multi Join Key Pre-fetching（Oracle9iから）
  • Nested Loops join Batching（Oracle Database 11gから）

それぞれを説明する前に、それ以前（Oracle8i以前）の実行計画から説明します。Oracle8i以前では、以下のような実行計画になり、駆動表の1行に対して①～③の順番でアクセスして結合します（内部表が索引範囲スキャン’INDEX RANGE SCAN’のときは②と③を繰返します）。このように内部表の1行ごとに索引スキャンを行うのが、一般的なネステッド・ループ結合になります。

【Oracle8i以前】

SQL> SELECT * FROM tab2 A, tab1 B where B.c1 = A.c1;
-------------------------------------------------
| Id  | Operation                    | Name     |
-------------------------------------------------
|   0 | SELECT STATEMENT             |          |
|   1 |  NESTED LOOPS                |          |
|*  2 |   TABLE ACCESS FULL          | TAB2     | ①駆動表
|   3 |   TABLE ACCESS BY INDEX ROWID| TAB1     | ③内部表の行をアクセス（②のROWIDから）
|*  4 |    INDEX RANGE SCAN          | TAB1_IX1 | ②内部表の索引アクセス

 

次に、Oracle9iからのMulti Join Key Pre-fetchingの実行計画です。以下のように’TABLE ACCESS BY INDEX ROWID’が’NESTED LOOPS’の前に出力されます（ただし、この実行計画でもバッファ・キャッシュの状態などによって動作しない場合もあります）。ネステッド・ループ結合の駆動表の1行に対して、内部表を索引範囲スキャンする場合に、Pre-fetchアクセスを行います（②で対象のROWIDをすべて取得してから、③でそのデータ・ブロックを先読みします）。そのため、索引範囲スキャンでアクセスするデータ・ブロック数が多いと効果的です。

【Multi Join Key Pre-fetching】
SQL> SELECT * FROM tab2 A, tab1 B where B.c1 = A.c1;
————————————————
| Id  | Operation                   | Name     |
————————————————
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1     | ③内部表をPre-fetchする（②のROWIDから）
|   2 |   NESTED LOOPS              |          |
|*  3 |    TABLE ACCESS FULL        | TAB2     | ①駆動表
|*  4 |    INDEX RANGE SCAN         | TAB1_IX1 | ②内部表（駆動表の1行に対して索引アクセスする）

 

ネステッド・ループ結合は、内部表の1回の索引スキャンが少ない場合でも（索引一意スキャンなどでも）、駆動表の行数が多いと内部表にアクセスるデータが多くなってしまいます。そのため、Oracle Database 11g(Oracle11g)からのNested Loops join Batchingは、以下の実行計画のようにネステッド・ループ結合を2回に分けて行うことで（1回目は駆動表と内部表の索引で行い、2回目は1回目の結果を駆動表にして内部表のデータと行う）、内部表の必要なデータをまとめてアクセスできるので、索引一意スキャンでも効果的に行うことが可能です。そして、Batchアクセスすることで、内部表のI/Oが更に改善されます。

【Nested Loops join Batching】
SQL> SELECT * FROM tab2, tab1 where tab1.c1 = tab2.c1;
————————————————-
| Id  | Operation                    | Name     |
————————————————-
|   0 | SELECT STATEMENT             |          |
|   1 |  NESTED LOOPS                |          | Nested Loops Join (2)
|   2 |   NESTED LOOPS               |          |   Nested Loops Join (1) ==> 結合結果を駆動表(2)
|   3 |    TABLE ACCESS FULL         | TAB2     |     駆動表 (1)
|*  4 |    INDEX RANGE SCAN          | TAB1_IX1 |     内部表 (1)（索引のみにアクセス）
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB1     |   内部表 (2)（ここのI/OをBatchアクセス） 

 

ただし、物理読込みするときに、ROWIDを並べ替えてテーブルにアクセスするので、結果が索引の順番を保障しません。そのため、索引の順番にしたい場合は、Order by句を指定する必要がありますが、以下のようにNO_NLJ_BATCHINGヒントを指定することで、行わないようにすることも可能です。

SQL> SELECT /*+ NO_NLJ_BATCHING(B) */ * FROM tab2 A, tab1 B where B.c1 = A.c1;
————————————————
| Id  | Operation                   | Name     |
————————————————
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1     |
|   2 |   NESTED LOOPS              |          |
|*  3 |    TABLE ACCESS FULL        | TAB2     |
|*  4 |    INDEX RANGE SCAN         | TAB1_IX1 |
 

（２）Oracle Database 12cの索引スキャン
Oracle Database 12c（Oracle12c）から索引範囲スキャンが拡張されているので、それについて説明します。
索引範囲スキャンは、Oracle11g以前までPre-fetchアクセス（Data Block Prefetching）のみでしたが、Oracle12cからNested Loops join BatchingのようにBatchアクセスも行うようになります。これは、Batch table access by rowid機能といい、索引範囲スキャンのROWIDで表にBatchアクセスするので、アクセスするデータ・ブロック数が多い場合に、更に効果的になります。このとき実行計画には、以下のように’TABLE ACCESS BY INDEX ROWID BATCHED’と出力されます。

SQL> SELECT * FROM tab1 WHERE c1 < 10;
——————————————————–
| Id  | Operation                           | Name     |
——————————————————–
|   0 | SELECT STATEMENT                    |          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1     |
|*  2 |   INDEX RANGE SCAN                  | TAB1_IX1 |
——————————————————–
Predicate Information (identified by operation id):
—————————————————
   2 – access(“C1″<10)
 

Batchアクセスは、索引の順番を保障しないので、以下のようにOrder by句を指定すると、実行されなくなります（通常の索引範囲スキャンになるので、ソート処理は行われません）。

SQL> SELECT * FROM tab1 WHERE c1 < 10 ORDER BY c1;
------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1     |
|*  2 |   INDEX RANGE SCAN          | TAB1_IX1 |
 

以下のようにNO_BATCH_TABLE_ACCESS_BY_ROWIDヒントを指定しても、Batchアクセスしない実行計画にできます。

SQL> SELECT /*+ NO_BATCH_TABLE_ACCESS_BY_ROWID(tab1) */ * FROM tab1 WHERE c1 < 10;
 

2. SQL自動変換
次に、第29回の続きとしてオプティマイザのSQL自動変換について説明しましょう。
SQL自動変換には、第29回で説明した代表的なもの以外に、まだ多くの機能がありますが、基本的には以下のようなことを行って最適な実行計画にします。

  • 不必要な処理を排除する（必要でない処理を指定しても、無駄に行わないようにする）
  • 索引を使用可能にする（索引が使用できないときでも、UNION ALLなどで索引を使用できるようにする）
  • 結合行数を削減する（処理を副問合せに移動するなどして、事前に行数を削減できるようにする）
  • ビューのマージ（索引を使用または表結合の順番のために、副問合せをなくすようにする）

ビューのマージや結合行数の削減などは、第29回で殆ど説明したので、それ以外でOracle Database 10g(Oracle10g)R2以降からの機能について説明します。基本は自動的に行うので、あまり気にする必要はありませんが、知っておくとSQLチューニングなどで役に立つと思います。

（１）Join Elimination（結合の排除）
まずは、Oracle10gR2からの結合の排除について説明します。
表結合は、必要なデータを取得するために行いますが、データがあるテーブルに存在するかだけを、確認するために行う場合もあります。例えば、以下のように商品マスター”prod”に登録されている商品だけを、出力するような場合などです。

SQL> SELECT A.* FROM tab1 A, prod B WHERE A.pno = B.pno;
-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN         |      |
|   2 |   TABLE ACCESS FULL| PROD |
|   3 |   TABLE ACCESS FULL| TAB1 |
-----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."PNO"="B"."PNO")
 

このような場合に参照整合性制約を設定すると、主キー側に存在することを保障できるので、結合する必要がありませんが、知らずに結合を指定してしまう場合もあります。そのような場合には、Oracle10gR2から以下のように、オプティマイザが排除してくれます。以下は、参照整合性制約を追加したときの実行計画になります（赤字の結合がなくなっています）。

SQL> ALTER TABLE prod ADD CONSTRAINT pk_prod PRIMARY KEY(pno);
SQL> ALTER TABLE tab1 ADD CONSTRAINT fk_tab1 FOREIGN KEY(pno) REFERENCES prod(pno));
SQL> SELECT A.* FROM tab1 A, prod B WHERE A.pno = B.pno;
———————————-
| Id  | Operation         | Name |
———————————-
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| TAB1 |
———————————-
Predicate Information (identified by operation id):
—————————————————
   1 – filter(“A”.”PNO” IS NOT NULL)
 

（２）Order by Elimination（Order by句の排除）
次もOracle10gR2からの機能で、副問合せ内のOrder by句の排除について説明します。
副問合せ内のOrder by句は、表結合を行うとソート結果が保障されないので、以下のようなランキング検索（上位N件を出力したいなど）を行うような場合などに使用します。インライン・ビューでOrder by句が指定可能になったのは、Oracle8iからこのランキング検索を可能にするためです。そのため、それ以外で使用してもあまり意味がありませんが、指定するとソート処理が動作してしまうので、それを行わないようにする機能です（これも知らずに行ってしまう場合があると思います）。

SQL> SELECT * FROM (SELECT * FROM tab1 WHERE … ORDER BY c1,c2) WHERE ROWNUM <= 10;
-----------------------------------------
| Id  | Operation               | Name |
-----------------------------------------
|   0 | SELECT STATEMENT        |      |
|*  1 |  COUNT STOPKEY          |      |
|   2 |   VIEW                  |      |
|*  3 |    SORT ORDER BY STOPKEY|      |
|   4 |     TABLE ACCESS FULL   | TAB1 |
-----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
 

以下のような場合のOrder by句は意味がないので、Oracle10gR2から右側のように、オプティマイザが自動的に排除してくれます。

【Oracle10gR1以前】
SQL> SELECT COUNT(*) FROM
  2   (SELECT c1,c2 FROM tab1 ORDER BY c1,c2);
————————————-
| Id  | Operation            | Name |
————————————-
|   0 | SELECT STATEMENT     |      |
|   1 |  SORT AGGREGATE      |      |
|   2 |   VIEW               |      |
|   3 |    SORT ORDER BY     |      |
|   4 |     TABLE ACCESS FULL| TAB1 |

【Oracle10gR2以降】
SQL> SELECT COUNT(*) FROM
  2   (SELECT c1,c2 FROM tab1 ORDER BY c1,c2);
———————————–
| Id  | Operation          | Name |
———————————–
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| TAB1 |
———————————–
 

（３）DISTINCT Elimination（DISTINCT句の排除）
次に、Oracle11gからのDISTINCT句の排除について説明します。
DISTINCT句は、結果を一意にするために指定するので、データが一意の場合は指定する必要がありません。つまり、一意索引が存在する列を検索する場合には、DISTINCT句を指定する必要がありませんが、一意索引の存在を知らずに指定する場合もあります。そのような場合には、Oracle11gから以下の右側のように、オプティマイザが排除してくれますが、これは索引スキャンが行われる場合だけになります（これは第11回の「無駄なソート処理をしない」で少し説明した機能になります）。例えば、以下の場合に一意索引だけでは、INDEX FULL SCANにならないので、DISTINCT句が排除されません。これは、B-Tree索引にNULLが含まれないので、NULLを対象外にするためにNOT NULL制約などが必要になるからです（この例では、主キー’pk_tab1’を使用しています）。このようなことも重要なので、忘れないようにしてください。

【Oracle10g以前】
SQL> ALTER … ADD CONSTRAINT pk_tab1 PRIMARY KEY(c1);
SQL> SELECT DISTINCT c1 FROM tab1;
————————————–
| Id  | Operation          | Name    |
————————————–
|   0 | SELECT STATEMENT   |         |
|   1 |  SORT UNIQUE NOSORT|         |
|   2 |   INDEX FULL SCAN  | PK_TAB1 |		 【Oracle11g以降】
SQL> ALTER … ADD CONSTRAINT pk_tab1 PRIMARY KEY(c1);
SQL> SELECT DISTINCT c1 FROM tab1;
————————————
| Id  | Operation        | Name    |
————————————
|   0 | SELECT STATEMENT |         |
|   1 |  INDEX FULL SCAN | PK_TAB1 |
————————————
 

（４）Native Full Outer Join（Native方式のFull Outer Join）
次もOracle11gからの機能で、Native方式のFull Outer Joinについて説明します。
Full Outer Join（完全外結合）を行うと、Oracle10g以前まで以下の左側のように、Left Outer JoinとNOT EXISTS（アンチ結合）を使用して実現していましたが、Oracle11gから以下の右側のように、より効果的なNative方式のFull Outer Joinで行います。これは、効果的になった実行計画との違いを知って欲しいと思い載せておきました。

【Oracle10g以前】
SQL> SELECT *
  2  FROM tab1 A FULL OUTER JOIN tab2 B USING (c1);
————————————-
| Id  | Operation            | Name |
————————————-
|   0 | SELECT STATEMENT     |      |
|   1 |  VIEW                |      |
|   2 |   UNION-ALL          |      |
|*  3 |    HASH JOIN OUTER   |      |
|   4 |     TABLE ACCESS FULL| TAB1 |
|   5 |     TABLE ACCESS FULL| TAB2 |
|*  6 |    HASH JOIN ANTI    |      |
|   7 |     TABLE ACCESS FULL| TAB2 |
|   8 |     TABLE ACCESS FULL| TAB1 |
————————————-
Predicate Information (identified by operation id):
—————————————————
   3 – access(“A”.”C1″=”B”.”C1″(+))
   6 – access(“A”.”C1″=”B”.”C1″)		 【Oracle11g以降】
SQL> SELECT *
  2  FROM tab1 A FULL OUTER JOIN tab2 B USING (c1);
——————————————
| Id  | Operation             | Name     |
——————————————
|   0 | SELECT STATEMENT      |          |
|   1 |  VIEW                 | VW_FOJ_0 |
|*  2 |   HASH JOIN FULL OUTER|          |
|   3 |    TABLE ACCESS FULL  | TAB2     |
|   4 |    TABLE ACCESS FULL  | TAB1     |
——————————————
Predicate Information (identified by operation id):
—————————————————
   2 – access(“A”.”C1″=”B”.”C1″)

（５）Join Factorization（結合の因数分解）
次に、Oracle11gR2からの結合の因数分解について説明します。
UNION ALLのブランチ間で、共通な処理を使用している場合に、その処理を共有して最適化します。以下の例は、条件”A.c0 < 0″が両方のブランチに存在しています。これを左側の実行計画では、インライン・ビューを作成して、その外で1回だけ行っています。これで、共通な処理を何回も実行するような、無駄なことを行わないようにします。

tsushima-34-SQL-1

つまり、以下のSQLのように内部的に変換を行います。

SQL> SELECT A.c1, V_JF.c2
  2  FROM tab1 A, (SELECT B.c1 item_1, B.c2 FROM tab2 B, tab3 C WHERE B.c2 = C.c2
  3                UNION ALL
  4                SELECT B.c1 item_1, B.c2 FROM tab2 B, tab4 D WHERE B.c2 = D.c2) V_JF
  5  WHERE A.c1 = V_JF.item_1 AND A.c0 > 1 ;
 

（６）Table Expansion（表拡張）
次もOracle11gR2からの機能で、パーティション表に対する表拡張について説明します。
パーティション表をアクセスする場合に、アクセスするパーティションの一つでも索引が使用できない（UNUSABLEになっている）場合には、アクセスするパーティションすべてにフル・スキャンします。索引が使用できるパーティションは、索引スキャンの方が効率良いということで、Oracle11gR2からの表拡張によって、第9回のOR拡張と同じように、UNION ALLに変換して索引スキャンを行います。そのため、意図的にあるパーティションの索引をUNUSABLEにしたい場合は効果的です。例えば、あるパーティションのデータを大量に更新するような場合は、索引のメンテナンスがオーバーヘッドになってしまうので、一時的にそのパーティションの索引をUNUSABLEにして、索引メンテナンスを行わないようにする場合などです。つまり、索引が多いような場合は、効果があると思うので、検討してみてください。
以下のパーティション表’tab1’とローカル索引’tab1_ix1’に対して、パーティション’p04’のローカル索引をUNUSABLEにする例を使用して、それぞれの場合について説明していきます。

SQL> CREATE TABLE tab1 (c1 number, ...)
  2   PARTITION BY RANGE(c1) 
  3   (PARTITION p01 VALUES LESS THAN (100),
  4    PARTITION p02 VALUES LESS THAN (200),
  5    PARTITION p03 VALUES LESS THAN (300),
  6    PARTITION p04 VALUES LESS THAN (400)); 
SQL> CREATE INDEX tab1_ix1 ON tab1 (c2) LOCAL;
 

以下のSQLを実行すると、通常は条件’c1 > 200’でパーティション’p03’と’p04’にパーティション・プルーニングされ、条件’c2 = 1’で索引スキャンされます。

SQL> SELECT * FROM tab1 WHERE c1 > 200 AND c2 = 1;
-------------------------------------------------------<省略>-----------------
| Id  | Operation                          | Name     |      | Pstart| Pstop |
-------------------------------------------------------<省略>-----------------
|   0 | SELECT STATEMENT                   |          |      |       |       |
|   1 |  PARTITION RANGE ITERATOR          |          |      |     3 |     4 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TAB1     |      |     3 |     4 |
|*  3 |    INDEX RANGE SCAN                | TAB1_IX1 |      |     3 |     4 |
-------------------------------------------------------<省略>-----------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter("C1">200)
  3 - access("C2"=1)
 

これにパーティション’p04’のローカル索引をUNUSABLEにすると、Oracle11gR1以前は以下のように、パーティション’p03’と’p04’に対して、フル・スキャンになります。

SQL> ALTER INDEX tab1_ix1 MODIFY PARTITION p04 UNUSABLE;
SQL> SELECT * FROM tab1 WHERE c1 > 200 AND c2 = 1;
-----------------------------------------<省略>----------------
| Id  | Operation                | Name |      | Pstart| Pstop |
-----------------------------------------<省略>-----------------
|   0 | SELECT STATEMENT         |      |      |       |       |
|   1 |  PARTITION RANGE ITERATOR|      |      |     3 |     4 |
|*  2 |   TABLE ACCESS FULL      | TAB1 |      |     3 |     4 |
-----------------------------------------<省略>-----------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("C2"=1 AND "C1">200)
 

これがOracle11gR2以降では、以下のようにUNION ALLに変換することで、索引スキャンが可能になります。つまり、索引スキャンするパーティション’p03’とフル・スキャンするパーティション’p04’をUNION ALLで別けることで、索引スキャンを可能にします。

tsushima-34-SQL-2


3. おわりに
今回は索引スキャンのI/OとSQL自動変換の続きについて説明しましたが、少しは参考になりましたでしょうか。次回はOracle12cのオプティマイザ新機能の続きを説明しようと思いますので、よろしくお願いします。
それでは、次回まで、ごきげんよう。

 

ページトップへ戻る▲ 

 

津島博士のパフォーマンス講座　Indexページ ▶▶