前節では、既にあるインデックスの列の順番を変えることで、インデックスから得られるメリットを大きくする方法について説明しました。 しかし、例に挙げたのは2つのSQL文だけでした。本来は、インデックスを変更 すると、そのインデックスが作成されたテーブルに対する全てのクエリが影響を受けます。この節では、データベースがどのようにインデックスを選ぶのかを 説明し、既存のインデックスを変更することによる副作用を見ていきます。
前節で採用したEMPLOYEES_PKインデックスは、
子会社だけで検索する全てのクエリのパフォーマンスを改善します。実際のところこのインデックスは、他にどのような検索条件が追加されたと
しても、SUBSIDIARY_IDを検索に使う全てのクエリに使うことができます。つまり、where句の他の部分で
他のインデックスを使っていたクエリに対しても有効になるということです。この場合、複数のアクセスパスがある時には、オプティマイザがどの
インデックスが最適かを判断する役目を持ちます。
インデックスを変更すると、好ましくない副作用が出る場合もあります。 ここでは、社内用の社員検索アプリケーションが合併後から非常に遅くなってしまった例を考えてみましょう。まず最初に、以下のクエリがスローダウンの 原因でないかどうかを調べます。
SELECT first_name, last_name, subsidiary_id, phone_number
FROM employees
WHERE last_name = 'WINAND'
AND subsidiary_id = 30実行計画は以下のようになります。
例2.1変更後のプライマリキーを使った場合の実行計画
---------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 30 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 30 |
|*2 | INDEX RANGE SCAN | EMPLOYEES_PK | 40 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='WINAND')
2 - access("SUBSIDIARY_ID"=30)実行計画では、インデックスを使っており、その合計コストは30と出ています。まあまあの結果でしょう。ただし、変更したばかりの
インデックスを使っている点が少々気になるところです。これは、インデックスの変更がパフォーマンスに影響を及ぼしている可能性を
疑うには十分だと言えるでしょう。変更前のインデックスが
EMPLOYEE_IDから始まっていたことを覚えていればなおさらです。つまり、where句に含まれていない列から
始まっていたわけです。従って、以前はこのクエリはインデックスを使えなかったはずです。
詳しい調査のためには、変更前後の実行計画を比べてみるのがよいでしょう。元々の実行計画を見るために、以前のインデックス定義をもう一度 適用してみることもできますが、多くのデータベースでは、あるクエリに対してインデックスを使わないようにする簡単な方法が提供されています。 以下は、Oracleにおけるその機能であるオプティマイザヒントの例です。
SELECT /*+ NO_INDEX(EMPLOYEES EMPLOYEES_PK) */
first_name, last_name, subsidiary_id, phone_number
FROM employees
WHERE last_name = 'WINAND'
AND subsidiary_id = 30インデックスの変更前に使われていたであろう実行計画では、インデックスを使っていませんでした。
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 477 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 477 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='WINAND' AND "SUBSIDIARY_ID"=30)TABLE ACCESS FULLでは、全テーブルを
読み込む必要がありますが、この場合はインデックスを使うよりも高速なようです。1行のみに一致するクエリでは、こういったことはあまり
ないことです。1行のみを検索するのにインデックスを使うのは、フルテーブルスキャンより高速なはずですが、今回はそうではありませんでした。
インデックスが悪さをしているように思えます。
このような時には、問題のある実行計画を順に追っていきましょう。
まず最初は、EMPLOYEES_PKインデックスでのINDEX RANGE
SCANです。このインデックスは、LAST_NAMEを含んでいませんので、INDEX
RANGE SCANは SUBSIDIARY_IDのフィルタだけを使っているものと考え
られます。Oracleでは、これについて“Predicate Information”(述語情報)の欄に、今回の場合は実行計画のエントリ2番として
書かれています。ここで、各処理にどの条件が適用されたかがわかります。
ヒント
付録A, 「実行計画」,では、他のデータベースにおける“Predicate Information”(述語情報)の確認方法について取り上げています。
ID 2(例2.1)のINDEX RANGE
SCANでは、SUBSIDIARY_ID=30
フィルタだけを適用しています。つまり、SUBSIDIARY_IDが
30の最初のエントリを見つけるために、インデックスツリーをたどります。それから、その子会社に属する全てのエントリを見つけるために、
リーフノードチェーンをたどります。INDEX RANGE SCANの
結果は、SUBSIDIARY_IDの条件を満たすROWIDsの
リストになります。子会社の規模によって、このリストは数行かもしれませんし、数百行かもしれません。
次は、TABLE ACCESS BY INDEX ROWIDの処理です。
前段階で得たROWIDを使って、テーブルから行(の全ての列)を
取得します。LAST_NAME列が得られたら、データベースはwhere句の残りの部分を評価します。つまり、データベースは
LAST_NAMEのフィルタを適用する前に、SUBSIDIARY_ID=30
に一致する全ての行を取得する必要があることになります。
SQL文の応答時間は、結果のサイズではなく、該当する子会社の従業員数に依存します。子会社に従業員が少数しかいない場合、
INDEX RANGE SCANのパフォーマンスは比較的良くなります。
しかし、大きな子会社の場合はTABLE ACCESS FULLの方が、
テーブルから大きなデータを一度に読みだすので、より速くなる可能性もあります(「フルテーブルスキャン」を参照)。
協力してください
この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。
インデックスの探索で、従業員一人一人にあたるROWIDsを
たくさん返す場合には、その後データベースが1行ずつデータを読み出すことに
なるので、クエリは遅くなります。インデックスを遅くする、完璧な組み合わせ
――それが、広い範囲のインデックス探索と、たくさんの行を1行ずつ読み出す処理です。
最適な実行計画を選ぶには、テーブル上のデータの分散状態にも依存 します。そのため、オプティマイザはデータベースの中身についての統計情報も 利用します。今回の例では、子会社間の従業員の分散状態を含むヒストグラムが使われます。これにより、オプティマイザはインデックス探索でどのくらいの 行が返されるかを見積もり、それをコスト計算に使うことができます。
データが削除されるなどして統計情報が使えない時には、オプティマイザは
デフォルト値を使います。Oracleのデフォルト統計情報は、中程度の選択性と小さなインデックスの組み合わせです。デフォルトを使う場合、
INDEX RANGE SCANが40行を返すと見積もります。
実行計画では、この見積もりはRows列に表示されます(改めて例2.1を参照)。すぐ分かるように、子会社に1000人の従業員がいた場合には、
これはひどく過小な見積もりになってしまいます。
正しい統計情報を提供できているうちは、オプティマイザはよい仕事をしてくれます。以下の実行計画では、INDEX
RANGE SCANでは1000行を返すという、新しい見積もりをしていることが分かります。
その結果、次のテーブルアクセスにより高いコストがかかると算出しています。
---------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 680 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 680 |
|*2 | INDEX RANGE SCAN | EMPLOYEES_PK | 1000 | 4 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='WINAND')
2 - access("SUBSIDIARY_ID"=30)680というコスト値は、FULL TABLE SCANを使った時のコスト値(477)よりも大きくなって
いるので、オプティマイザは自動的にFULL TABLE SCANを選択します。
協力してください
この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。
この遅いインデックスの例で、正しいインデックスを作成することが最適な解決策でないと言いたいわけではありません。もちろん、姓で
検索するには、LAST_NAMEに作ったインデックスが役に立ってくれることでしょう。
CREATE INDEX emp_name ON employees (last_name)新しいインデックスを使った場合、オプティマイザはコスト値を3と見積もります。
例2.2専用のインデックスを付けた場合の実行計画
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 3 |
|* 2 | INDEX RANGE SCAN | EMP_NAME | 1 | 1 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SUBSIDIARY_ID"=30)
2 - access("LAST_NAME"='WINAND')オプティマイザの見積もりによると、インデックスを使うと1行しかアクセスしないようです。データベースは、テーブルから1行しか
読み込まないのです。これは、間違いなくFULL TABLE SCAN
よりも高速です。正しく定義されたインデックスは、元のフルテーブルスキャンよりもやはり高速でした。
例2.1と例2.2の2つの実行計画は、ほとんど同じ結果になりました。2つ目の実行計画の方が
パフォーマンスは良いですが、データベースは同じ処理を行い、オプティマイザは同じようなコスト値を算出しました。INDEX RANGE SCANの
効率は、かなり広い範囲に渡って変わり、特にテーブルアクセスが後に続く時に
顕著に変わります。インデックスを使えば、SQL文が最も効率のよい方法で実行されるとは限らないのです。
