 コンテンツ |
|
|
はじめに
前回の記事「Optimizing Informix® SQL: Examples and Analysis」で、私は、次の3つの方法によってIBM Informix内部照会最適化プログラムの振る舞いに影響を与えることができると指摘しました。
- IBM Informixエンジン構成パラメーターをセットアップする
- UPDATE STATISTICSを定期的に実行する
- SQLステートメントを記述する際に照会ディレクティブを使用する
最初の2項目については、実際のところあまり制御できません。IBM Informixエンジン構成パラメーターの設定を制御することは確かに可能ですが、最適化プログラムの振る舞いは、完全にIBM®
Informixエンジンによって制御されています。設定を変更しようとするならば、変更を行うためにエンジンを停止しなければなりません。UPDATE
STATISTICSについては、エンジンが最適化プログラムに収集した詳細の度合いを制御できますが、照会実行プランに対する最適化プログラムの決定に関しては何も行うことはできません。しかし、第3項目の照会ディレクティブは、照会実行プランに対する最適化プログラムの決定に直接影響を与える膨大なチャンスをもたらしてくれます。残念なことに、照会ディレクティブに注目するプログラマーや開発者の数は多くありません。
SQLステートメントやプログラムに照会ディレクティブを使用するプログラマーや開発者にいたってはさらに少ないのが現状です。
この記事では、照会ディレクティブについて詳しく述べ、照会ディレクティブの実体と、照会最適化およびパフォーマンスにおける役割を徹底的に解説します。次に、具体的な例をまじえて照会ディレクティブのはたらきを紹介します。
照会ディレクティブは、「ヒント」と呼ばれることもありますが、SQLステートメントまたは照会の特殊なコメントです。特殊という表現を使って、私はこうしたコメントが実質的に内部照会最適化プログラムの照会実行プランの選択に対する命令またはインテリジェント・ヒントであることを示しています。照会パフォーマンスを最適化する際のその役割は、一目瞭然です。照会実行プランに直接的に影響を与えることができるのです。最適化プログラムは通常、構成パラメーターと更新済み内部統計の助けを借り、照会実行プランの選択をかなり得意としています。しかし場合によっては、最適化プログラムは、その判断力に限界があるため、人的要因（照会の目的など）、全般的なパフォーマンスの考慮により、最善の選択を行う十分な処理能力を兼ね備えていないこともあります。たとえば、アプリケーション設計者として皆さんは、最適化プログラムが認識していないデータベースに関する情報を知っている場合もあります。一例をあげれば、特定の索引が同じ表の他の索引よりも選択的であること、どの結合方式が照会に適しているか、といったことを皆さんはご存知かもしれません。この情報を基に、皆さんは最適化プログラムよりも効率的な実行プランを選択できるかもしれないのです。このような場合、照会ディレクティブを使用して、皆さんの選択した照会実行プランを採用するよう最適化プログラムに強制することができます。
次の2つの方法のいずれかを使用して、SQL照会で照会ディレクティブを指定することができます。以下の例のように、照会ディレクティブの前に「--+」を入れます。
SELECT --+ORDERED
* FROM employee e, department d
WHERE e.dep_no = d.dept_no
|
あるいは、以下の例のように、照会ディレクティブを中括弧で囲むこともできます。
SELECT {+ORDERED}
* FROM employee e, department d
WHERE e.dep_no = d.dept_no
|
SQL照会に複数の照会ディレクティブを使うこともできます。たとえば、Danverで勤務する従業員全員のリストを作成する場合には、3つの表（job、department、employee）を結合して情報を取得し、department表およびemployee表で索引を使用し、job表で完全表スキャンを使用し（非常に小規模な表なので）、job表およびdepartment表で動的ハッシュ結合を使用してもよいでしょう。
この照会は、次のように記述されます。
SELECT {+ ORDERED,
INDEX(emp ixl),
FULL(job),
USE_HASH(job/BUILD),
USE_HASH(dept/BUILD),
INDEX(dept is3)}
* FROM emp, job, dept
WHERE emp.location=1
AND emp.jobno = job.jobno
AND emp.deptno = dept.deptno
AND dept.location = "DANVER"
|
照会ディレクティブを使用する前に、UPDATE STATISTICS HIGHを実行して最適化プログラムがすべての表と索引の更新済み統計を認識していることを確認します。これは重要です。正確な統計がなければ、たとえ照会ディレクティブの助けがあっても最適化プログラムは最善の照会実行プランを選択できないからです。さらに、照会が基にする表と索引、および照会に採用することになるアクセス方式と結合方式についてさらに深く考える必要があります。もちろんこれは、各自の状況に基づくものであり、従うべき確実な鉄則というものはありません。以下に、照会を記述する際に適用すべきガイドラインを示します。
- 表内のデータの多くの割合（70パーセント以上）を検索する場合には索引の使用は避けます。索引は、非常に選択的な照会に使用すべきです。表内の大量のデータを検索する場合には、データベース・サーバーがほとんどの行を読み取る必要があるのであれば、表を順次読み取るほうが（完全表スキャン）、索引を（およびデータ・ページを順次）検討してゆくよりもはるかに効率的です。たとえば皆さんが、全顧客情報を保管する100万行または200万行ある膨大な表で作業しており、アラスカ州に住んでいない顧客の名前をリストしたい場合を想定します。言い換えれば、表のデータの約90パーセントを検索しようとしています。この照会では索引を使わないほうが賢明といえます。応答時間が速くなるはずです。
- 表を結合する場合には常に、ドライビング・テーブルとして最小の行を返す表を選択します。ご存知のように、結合オペレーションは最も高価なデータベース・オペレーションの1つですから、データベース・サーバーが必要なデータを取得するために行わなければならない結合の数をできる限り減らすという発想があります。ドライビング・テーブルは、最も小さい表であるか、またはWHERE文節にフィルターを持つ表でなければなりません。たとえば、ネバダ州の顧客が注文したオーダーのリストを生成するため、「customers」と「orders」という2つの表を結合する場合には、「customers」表をドライビング・テーブルとして使用する必要があります。このようにして、サーバーはまず、照会基準を満たさない「customers」表の行を除外し、次に「orders」表との結合を行います。このようにしないと、サーバーは最初に「orders」表のすべての行を「customers」表のすべての行と結合してから、照会で指定されている基準を満たさない行を除外します。これはまさに避けたい状況です。「customers」表と「orders」表がいずれも膨大なものであれば2つの表の結合は膨大なものになり、システムに十分なメモリーがなければ非常に遅くなるか、あるいはエンジンをクラッシュさせてしまうおそれさえあります。
- ネストループ結合は、結合しようとする列が索引付けされていない場合には最も効率的です。ネストループ結合では、最初の表つまり外部テーブルが照会基準を満たす行に対してスキャンされます。外部テーブルで見つけ出されたすべての行について、データベース・サーバーは2番目の表つまり内部テーブルに対応する行を検索します。外部テーブルは、表に応じて索引スキャンまたは表スキャンによりアクセスされます。データベース・サーバーは、フィルターがあれば最初に適用します。内部テーブルに索引がなければ、データベース・サーバーは表に索引を構築するコストと順次スキャンのコストを比較して、コストの低いほうを選択します。ネストループ結合の全体コストは、結合列に索引があるかどうかで異なります。結合列に索引がある場合は、コストは大幅に低くなります。
- ハッシュ結合または動的ハッシュ結合は、結合される表がいずれも索引を持たない場合に最も効率的になります。ハッシュ結合は、IBM Informix Dynamic Server? V7以降の新しい結合方式です。これは、結合される表の一方または両方に索引がない場合、あるいはデータベース・サーバーが両方の表から多数の行を読み取る必要がある場合に使用されます。この結合では、表の一方（通常は小さいほうの表）がスキャンされ、メモリー内にハッシュ・テーブルを作成するために使われます。ハッシュ関数を使用して、各行は同じハッシュ値を持つ他の行とともに「バケット」に入れられます。最初の表がスキャンされてハッシュ・テーブルに置かれた後、2番目の表が1回スキャンされ、各行がハッシュ・テーブルで検索されて結合が行えるかどうか確かめます。
ハッシュ結合は、ハッシュ・テーブルの構築（構築フェーズ）とハッシュ・テーブルのプローブ（プローブ・フェーズ）という2つのアクティビティーから構成されています。構築フェーズでは、データベース・サーバーが1つの表を読み取り、その後、フィルターを適用し、ハッシュ・テーブルを作成します。概念としては、ハッシュ・テーブルは一連のバケットのようなもので、それぞれにハッシュ関数を適用してキー値から導かれたアドレスが入っていると考えることができます。データベース・サーバーは特定のハッシュ・バケットのキーをソートしません。データベース・サーバーは、プローブ・フェーズで結合の他の表を読み取り、フィルターを適用します。照会基準を満たす行ごとに、データベース・サーバーがキーにハッシュ関数を適用し、ハッシュ・テーブルをプローブして一致を見つけます。ソートを行う必要がないので、ハッシュ結合は通常、分類組み合わせ結合よりも高速になります。
照会ディレクティブの種類
照会ディレクティブには4つの種類があります。それぞれが照会実行プランを選択する最適化プログラムの決定の一側面に影響を及ぼします。アクセス・プラン・ディレクティブは、アクセス・プランを選択する最適化プログラムの決定に影響力を持ちます。結合順序ディレクティブは、結合順序を選択する最適化プログラムの決定に影響力を持ちます。結合プラン・ディレクティブは、結合方式を選択する最適化プログラムの決定に影響力を持ちます。最適化目標ディレクティブは、照会結果のフォーマットする最適化プログラムの決定に影響力を持ちます。では、これらのディレクティブをそれぞれさらに詳細に考察して、より適した照会実行プランを選択するよう最適化プログラムに影響を与えるために、どのようなオプションがあるか検討します。
アクセス・プラン・ディレクティブ
これらのディレクティブは、アクセス方式を扱います。IBM Informixソフトウェアがデータを検索すると、完全表スキャンまたは索引キー・スキャンを使用します。完全表スキャンでは、表全体をメモリーに読み取ってから、照会基準と一致があるかどうか一行一行内容を調べます。このアクセス方式は表の各行を調べるので、その効率は表のサイズに大きく左右されます。表が膨大なもの、たとえば10万行以上であったとすれば、それはパフォーマンスのボトルネックになるおそれもあります。この状況には、索引キー・スキャンが適切なアクセス方式であり、非常に効率的です。索引キー・スキャンは、最初に索引を読み取ります。もっと正確に言えば、索引キーをメモリーに読み込んでから、照会基準を満たす索引キーを探してバイナリー・サーチを行い、最後にこれらの索引キーに従って実際のデータを取得します。このカテゴリーには、以下の表に示すように4つのディレクティブがあります。
| ディレクティブ |
説明 |
| INDEX |
表にアクセスする際に使う索引を指定する |
| AVOID_INDEX |
表にアクセスする際に使わない索引を指定する |
| FULL |
完全表スキャンを使用して表にアクセスする |
| AVOID_FULL |
表にアクセスする際に完全表スキャンを使用しない |
上記の表に示された4つのディレクティブのうち、おそらくINDEXが最も便利なものでしょう。一般に、最適化プログラムは索引キー・スキャンを選択することをかなり得意としています。表に索引があれば常に、完全表スキャンよりも索引キー・スキャンを選択します。しかし、表に複数の索引がある場合には、照会実行プランに最適な索引を選択する能力が十分ではないこともあります。こうした状況では、INDEXディレクティブを使用して照会実行プランに最適な索引を使うよう最適化プログラムに指示することもできます。索引を選択する際の一般則は、固有性にあります。索引が固有のものであれば、それに応じてデータ検索の速度も高まります。
もちろん、これにはデータベース表と索引について理解すること、さらに照会の目的を把握することが必要になります。
結合順序ディレクティブ
このカテゴリーには、ORDEREDの1つしかディレクティブはありません。その機能は、SELECTステートメントにリストされている順序で表を結合するよう最適化プログラムを強制することです。場合によっては、このディレクティブを使用して表結合の順序のみではなくそれ以上の影響を及ぼすこともできます。つまり、結合方式を選択する最適化プログラムの決定に影響を与えることです。以下のSQLステートメントは、このディレクティブがどのように機能するか示しています。
SELECT --+ORDERED
* FROM employee e, department d
WHERE e.dept_no = d.dept_no AND e.id > 2
|
この場合、最適化プログラムはこの照会に対して動的ハッシュ結合を選択し、照会プランは次のようになります。
QUERY:
------
SELECT --+ORDERED
* FROM employee e, department d
WHERE e.did = d.id
AND e.id > 2
DIRECTIVES FOLLOWED:
ORDERED
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) informix.e: SEQUENTIAL SCAN
Filters: informix.employee.id > 2
2) informix.d: SEQUENTIAL SCAN
DYNAMIC HASH JOIN
Dynamic Hash Filters: informix.employee.did = informix.department.id
|
しかし、以下のように2つの表の順序を入れ替えると、
SELECT --+ORDERED
* FROM department d, employee e
WHERE e.dept_no = d.dept_no
AND e.id > 2
|
最適化プログラムは、動的ハッシュ結合ではなく、ネストループ結合を選択します。これは、結合列e.didの「employee」表に索引があるからです。照会プランは以下のようになります。
QUERY:
------
SELECT --+ORDERED* FROM d, e
WHERE e.did = d.id
AND e.id > 2
DIRECTIVES FOLLOWED:
ORDERED
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 3
Estimated # of Rows Returned: 2
1) informix.d: SEQUENTIAL SCAN
2) informix.e: INDEX PATH
Filters: informix.e.id > 2
(1) Index Keys: did (Serial, fragments: ALL)
Lower Index Filter: informix.e.did = informix.d.id
NESTED LOOP JOIN
|
上記の例は、結合順序の重要性を明確に示しています。先に述べたように、2つ以上の表を結合する場合には常に結合 のドライビング・テーブルとして最少の行を返す表を選択する必要があります。そのようにしないと、結合の効率が大幅に落ちて、最終的に全体のパフォーマンスを低下させてしまうおそれもあります。
結合プラン・ディレクティブ
結合プラン・ディレクティブは、結合方式に関する最適化プログラムの決定に影響を与えます。結合方式としては主に、ネストループ結合と動的ハッシュ結合の2つがあります。ネストループ結合では、最初の表、つまり外部テーブルが照会基準を満たす行についてスキャンされます。外部テーブルで見つけられたすべての行に対して、データベース・サーバーは2番目の表つまり内部テーブルの対応する行を検索します。結合列に索引があれば、コストは大幅に低下しますが、索引がない場合にはデータベースは外部テーブルと内部テーブルのすべての表に完全表スキャンを行う必要があるので、表が膨大でしかも照会基準が非常に選択的であればコストは大幅に上昇します。
ハッシュ結合または動的ハッシュ結合は、IBM Informix Dynamic Server? V7以降の新しい結合方式です。これは、結合される表の一方または両方に索引がない場合、あるいはデータベース・サーバーが両方の表から多数の行を読み取る必要がある場合に使用されます。この結合では、表の一方（通常は小さいほうの表）がスキャンされ、メモリー内にハッシュ・テーブルを作成するために使われます。ハッシュ関数を使用して、各行は同じハッシュ値を持つ他の行とともに「バケット」に入れられます。最初の表がスキャンされてハッシュ・テーブルに置かれた後、2番目の表が1回スキャンされ、各行がハッシュ・テーブルで検索されて結合が行えるかどうか確かめます。ハッシュ結合は通常、索引キー・スキャンではなくハッシュ関数を使ってデータをプローブするので、ネストループ結合に比べて高速です。
このカテゴリーには、下記の表に示すように4つのディレクティブがあります。
| ディレクティブ |
説明 |
| USE_NL |
照会プランにネストループ結合を使用する |
| USE_HASH |
照会プランに動的ハッシュ結合を使用する |
| AVOID_NL |
照会プランにネストループ結合を使用しない |
| AVOID_HASH |
照会プランに動的ハッシュ結合を使用しない |
上記の4つのディレクティブのうち、最も便利なものはおそらくUSE_HASHです。この最適化プログラムは通常、結合される表が結合列に索引を持つ場合にネストループ結合を選択する十分な機能を備えています。しかし、先に述べたように、動的ハッシュ結合は通常、ネストループ結合よりも高速になります。このディレクティブを使って、照会実行プランに動的ハッシュ結合を使用するよう最適化プログラムに強制することもできます。この場合もやはり状況に大きく左右されるので、データベースの表と索引、および照会の目的について深い理解が必要です。
最適化目標ディレクティブ
最適化目標ディレクティブは、照会結果、あるいは照会が返す行の数に直接影響を与えます。たとえば、場合によっては照会から返されるすべての行が必要ではないこともあり、必要なのは最初の数行だけという場合もあります。このカテゴリーには、以下の表に示すように2つのディレクティブがあります。
| ディレクティブ |
説明 |
| FIRST_ROWS |
照会から最初の数行が返されるよう指定する |
| ALL_ROWS |
デフォルト、照会からすべての行を返す |
FIRST_ROWSは、非常に便利なディレクティブです。照会結果を必要に応じてトリムするだけでなく、さらに重要なことに、適切な照会実行プランを選択する最適化プログラムの決定に影響を与え、エンジンの時間を要するアクティビティーを省いてくれます。たとえば、照会がすべての行を返すようにする場合には、最適化プログラムは照会に1つの実行プランを選択しますが、照会が最初の2行を返すようにする場合には、最適化プログラムはその照会に対して別のものを選択します。仮に、employee表とdepartment表という2つの表があるとします。employee表のdept_no列に索引がありますが、department表には索引がありません。これらの2つの表を結合して、受注部門に勤務する従業員全員のリストを生成する場合は、以下のように照会を記述することになります。
SELECT * FROM employee e, department d
WHERE e.did = d.id
AND d.name = 'Orders'
|
しかし、受注部門に勤務する従業員のうち2名の名前を知りたいだけという場合には、FIRST_ROWSディレクティブ を使用して以下のように照会を記述しなおすことができます。
SELECT {+FIRST_ROWS} FIRST 2
* FROM employee e, department d
WHERE e.did = d.id
AND d.name = 'Orders'
|
では、これらの2つの照会について照会実行プランを検討してみましょう。FIRST_ROWSディレクティブによって照 会実行プランがどのような影響を受けるかについて具体的なイメージがつかめます。2つの照会の結合方式は全く異なっていることに注目してください。
FIRST_ROWSディレクティブのない、最初の照会の照会実行プランは次のとおりです。
QUERY:
------
SELECT * FROM employee e, department d
WHERE e.did = d.id
AND d.name = 'Orders'
Estimated Cost: 2
Estimated # of Rows Returned: 3
1) informix.e: SEQUENTIAL SCAN
2) informix.d: SEQUENTIAL SCAN
Filters:
Table Scan Filters: informix.d.name = 'Orders'
DYNAMIC HASH JOIN
Dynamic Hash Filters: informix.e.did = informix.d.id
|
FIRST_ROWSディレクティブのある、2番目の照会の照会実行プランは次のとおりです。
QUERY: (FIRST_ROWS OPTIMIZATION)
------
SELECT {+FIRST_ROWS} FIRST 2 * FROM employee e, department d
WHERE e.did = d.id
AND d.name = 'Orders'
DIRECTIVES FOLLOWED:
FIRST_ROWS
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 3
Estimated # of Rows Returned: 3
1) informix.d: SEQUENTIAL SCAN
Filters: informix.d.name = 'Orders'
2) informix.e: INDEX PATH
1) Index Keys: did (Serial, fragments: ALL)
Lower Index Filter: informix.e.did = informix.d.id
NESTED LOOP JOIN
|
上記の出力からわかるように、最初の照会では、2つの表全体の結合が必要であるためハッシュ結合を使用しています。「department」が非常に小さい表なので、ネストループ結合よりも表にハッシュを構築するほうが効率が高まります。しかし、2番目の照会では異なる結合方式、ネストループ結合を使用します。2番目の照会では、結合から返された最初の2行が必要になるだけです。この場合には、department表にハッシュを構築することがオーバーヘッドになり、最適化プログラムは表全体をメモリーに読み込むことがコスト効率的ではないと判断して、プローブを行います。そのため、ネストループ結合が選択されます。
例
IBM Informixは、最適化プログラムが照会に最適な実行プランを選択するよう手がかりとなる、あるいは指示するために使用できる各種の照会ディレクティブを提供しています。これらの照会ディレクティブは、照会にどのように使用したらよいのでしょうか。従うべき一連の鉄則というものはありませんが、これだけははっきり言えることがあります。データベースの表と索引、および照会の目的をしっかりと理解しておく必要があるということです。照会を使用する前に、構成ファイルのDIRECTIVESパラメーターがオンになっていること、つまり1に設定されていることを確認する必要があります。そうしないと、最適化プログラムは照会で指示またはヒントに従うことができません。
以下に、照会実行プランを選択する最適化プログラムの決定に直接影響を及ぼすためにどのように照会ディレクティブを使うことができるか例を示します。
例1：
元の照会とその実行プランは次のとおりです。
QUERY:
------
SELECT UNIQUE mne.mso_id, mne.ne_inst, ane.mso_id, 1
FROM ne mne, ne ane, cell, neigh, cell ncell
WHERE cell.workspace_id = 291
AND cell.acg_instance = mne.ne_inst
AND cell.workspace_id = mne.workspace_id
AND mne.ne_type = 0
AND mne.mso_id !=ane.mso_id
AND mne.workspace_id=ane.workspace_id
AND ane.ne_inst=ncell.acg_instance
AND cell.cell_instance = neigh.adv_cell_inst
AND ncell.cell_instance = neigh.neigh_cell_inst
AND cell.workspace_id = ncell.workspace_id
AND ncell.workspace_id = neigh.workspace_id
Estimated Cost: 5971
Estimated # of Rows Returned: 1
1) root.cell: INDEX PATH
(1) Index Keys: workspace_id
cellglob_instance (Serial, fragments: ALL)
Lower Index Filter: root.cell.workspace_id = 291
2) informix.mne: INDEX PATH
Filters: informix.mne.ne_type = 0
(1) Index Keys: workspace_id ne_inst
(Serial, fragments: ALL)
Lower Index Filter: (root.cell.acg_instance =
informix.mne.ne_inst AND
root.cell.workspace_id = informix.mne.workspace_id )
NESTED LOOP JOIN
3) root.neigh: INDEX PATH
(1) Index Keys: workspace_id
adv_cell_inst neigh_cell_inst (Key-Only)
(Serial, fragments: ALL)
Lower Index Filter: (root.cell.cell_instance
= root.neigh.adv_cell_inst AND
informix.mne.workspace_id = root.neigh.workspace_id )
NESTED LOOP JOIN
4) informix.ncell: INDEX PATH
(1) Index Keys: workspace_id cell_instance
(Serial, fragments: ALL)
Lower Index Filter:
(informix.ncell.cell_instance =
root.neigh.neigh_cell_inst AND
informix.mne.workspace_id =
informix.ncell.workspace_id )
NESTED LOOP JOIN
5) informix.ane: INDEX PATH
Filters: informix.mne.mso_id
!= informix.ane.mso_id
(1) Index Keys: workspace_id ne_inst
(Serial, fragments: ALL)
Lower Index Filter:
(informix.ane.ne_inst = informix.ncell.acg_instance AND
root.cell.workspace_id = informix.ane.workspace_id )
NESTED LOOP JOIN
|
この照会プラン出力を分析した後、ディレクティブを使って結合表でさらに多くの固有の索引を使用するように指示できること、またそれが全般的な照会のパフォーマンスを高めることが分かりました。それで元の照会に変更を加えましたが、期待通りに機能しました。以下の出力からご覧いただけるように、照会のコストは50パーセント以上減少しました。変更された照会の実行プランは以下のとおりです。特定の索引を使用するように最適化プログラムに指示するためUSE_INDEXがどのように使われているか注目してください。
QUERY:
------
SELECT {+INDEX (mne ne_idx1),
INDEX (cell cell_foridx1),
INDEX (neigh neigh_foridx2)}
UNIQUE mne.mso_id, mne.ne_inst, ane.mso_id, 1
FROM ne mne, ne ane, cell, neigh, cell ncell
WHERE cell.workspace_id = 291
AND cell.acg_instance = mne.ne_inst
AND cell.workspace_id = mne.workspace_id
AND mne.ne_type = 0
AND mne.mso_id !=ane.mso_id
AND mne.workspace_id=ane.workspace_id
AND ane.ne_inst=ncell.acg_instance
AND cell.cell_instance = neigh.adv_cell_inst
AND ncell.cell_instance = neigh.neigh_cell_inst
AND cell.workspace_id = ncell.workspace_id
AND ncell.workspace_id = neigh.workspace_id
DIRECTIVES FOLLOWED:
INDEX ( mne ne_idx1 )
INDEX ( cell cell_foridx1 )
NDEX ( neigh neigh_foridx2 )
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 2559
Estimated # of Rows Returned: 1
1) informix.ncell: INDEX PATH
(1) Index Keys: workspace_id cellglob_instance (Serial, fragments: ALL)
Lower Index Filter: informix.ncell.workspace_id = 291
2) informix.ane: INDEX PATH
(1) Index Keys: workspace_id ne_inst (Serial, fragments: ALL)
Lower Index Filter: (informix.ane.ne_inst = informix.ncell.acg_instance
AND informix.ane.workspace_id = informix.ncell.workspace_id )
NESTED LOOP JOIN
Dynamic Hash Filters: (root.cell.cell_instance = root.neigh.adv_cell_inst
AND root.cell.workspace_id = informix.ncell.workspace_id )
3) root.neigh: INDEX PATH
(1) Index Keys: workspace_id adv_cell_inst neigh_cell_inst
(Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (root.cell.cell_instance
= root.neigh.adv_cell_inst AND
informix.mne.workspace_id = root.neigh.workspace_id )
NESTED LOOP JOIN
4) informix.ncell: INDEX PATH
(1) Index Keys: workspace_id cell_instance
(Serial, fragments: ALL)
Lower Index Filter: (informix.ncell.cell_instance
= root.neigh.neigh_cell_inst AND
informix.mne.workspace_id = informix.ncell.workspace_id )
NESTED LOOP JOIN
5) informix.mne: INDEX PATH
Filters: (informix.mne.ne_type = 0 AND
informix.mne.mso_id != informix.ane.mso_id)
(1) Index Keys: workspace_id ne_inst
(Serial, fragments: ALL)
Lower Index Filter: (root.cell.acg_instance =
informix.mne.ne_inst AND root.cell.workspace_id =
informix.mne.workspace_id )
NESTED LOOP JOIN
|
例2：
顧客の注文のリストを生成します。元の照会とその実行プランは次のとおりです。
QUERY:
------
SELECT lname, fname, order_date, price
FROM customer c, orders o
WHERE c.customer_num = o.customer_num
AND c.customer_num = 117
Estimated Cost: 100
Estimated # of Rows Returned: 69
1) informix.c: SEQUENTIAL SCAN
Filters: informix.c.customer_num = 117
2) informix.o: INDEX PATH
(1) Index Keys: customer_num (Serial, fragments: ALL)
Lower Index Filter: informix.c.customer_num = informix.o.customer_num
NESTED LOOP JOIN
|
顧客数はさほど多くないので、顧客表もかなり小さく、動的ハッシュ結合が役立つと考えられます。変更を加えた照会とその実行プランは次のとおりです。ネストループ結合から動的ハッシュ結合への変更に注目してください。また、照会のコストが大幅に低下したことにも着目してください。さらに、USE_HASHディレクティブがどのように使用されているのか分かります。
QUERY:
------
SELECT {+USE_HASH(c /BUILD)} lname, fname, order_date, price
FROM customer c, orders o
WHERE c.customer_num = o.customer_num
AND c.customer_num = 117
DIRECTIVES FOLLOWED:
USE_HASH ( c )
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 9
Estimated # of Rows Returned: 69
1) informix.c: SEQUENTIAL SCAN
Filters:
Table Scan Filters: informix.c.customer_num = 117
2) informix.o: SEQUENTIAL SCAN
DYNAMIC HASH JOIN
Dynamic Hash Filters: informix.c.customer_num = informix.o.customer_num
|
例3：
最後の例は、今年度に2件以上注文している顧客のリストを作成するものです。元の照会とその実行プラン出力は次のとおりです。
QUERY:
------
SELECT fname, lname, COUNT(order_num), SUM(price*discount)
FROM customer c, orders o
WHERE c.customer_num = o.customer_num
GROUP BY 1, 2
HAVING COUNT(order_num) > 2
Estimated Cost: 60
Estimated # of Rows Returned: 1
Temporary Files Required For: Group By
1) informix.o: SEQUENTIAL SCAN
2) informix.c: INDEX PATH
(1) Index Keys: customer_num
(Serial, fragments: ALL)
Lower Index Filter: informix.c.customer_num
= informix.o.customer_num
NESTED LOOP JOIN
|
例2で行ったように、この照会にハッシュ結合を適用しました。変更を加えた照会とその実行プラン出力は次のとおりです。
QUERY:
------
SELECT {+USE_HASH (c /build)}
fname, lname, COUNT(order_num), SUM(price*discount)
FROM customer c, orders o
WHERE c.customer_num = o.customer_num
GROUP BY 1, 2
HAVING COUNT(order_num) > 2
DIRECTIVES FOLLOWED:
USE_HASH ( c/BUILD )
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 50
Estimated # of Rows Returned: 1
Temporary Files Required For: Group By
1) informix.c: SEQUENTIAL SCAN
2) informix.o: SEQUENTIAL SCAN
DYNAMIC HASH JOIN (Build Outer)
Dynamic Hash Filters: informix.c.customer_num = informix.o.customer_num
|
結論
以上の例から、どのように照会ディレクティブを使って最適化プログラムの照会実行プラン決定に影響を与えることができるか、いくらかお分かりいただけると思います。さらに、繰り返しになりますが、照会ディレクティブの適切な使用は、データベースの表と索引、さらに照会の目的を理解していることに大きく依存しています。従うべき鉄則というものはないのです。
執筆者について
 |
Jianing FanはMotorolaのソフトウェア・エンジニアで、リレーショナル・データベース管理システムを専門としています。彼は、Informix認定プロフェッショナル、Oracle認定プロフェッショナルであり、開発者、システム管理者、DBAとして10年以上にわたりデータベースとシステムに経験を積んでいます。
メール・アドレス：fan@eis.comm.mot.com |
IBM、DB2、および WebSphere は、米国およびその他の国におけるIBM Corporation の商標です。
Windows および Windows NT は、米国およびその他の国における Microsoft Corporation の商標です。
Java および Java ベースの商標およびロゴは、米国およびその他の国における Sun Microsystems, Inc. の商標です。
その他の会社名、製品名、およびサービス名は、それぞれの商標またはサービス・マークです。 。
 原文はこちら
|
