1 はじめに........................................................................................................................
2 ＳＱＬの実行前の処理.........................................................................................................
3 再利用可能なＳＱＬの書き方（ＳＱＬコーディング基準）....................................................................
4 オプティマイザについて.......................................................................................................
4.1 アクセスパス............................................................................................................................................................................................
4.2 オプティマイザの目標............................................................................................................................................................................
5 ヒントとExplain Plan..................................................................................................
5.1 ヒント..........................................................................................................................................................................................................
5.2 Explain Plan...........................................................................................................................................................................................
6 インデックスを使わない方がよい場合もある................................................................................
7 パフォーマンスを意識したＳＱＬコーディング作法..........................................................................
7.1 単純なSQLの書き方 －インデックスを妨げる方法........................................................................................................................
7.1.1 条件の左側には関数を使わない.....................................................................................................................................................
7.1.2 文字の連結'||'は関数です..............................................................................................................................................................
7.1.3 算術演算も関数です........................................................................................................................................................................
7.1.4 その他の関数....................................................................................................................................................................................
7.1.5 データ型の混合..............................................................................................................................................................................
7.1.5.1 型変換の補足.........................................................................................................................................................................
7.1.6 列と列の＜、＞、＜＝、＞＝..........................................................................................................................................................
7.1.7 文字列の部分検索.........................................................................................................................................................................
7.1.8 NOT NULL定義されていない列でのORDER BY................................................................................................................
7.1.8.1 ORDER BY補足....................................................................................................................................................................
7.1.9 インデック付きの列をORで並べない..........................................................................................................................................
7.1.10 NULL検索、NOT NULL検索..............................................................................................................................................
7.1.11 NOT EQUAL ( != 、＜＞) 検索、 NOT IN検索.............................................................................................................
7.1.12 GROUP BY..................................................................................................................................................................................
7.1.13 CONNECT BY............................................................................................................................................................................
7.1.14 NOT NULL定義されてない列のＤＩＳＴＩＮＣＴ..........................................................................................................................
7.1.15 MAXまたはMIN........................................................................................................................................................................
7.2 既存のインデックスを上手に使う書き方...........................................................................................................................................
7.2.1 インデックスの調べ方....................................................................................................................................................................
7.2.2 複合インデックスを使う..................................................................................................................................................................
7.2.3 結合インデックスを使う..................................................................................................................................................................
7.2.4 ROWIDでアクセスするのが一番はやい......................................................................................................................................
7.3 より複雑なSQL－結合（JOIN）、副問合せ、集合演算（INTERSECT, MINUS, UNION）..........................................................
7.3.1 結合の順序について......................................................................................................................................................................
7.3.2 副問合せ（複合文）.........................................................................................................................................................................
7.3.3 集合演算ＩＮＴＥＲＳＥＣＴ、ＭＩＮＵＳ、ＵＮＩＯＮ.................................................................................................................................
7.4 書き方の順序関係...............................................................................................................................................................................
7.4.1 等号（＝）のＡＮＤ条件....................................................................................................................................................................
7.4.2 等号（＝）と不等号（＜、＜＝、＞、＞＝）のＡＮＤ条件.................................................................................................................
7.4.3 ２つのテーブルの指定順序...........................................................................................................................................................
7.4.4 ３つのテーブルの指定順序...........................................................................................................................................................
7.4.5 インデックスがないときのWHERE句の指定順序.....................................................................................................................
7.4.5.1 ANDの条件は重そうな条件を上にする（インデックスがないとき）.....................................................................................
7.4.5.2 ORの条件は重そうな条件を下にする（インデックスがないとき）.........................................................................................
8 おわりに.......................................................................................................................
9 参考文献......................................................................................................................
10 付録A Explain Planの例......................................................................................
11 付録B チェックリスト......................................................................................................
1 はじめに
アプリケーションのパフォーマンスを左右する要因は多様です（本屋でも見かけた人もいると思いますが、パフォーマンスというネタだけで枕になるような本もでています）。 DBAがインスタンスを最適に作成していない、というのも大きな要因でしょう。 また、Ｉ／Ｏ性能の低いディスクを使っている、というハードウェアも要因になるでしょう。
ここで、記述するのは、ＳＱＬの書き方に限定しています。 いくら最強のＨ／Ｗを使っていても、また最適なインスタンスを作成していても、アプリケーション、つまりはＳＱＬがその環境を有効に使うように記述されていないと、最終的にはよいパフォーマンスを得ることができません。 じゃあ、どんな書き方をすればよいか、どんな書き方をしてはいけないか、大体、そんなことを書いています。
l バックグラウンドになる仕組みも、若干書いてありますので、仕組みから知りたい人は、最初から読んでください。
l 書き方さえ分かれば良いというひとは、付録Bを索引にして本文を見てもらえばよいでしょう。
l 本文中の例題の実行計画（Explain Planの結果）を付録Aに入れてありますので、間違った書き方と正しい書き方の違いを比べてみて下さい。
l もっと、詳しく勉強したいという人は、参考文献を挙げてありますので、参考にしてください。
前提： ・ＵＮＩＸ版Ｏｒａｃｌｅ７．３．２に基づいています。
・見やすくするために、紹介した作法に合わない書き方でサンプルを書いています。
標記： 特に断わりのない限り、「Ｏｒａｃｌｅ」は、Ｏｒａｃｌｅ７．３．２．３を指しています。
2 ＳＱＬの実行前の処理
| 表1 ＳＱＬ実行までの流れ
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ＳＱＬのパフォーマンスを考えるとき、ＳＱＬが実行されるまでの流れを多少知っていることが必要です。Ｏｒａｃｌｅでは表1のステップでSQLが処理されます。
実行前の準備段階に当たりますが、ステップ３のＳＱＬ文の解析の処理がオーバヘッドになります。 メモリ上に再利用可能なＳＱＬが存在する場合、ステップ３～６は不要となります。オーバヘッドを減らすためにも、再利用可能なSQLが求められます。 また、ステップ３は自分自身のパフォーマンスを落とすだけでなく、 メモリ上に再利用可能な状態で存在する他のSQLを追い出す場合もあり、他のアプリケーションにも影響を及ぼします。
|
参考： CIMAでは常時、３，０００～４，０００件のSQLが共有SQL領域に存在しており、 通常、９５％以上の再利用率を維持しています。 |
3 再利用可能なＳＱＬの書き方（ＳＱＬコーディング基準）
Ｑ：「再利用できるＳＱＬとはどのようなものでしょうか？」
現在のメモリ（「共有ＳＱＬ領域」）をＯｒａｃｌｅのビューを使ってのぞいてみると一目瞭然ですが、同じ「構文」のＳＱＬがいくつもあるのが分かります。
① select count(*) from TBL_EMPLOYEE;
② Select count(*) from TBL_EMPLOYEE;
③ select count(*) from TBL_EMPLOYEE;
この３つは微妙に違います（印刷が悪いわけではありません！）。①と他との違いは、②は最初の文字”S”が大文字であり、③は”select”と”count(*)”の間にスペースが１バイト多いことです。 これだけで、別のSQLとして判断されます。
要するに、文字コードのレベルで、すべて一致していなければ、Oracleは異なるSQLと判断し、新たに解析を行います。
すべてのSQLに対して、再利用可能にするためには、結局は「コーディング基準」が必要ということです。例えば、図1のようなものです。
|
★コーディング基準の例 ①ＳＱＬ文は大文字または小文字で統一する ②キーワード(from, where, and, orなど)は改行して、行の先頭に記述する ③ワード間の区切りはスペース１バイトとし、タブは使用しない ④ホスト変数を使用する ⑤表には別名を付けて、別名で列名を修飾する 図1 コーディング基準の例 |
バッチ処理の場合に注意すべき点は④のホスト変数の使用です。 ホスト変数は変数が展開されずに、メモリ上にロードされるので、変数の値がいくら変わろうとも、SQLとしては同じ文であり、再利用できます。 これを変数を使わずに、コーディングすると、値の数だけSQLがメモリにロードされるため、バッチ処理のように大量の件数を処理する場合、共有ＳＱＬ領域を専有してしまいかねません。
例： あるバッチプログラムで、次のselect文が、条件の値が変わって、１万回実行される。
select extrl_cmpny_nm_kana, extrl_cmpny_nm_kanji from suser.tbl_external_company
where extrl_cmpny_cd =‘01949’;
CIMAでは通常、共有SQL領域には４千件ほどのSQLが存在できますが、このSQLが値を変えながら１万回実行されると、すべてのSQLは異なる文と判断され、すべて解析・ロードされます。 この例の場合の条件は取引先テーブルのプライマリキーなので、SQLの実行自体はほとんどリソースを使用しませんが、解析が発生するために、スループットが低下します。 また、メモリには１万件も入れられないので、それまでメモリに存在したSQLは追い出され、メモリはこのSQLで専有されます。 結局、このバッチが終了したあとには、オンラインで再利用したいSQLは何も残っていない、ということになります。 この条件の値をホスト変数にすれば、１万件のSQLはたった１件分のメモリ使用で済みます。
4 オプティマイザについて
4.1 アクセスパス
SQLの実行時のパフォーマンスの良し悪しを決定するのは、「オプティマイザ」の判断に委ねられていると云えます。 「オプティマイザ」とは、簡単に云えば、SQLを解析して前述の「実行計画」を作成するOracleの機能であり、「実行計画」にしたがって、内部的な処理方法・手順が決まり、SQL文はこれにしたがってコンパイルされます。
表2が、オプティマイザが実行計画を作成する際に、基準となっている（であろう）アクセスパスです（CIMAで使われないパスは外してあります）。 この優先順位にしたがって、適用するアクセスパスが決められます。
ＲＯＷＩＤ[1]が指定されていれば、最優先に適用され、適切なパスが見つからなければ、全表走査される、という見方になります。
| 表2 アクセスパス
|
どのパスが実行計画に採用されるかを決定する主な要因は、次の事項です。
l 条件文の記述（ｗｈｅｒｅ句）
l インデックスの有無
l インデックスの一意性
l データの件数
l データの分布
etc.
表からも想像できるように、優先順位の値が小さいパスほど、よりパフォーマンスが優れています。 SQLをチューニングする、ということは、如何に上位のアクセスパスを適用させるか、ということであり、これは如何にインデックスが利くようにSQLを作るか、と考えてもよいでしょう。
4.2 オプティマイザの目標
オプティマイザが実行計画を作成する際のルールは上記の通りですが、 実はこのときオプティマイザは、一つの目標に向かって実行計画を作成します。この目標とは、何を最短にするためのアクセスパスを見つけるのか、という「オプティマイザ・ゴール」と呼ばれるものです。
「オプティマイザ・ゴール」には、次の２つがあります。
① レスポンス ： 最初の１件目を最短にする。 （＝ オンラインの目標）
|
ＣＩＭＡの場合、①のレスポンスを「オプティマイザ・ゴール」として設定しています。すなわち、「１件目を如何に最短で処理できるか」という目標で、実行計画が作成されています。 |
① スループット： 全件の終了を最短にする。 （＝ バッチの目標）
したがって、バッチ処理の場合、レスポンスを重視した実行計画では、不都合な場合もあります。 しかし、そのＳＱＬがオンライン処理のものなのか、バッチ処理のものなのかは、開発者（設計者）にしかわかりません。 そこで、ＳＱＬ文の中で、オプティマイザに対して、明示的にアクセスパスやゴールを指示するができます。 この方法は「ヒント」と呼ばれます。
5 ヒントとExplain Plan
5.1 ヒント
ヒントは上述のように、オプティマイザに指示を与えられるものです。 ヒントを使うことによって、パフォーマンスが大きく向上することもあります。但し、注意しなければならない点は、リリースによって変化する可能性があることです。
ヒントはSQL文中に直接記述しますが、SQLではなく、Oracleのツールです。 基本的には、次のことを指示できます。
ゴール
ソート処理の方法
結合の順序
インデックスの指定
書き方については、Oracleの「SQL言語レファレンス」を見てください。
5.2 Explain Plan
「Explain Plan」とは、ＳＱＬの実行計画を調べるためのＯｒａｃｌｅのツールです。 これを使えば、アプリケーションを実行する前に、実際に実行したときに用いられる実行計画を予めチェックし、次のようなことを確認、検討できます。
l 最適な（良好な）実行計画が作成されるか
l 既存のインデックスが適用されるか
l 新規にインデックスを作成できるか、あるいは必要があるか
実行に何時間も、何日もかかるようなＳＱＬをこの段階でチェックしてチューニングしておくことがベストな手順です。場合によっては、アプリケーション・ロジックに影響するようなチューニングが必要な場合がありますので、システムテストまで進んでから、パフォーマンスが悪いことがわかっても、テストをやり直さなければならないので、容易にロジック修正はできないでしょう。 したがって、事前に実行計画を評価することを強く推奨します。
サンプルが付録Ａに山ほどありますので、参考にして下さい。
6 インデックスを使わない方がよい場合もある
一般的に、インデックスを付けない方がよい場合として次のケースがあります。
① 同じテーブルに複合インデックスの先頭部分の列として、既に作成されている。（詳細は7.2.2章参照）
② 選択性が低いため、全表走査した方が効率的。
③ 列が非常に長い場合。（LONG、LONG LAW型へのインデックスは不可）
④ 他のプログラムのパフォーマンスが低下する場合。
①は後述します。②～④は明確な指標はありません。 ここではインデックスの基本的な考え方となる②について説明します。
後述するのSQLは別にして、正しい書法でもインデックスが使われない場合があります。
オプティマイザが「インデックスを使うよりも、全件検索した方が実行コストが低い」、と判断した場合に、このことが起こります。 ある検索を行うときに、テーブルの大部分のレコードを調べる必要がある場合、インデックスを使うよりも、最初から全件走査した方が一般的に高速に処理されると云われています。 基本的には、
「このSQLを実行したときに、全体の何％にアクセスすることになるのか」（「選択性」と呼びます）
ということを内部的に数値化して、この値からインデックスを使うか、使わないかを決定して、実行計画に反映しています。
|
全体の１５％～２０％以下の件数のときにインデックスを使用 |
数値の計算方法は、参考文献で確認してもらえばよいのですが、通常は
すると考えて下さい。
| 地区コード | 件数 | 割合(%) |
| 10 | 19633 | 71 |
| 20 | 6074 | 22 |
| 30 | 2070 | 7 |
| 計 | 27777 | 100 |
例 ： 購買の地区コードは‘１０’、‘２０’、‘３０’の3種類です。 購買受付テーブルにおける現在（1998/12/10時点）のデータの割合は、次のようになっています。
このとき、地区コード‘１０‘のデータを検索する場合、最低でもの７１％のレコードにアクセスしないと、目的の結果を得ることができません。 しかし、この７１％のレコードは平均してテーブルに分布していると考られるので、結局全件検索した方が、インデックスを介すよりも高速になるということです。逆に、’３０‘を条件にするときは、全体の７％なので、インデックスを使うほうが高速になります。 ’２０‘のときは、どちらも同じようなスループットと考えらます。
7 パフォーマンスを意識したＳＱＬコーディング作法
7.1 単純なSQLの書き方 －インデックスを妨げる方法
インデックスの使用を妨げる書き方を紹介します。 より良い書き方を提示できるものは、【正解】として示してあります。 ここで、「単純な」SQLとは、一つの表を対象とするSQLのことです。 複数の表を使う場合は、「複雑な」SQLとして、後述します。
7.1.1条件の左側には関数を使わない
基本です。
例： ”社員番号の頭３桁が857で始まる社員は？”
SELECT ..... FROM TBL_EMPLOYEE
WHERE SUBSTR(EMPLOYEE_NO, 1, 3) = '857' ;
【正解】
SELECT ..... FROM TBL_EMPLOYEE
WHERE EMPLOYEE_NO LIKE '857%' ;
7.1.2文字の連結'||'は関数です
関数らしくないですが関数です。条件の左側に使うとインデックスが使われません。
SELECT PROJECT_NAME FROM TBL_PROJECT
WHERE PROJECT_NO||ACCEPT_BRUNCH_NO = '2900GE7501' ;
【正解】
SELECT PROJECT_NAME FROM TBL_PROJECT
WHERE PROJECT_NO = '2900GE75' AND ACCEPT_BRUNCH_NO = '01' ;
わざと、インデックスの使用を拒否したい場合に、次のような書き方をすることがあります。
SELECT PROJECT_NAME FROM TBL_PROJECT
WHERE PROJECT_NO||’’ = '2900GE75' AND ACCEPT_BRUNCH_NO = '01' ;
例えば、6章の例の場合、購買地区コード‘１０’を選択するときは、 全表走査した方が速いので、次のような条件の書き方をします。
WHERE PURCHASE_AREA_CODE||’’ = ‘10’
7.1.3算術演算も関数です
ありがちです。
例： ”税込みで１０万円超えるものは何件？”
SELECT COUNT(*) FROM TBL_SALES WHERE SALES_AMNT_YEN * 1.05 > 100000 ; 【正解】
SELECT COUNT(*) FROM TBL_SALES WHERE SALES_AMNT_YEN > 95238 ;
7.1.4その他の関数
上記の関数以外にも、すべての関数は、条件の左側に書くとインデックスが使えません。
特に、文字列操作関数（SUBSTR, TO_CHARなど）は注意して下さい。
7.1.5データ型の混合
ありがちです。 Oracleは自動的に型変換をしてくれます。 しかし、この場合、インデックスを使用できなくなる可能性があります。 下の例では、123456という値を使っていますが、これがバインド変数であっても同じです。
例： “社員番号が123456の社員名は？ （社員番号はCHAR型です）”
SELECT EMPLOYEE_NAME FROM GUSER.TBL_EMPLOYEE
WHERE EMPLOYEE_NO = 123456 ;
【正解】
SELECT EMPLOYEE_NAME FROM GUSER.TBL_EMPLOYEE
WHERE EMPLOYEE_NO = ‘123456’ ;
7.1.5.1 型変換の補足
正確にいうなら、文字型と数値型を比較するとき、文字型の方が数値型に変換されます。
つまり where num_type = char_typeという句は、 where num_type = to_number(char_type) となります。一方、 where char_type = num_typeは、where to_number(char_type) = num_type となり、 左辺に関数を使用する形になるため、インデックスが使えないということになります。 ということは、上の【正解】の場合に、EMPLOYEE_NOが数値型だと、実はインデックスが使えるということです。
7.1.6列と列の＜、＞、＜＝、＞＝
列と列との比較は、インデックスは使えません。 比較とは＜、＞、＜＝、＞＝ です。
例： 仕入テーブルから、返品入力日が仕入確定入力日より大きいデータの件数を調べる。
select count(*) from TBL_BUYING
where RTRND_GDS_INPUT_DATE > BUYING_DCSN_INPUT_DATE ;
この場合、返品入力日にはインデックスがあります。例え、仕入確定入力日にインデックスがあっても、
全表走査になります。次の例ではインデックスが使われます。
select count(*) from TBL_BUYING where RTRND_GDS_INPUT_DATE > 19981130 ;
7.1.7文字列の部分検索
当然、このような要件はありますので、やむ得ない気もしますが、全表走査で CPU負荷となります。負荷の程度は、比較する文字列の長さに依存するそうです。 インデックスが使える条件をANDで加えると良いのですが。
例： “マイクロという名称をもつ商品は何件あるか？”
SELECT COUNT(*) FROM TBL_GOODS
WHERE GOODS_NAME_KANJI LIKE ‘%マイクロ%’ ;
7.1.8NOT NULL定義されていない列でのORDER BY
NOT NULL定義された列であればインデックスを使用できますが、NULLが存在する列だと全表走査になり、かつソート処理のため、 Ｉ／Ｏ負荷になります。
SELECT GOODS_NAME_KANJI FROM TBL_GOODS
ORDER BY GOODS_NAME_KANJI ;
ただし、WHERE句を付けて件数を絞れれば、限定された範囲での表走査＋ソートになります。
7.1.8.1ORDER BY補足
少し補足すると、次のすべての条件が満たされたとき、インデックスが使用できます。
l ORDER BYで指定する列が、１つの列のインデックスか、または複合インデックスの先頭の列
l ORDER BYで指定する列が、少なくとも１つは、プライマリキーかNOT NULL制約がある
l NLS_SORTパラメータが’BINARY’に設定されている（デフォルト）
例： 社員マスタから社員番号と社員氏名をとりだす。
①社員番号順（EMPLOYEE_NOはプライマリキー）
SELECT EMPLOYEE_NO, EMPLOYEE_NAME FROM GUSER.TBL_EMPLOYEE ORDER BY EMPLOYEE_NO ;
②プロジェクトNO順（PROJECT_NOはNOT NULL制約がないインデックス）
SELECT EMPLOYEE_NO, EMPLOYEE_NAME FROM GUSER.TBL_EMPLOYEE ORDER BY PROJECT_NO ;
①はインデックスが使われますが、②はインデックスが使われず全表走査になります。
7.1.9インデック付きの列をORで並べない
インデックス付きの列をORに使用すると、全表走査になります。
例： ”部門コードがA100かA200かA300の部門は？”
SELECT ... FROM TBL_DEPARTMENT
WHERE DEPARTMENT_CD = 'A100'
OR DEPARTMENT_CD = 'A200' OR DEPARTMENT_CD = 'A300' ;
【正解】
SELECT ... FROM TBL_DEPARTMENT
WHERE DEPARTMENT_CD IN ('A100', 'A200', 'A300') ;
これは、インデックスの存在が逆にオーバヘッドになってしまうパターンです。 あるアプリケーションでインデックスを貼ったら、別のアプリケーションが遅くなるという可能性を示しています。
7.1.10NULL検索、NOT NULL検索
NULL値はインデックスには存在しないため、NULLを条件に使用すると、全表走査になります。
a) SELECT COUNT(*) FROM TBL_JOURNAL WHERE PARTNER_CD IS NULL ;
b) SELECT COUNT(*) FROM TBL_JOURNAL WHERE PARTNER_CD IS NOT NULL ;
正解はありません。 比較する列によっては、AND条件に分解することも可能です。 対象とするテーブルの件数や、当該SQLの実行回数により処理時間は異なりますので、 いくつかのパターンで実行計画を確認して、最適な方法を見つけるのが良いでしょう。
7.1.11NOT EQUAL ( != 、＜＞) 検索、 NOT IN検索
どちらもインデックスには存在しないものを検索しろ、ということなので、全表走査になります。
正解はありません。 ケースによっては、複数のSQLに分割することも可能です。 NULL検索の場合と同じく、実行計画を確認します。
ＮＯＴ ＩＮ の対象のリストが、副問い合せの場合、ＮＯＴ ＥＸＩＳＴＳに置き換えれば、インデックスを使うことができます。7.3.2章を参照して下さい。
7.1.12GROUP BY
インデックスは使えません。 ただし、ＷＨＥＲＥ句と合わせて使えば、絞られた件数に対して処理されます。
7.1.13CONNECT BY
インデックスは使えません。 また、負荷も大きいコマンドです。
7.1.14NOT NULL定義されてない列のＤＩＳＴＩＮＣＴ
ＯＲＤＥＲ ＢＹと同じで、NOT NULL定義されていれば、インデックスを使えますが、そうでなければ全表走査になります。 また、内部的にはソートも発生しますので、負荷の大きいコマンドです。
7.1.15MAXまたはMIN
MAXまたはMIN関数で取り出すSELECT文が、次のすべての条件を満たすとき、インデックスが使用できます。
l 取り出す項目は、１つの列のインデックスか、または複合インデックスの先頭の列
l SELECTする項目に、他の式がない
l GROUP BY句がない
例： あまり良い例ではないですが、
①社員マスタのプロジェクトNOの最大とその件数を取り出す（この操作に意味はありません）。
SELECT MAX(PROJECT_NO),COUNT(*) FROM GUSER.TBL_EMPLOYEE GROUP BY PROJECT_NO;
②社員マスタのプロジェクトNOの最大と最小を取り出す。プロジェクトNOには、インデックスが存在します。
SELECT MAX(PROJECT_NO), MIN(PROJECT_NO) FROM GUSER.TBL_EMPLOYEE ;
これらはインデックスが使われません。社員マスタ程度の規模なら問題ないですが、規模が大きなテーブルの場合、
考慮すべきです。
7.2 既存のインデックスを上手に使う書き方
インデックスは作りすぎると、逆にオーバヘッドが大きくなりすぎて、遅くなります。 たとえば、あるテーブルに２０種類のインデックスを作成した場合、１件のレコードの更新処理（UPDATE,INSERT,DELETE）をするために、２０のインデックスを更新しなければなりません。 したがって、数は限定しておいて、必要に応じて既存のインデックスを統廃合することになります。
|
参考： ＣＩＭＡでは、１つのテーブルに対して、５つのインデックス(単一または、複合インデックス)を目安にしています。 |
ここでは、作成済みのインデックスを有効に使うために必要な書き方を紹介します。
7.2.1インデックスの調べ方
|
SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME=’テーブル名’’ ORDER BY INDEX_NAME, COLUMN_POSITION ;
テーブル名は大文字で指定します。 |
まず、どんなインデックスが作成されているかを調べるには、次のＳＱＬを実行します。 指定したテーブルに作成されているすべてのインデックスが表示されます。
例：仕訳帳に作成されているインデックスを調べる場合
SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME=’TBL_JOURNAL’
ORDER BY INDEX_NAME, COLUMN_POSITION ;
7.2.2複合インデックスを使う
「複合インデックス」とは、一つのインデックスの中に、２つ以上の列を指定して作成されたインデックスです。
例： 一般会計仕訳帳の勘定科目、補助科目、プロジェクトNOおよび枝番の２つの列で、”JOURNAL_IX4 “という名称の 一つのインデックスを作成されている。
JOURNAL_IX4 = ACCOUNTING_SUBJECT_CD ････(1)
+SUBSIDIAL_SUBJECT_CD ････(2)
+PROJECT_NO ････(3)
+ACCEPT_BRANCH_NO ････(4)
複合インデックスを使用できる検索条件は、作成されたときの指定した列の順序に依存します。この例では、(1)→(4)の順です。 そして、(1)から順番に選択できる列の組合せを使用するとインデックスを使用できます。 この例では、下のようになります。
a) WHERE ACCOUNTING_SUBJECT_CD = ‘1000’
b) WHERE ACCOUNTING_SUBJECT_CD = ‘1000’ AND SUBSIDIAL_SUBJECT_CD = ‘0001’
c) WHERE ACCOUNTING_SUBJECT_CD = ‘1000’ AND SUBSIDIAL_SUBJECT_CD = ‘0001’
AND PROJECT_NO = ‘2900GE75’
d) WHERE ACCOUNTING_SUBJECT_CD = ‘1000’
AND PROJECT_NO = ‘2900GE75’ AND ACCEPT_BRANCH_NO = ‘01’
逆に、使えない書き方は次のようになります。
l (2), (3), (4) の単独指定
l (1)と(3)のような連続しない組合せ
l (2)と(3)、(3)と(4)のような(1）から連続しない組合せ
l (3)と(2)と(1)のような順番が作り方と異なる組合せ
このような条件を使いたければ、別のインデックスを新規に作成するか、JOURNAL_IX4インデックスを分解して、結合インデックスとして使用できるようにする、などの方法を検討する必要があります。
7.2.3結合インデックスを使う
「結合インデックス」とは、複数のインデックス（列ではない）を組み合わせて、内部的に作成されるインデックスです。誰かが明示的に「結合インデックス」というのを作成するわけではありません。
例： 一般会計仕訳帳の３つのインデックスJOURNAL_IX3(SLIP_NO)、JOURNAL_IX1(ACCOUNTING_SUBJECT_CD)、JOURNAL_IX2(APPROPRIATE_DAY)があるとき、次のようになります。
a) WHERE SLIP_NO = ‘3111000001’ AND ACCOUNTING_SUBJECT_CD = ‘1001’
JOURNAL_IX3とJOURNAL_IX1から結合インデックスが作られます
b) WHERE APPROPRIATE_DAY < TO_DATE(‘19981130’, ‘YYYYMMDD’) AND SLIP_NO = ‘3111000001’
＝が優先され、JOURNAL_IX3のみ使われます
c) WHERE APPROPRIATE_DAY < TO_DATE(‘19981130’, ‘YYYYMMDD’) AND ACCOUNTING_SUBJECT_CD >= ‘1001’
最初の条件のインデックスJOURNAL_IX2が優先されます
7.2.4ROWIDでアクセスするのが一番はやい
インデックスとは関係ありませんが、先のアクセスパスの最優先の「ＲＯＷＩＤ」について触れておきます。
ROWIDはレコードの物理的アドレスです。 別な言い方をすれば、インデックスとレコードを結び付けているのがＲＯＷＩＤです。したがって、ＲＯＷＩＤを直接指定できれば、インデックスよりも速いということです。 あまり、使うチャンスはないかもしれませんが、 続けて同じレコードにアクセスするのであれば、最初にROWIDを取得して、2度目のアクセスはそのROWIDを使用するようにする、という使い方をします。
例： （ＣＩＭＡ社員マスタに生年月日はありませんが）５月生まれの人を一人づつ調べて、なんか処理（お祝いのメールを送るとか）をして、最後にマスタの年齢を更新する。
SELECT ROWID, EMPLOYEE_NO INTO :emp_rowid, :emp_no
FROM TBL_EMPLOYEE WHERE BIRTHDAY = ....５月
: （処理）
UPDATE TBL_EMPLOYEE SET AGE = AGE + 1 WHERE ROWID = :rowid ;
7.3 より複雑なSQL－結合（JOIN）、副問合せ、集合演算（INTERSECT, MINUS, UNION）
バッチ処理のアプリケーションでは、複数のテーブルからデータを抽出するというやり方が良く使われます。 単純なSQL（１つの表しか参照しないSQL）と複雑なSQLの基本的な違いは、 複数のテーブルを比較するために、内部でソート／マージ処理が発生することです。 この処理は、CPUやディスクI/Oを消費する高価な（遅いということ）処理で、SQLの書き方によっては、数時間から数日かかることもあります。
ソート／マージ処理は、対象となるデータ件数が少なければ、それだけリソースの消費は少なくて済みますので、結局は、単純なSQLの場合と同様に、適切なWHERE句を書くこと が、基本になります。
7.3.1結合の順序について
結合（JOIN）は、結合する順序によって、コストが変わります。 少し、結合のときの内部の処理に触れておきます。
複数のテーブルを結合させるときの内部の処理：
① ある判断にもとづいて、複数のテーブルから２つを選択する
② ある判断にもとづいて、一方を駆動表（外部表）とし、もう一方を内部表にする
③ ２つのテーブルを結合する
④ 結合の結果と、残りのテーブルを更に結合する
ここで、「ある判断」として用いられるのが、前述のアクセスパスやデータの件数・分布などです。
基本的には、「内部表」として、選択度の高い（検索するデータ量が少ない）テーブルが選ばれ、選択度の低い（検索するデータ量が多い）方のテーブルが「外部表」に選ばれます。
したがって、WHERE句の書き方がまずいと①と②の判断が正しくできないため、結果的に、パフォーマンスを低下させます。
| 図2 結合方法の違い |
結合のイメージを図2に示します。
①ネステッド・ループ結合（ＮＬＪ）
外部表を１件検索して、それと一致する内部表のデータを検索して結合します。外部表が終るまでこれを繰り返します。走査範囲が大きいため、ディスクＩ／Ｏが大きく、また無駄な走査が多いため、外部表と内部表の組合せの件数が多いと、パフォーマンス的に不利です。
②ソート・マージ結合（ＳＭＪ）
外部表と内部表を各々検索し、結果をソートして、最後にマージして結合します。 図2のように、走査範囲がＮＬＪに比べて小さく、無駄なＩ／Ｏが少ないのが特徴です。 しかし、２つの表をソートするため、十分なメモリを必要とし、足りない場合はディスク上でソートされるため、件数が多い場合、パフォーマンス的に不利です。
③ハッシュ結合
図にはありませんが、ハッシュ関数を使って内部表をメモリに展開して、ディスク上の外部表の各行と結合します。 ＳＭＪよりも更に走査範囲が小さくて済みます。 内部表のサイズだけがメモリに関係するので、内部表のサイズが小さい場合、パフォーマンス的に有利です。
通常、レスポンスを重視する場合はＮＬＪ、スループットを重視する場合はＳＭＪです。 ハッシュ結合は、内部表が小さければ、いずれに対しても有利です。 どの結合が用いられるかは、オプティマイザが決定しますので、WHERE句の書き方や、データ件数によって、採用される方法が変わります。
この３者の違いは、ＳＱＬトレースをとらないと分からないのですが、ディスクＩ／Ｏ、ＣＰＵでかなりオーバヘッドが違う場合があります。 ここでは、結合のときには、このようなソート処理が発生するということを認識しておいてもらえば良いでしょう。
|
参考： ＣＩＭＡではレスポンスをオプティマイザ・ゴールにしているので、通常はＮＬＪが選択されます。 |
7.3.2副問合せ（複合文）
副問合せとは、下の例でも分かるように、SELECT文の中に別のSELECT文を含むようなSQLのことです。 結合と考え方は同じで、一方が内部表、他方が外部表になって結合されます。
例： ”部門名称が‘産’で始まる部門に属さない社員は何人いますか？“
SELECT COUNT(*) FROM TBL_EMPLOYEE WHERE DEPARTMENT_CD NOT IN
( SELECT DEPARTMENT_CD FROM TBL_DEPARTMENT WHERE FORMAL_TITLE like '産%' ) ;
この場合、副問い合せの結果に対して、NOT IN は内部ソートを実行します。
また、副問合せは、FORMAN_TITLEを全件検索するため、CPU負荷の大きい処理になります。
【正解】 この場合は内部ソート処理を避けるために、次のようにすべきです。これだと、部門マスタのプライマリキー(DEPARTMENT_CD)を副問い合せ文のWHERE句に使用できるので、インデックスを使った範囲検索になり、スループットは雲泥の差となります。
SELECT COUNT(*) FROM TBL_EMPLOYEE e WHERE NOT EXISTS (
SELECT DEPARTMENT_CD FROM TBL_DEPARTMENT
WHERE e.DEPARTMENT_CD = DEPARTMENT_CD AND FORMAL_TITLE like '産%' ) ;
7.3.3集合演算ＩＮＴＥＲＳＥＣＴ、ＭＩＮＵＳ、ＵＮＩＯＮ
へたな結合（ＪＯＩＮ）文を書くよりは、集合演算を使った方が簡単です。 集合演算は、ＲＤＢの特徴の一つで、これを使えないと、ＲＤＢを使っている意味がないと思います（ちょっと言い過ぎか？）。 ちょっとした調べものには便利です。
しかし、パフォーマンスという点では、必ずしも良いとは言えません。
① INTERSECT（集合の積（交わり））
２つの集合（表）の共通のデータを抽出します。 「共通」を判断するために、２つの集合は、各々内部でソートされます。したがって、集合の件数が多くなると、コストは高くなります。
例： “銀行マスタに登録されている銀行で、社員が口座をもっている銀行を調べたい。“
いろんな書き方ができます。 どれが一番コストが低いか、わかるでしょうか？ 銀行コード(BANK_CD)は、もちろん銀行マスタのプライマリキーです。
a) 【INTERSECTを使用】
SELECT BANK_CD2 FROM GUSER.TBL_EMPLOYEE
INTERSECT
SELECT BANK_CD FROM AUSER.TBL_BANK ;
b) 【結合を使用】
SELECT DISTINCT e.BANK_CD2 FROM GUSER.TBL_EMPLOYEE e, AUSER.TBL_BANK b
WHERE e.BANK_CD2 = b.BANK_CD ;
c) 【副問合せを使用】
SELECT DISTINCT BANK_CD2 FROM GUSER.TBL_EMPLOYEE e WHERE EXISTS
( SELECT COUNT(*) FROM AUSER.TBL_BANK b WHERE e.BANK_CD2 = b.BANK_CD ) ;
この場合、ｃ）がベストです。 社員マスタも、銀行マスタも、条件はありませんので、どちらも全件検索になります。 ただし、全件アクセスするにしても、インデックスを使えた方が有利です。 ｂ）もｃ）も銀行コードが条件に使われているので、銀行マスタはインデックスを使えます。 ｂ）はネステッド・ループ結合になる（と予想される）ので、全件×全件の比較になります。
② MINUS（集合の差）
２つの集合（表）の差分を求めます。 ２つの「違い」を判断するために、２つの集合は、各々内部でソートされます。
例： “銀行マスタに登録されていないのに、社員が口座をもっている銀行を調べたい。“
a) 【MINUSを使用】
SELECT BANK_CD2 FROM GUSER.TBL_EMPLOYEE
MINUS
SELECT BANK_CD FROM AUSER.TBL_BANK ;
b) 【副問合せを使用】
SELECT DISTINCT BANK_CD2 FROM GUSER.TBL_EMPLOYEE e WHERE NOT EXISTS
( SELECT COUNT(*) FROM AUSER.TBL_BANK b WHERE e.BANK_CD2 = b.BANK_CD ) ;
この場合 b) がベターです。 考え方はINTERSECTの場合と同じです。 この場合、結合（JOIN）では良い表現はないでしょう(気が付いた人は教えてください)。
③ ＵＮＩＯＮ（集合の和（結び））
２つの集合（表）の和を求めます。 ＵＮＩＯＮは、２つの集合の共通部分を排除するために、２つの集合は、各々内部でソートされます。 ＵＮＩＯＮ ＡＬＬは重複も許すので、単なる２つの集合の足算となり、ソートは発生しません。
和を求めるのであれば、下手な結合文を書くよりはＵＮＩＯＮの方が良いでしょう。
例： “銀行マスタに登録されているか、または、社員が口座をもっている銀行を調べたい。“
a) 【ＵＮＩＯＮを使用】
SELECT BANK_CD2 FROM GUSER.TBL_EMPLOYEE
UNION
SELECT BANK_CD FROM AUSER.TBL_BANK ;
b) 【ＵＮＩＯＮ ALLを使用】
SELECT BANK_CD2 FROM GUSER.TBL_EMPLOYEE
UNION ALL
SELECT BANK_CD FROM AUSER.TBL_BANK ;
7.4 書き方の順序関係
実行計画が優先順位にもとづいて決まることは、すでに説明しましたが、すべての条件が同等である場合は、どうなるでしょうか？ この場合、ＳＱＬ文に書いた順序が物を言います。
7.4.1等号（＝）のＡＮＤ条件
ＡＮＤで指定された条件の評価が等しい場合、 最初に書かれているＡＮＤ条件のインデックスが検索されます。２つめ以降のインデックスは使われません。
7.4.2等号（＝）と不等号（＜、＜＝、＞、＞＝）のＡＮＤ条件
等号の条件と不等号の条件がＡＮＤで指定されている場合、等号の方のインデックスだけを使って検索します。
7.4.3２つのテーブルの指定順序
結合条件の評価が等しくて、すべて同じ優先順位のアクセスパスになる場合、 ＳＱＬ文のＦＲＯＭ句に書かれている順序が最後（一番右側）の表から処理されます。 最初に処理される表が内部表として全件読み込まれ、次に外部表が１づつ内部表とマッチングされます。内部表は何度も読まれるので、件数が小さい方が、内部処理的にパフォーマンス上有利になります。 そこで、件数の小さいものを最後に指定します。
次の例では、プロジェクトマスタが内部表になりますが、社員マスタの方が件数が小さいので、逆の順序が良いでしょう。
SELECT COUNT(*) FROM TBL_EMPLOYEE, TBL_PROJECT ;
7.4.4３つのテーブルの指定順序
処理される順序は、一番最後からなので、３つのうちで最も依存される表を最後に記述すると良いでしょう。
例： “プロジェクトの担当者で、注文の担当者管理に登録されたプロジェクトを持つ社員の人数？“
SELECT COUNT(e.EMPLOYEE_NO)
FROM TBL_CHARGE_EMPLOYEE_MGMT_LOG c, TBL_EMPLOYEE e, TBL_PROJECT p,
WHERE p.PROJECT_NO = e.PROJECT_NO
AND p.PROJECT_NO LIKE c.CHARGE_EMPLOYEE_MANAGEMENT_NO||’%’ ;
この場合、２つの結合条件にプロジェクトマスタが使われているので、プロジェクトマスタをＦＲＯＭ句の最後に書いています。
7.4.5インデックスがないときのWHERE句の指定順序
インデックスがまったく使えないときのWHERE句の指定順序について、知っておくべきルールを説明します。 ＷＨＥＲＥ句の条件がすべて、インデックスがない、または使えないという場合、どの条件も全表走査になりますが、この場合、条件の指定順序が最終的にはパフォーマンスに効いてきます。
7.4.5.1ANDの条件は重そうな条件を上にする（インデックスがないとき）
ANDの条件は下から上に評価されます。 基本的な考え方としては、処理が重そうな条件が実行されるときには、なるべく対象の件数が少ない方がよいということです。 そこで、最初に処理される（最後に指定したAND）条件でデータが絞りこまれるようにして、 あまい条件（検索される件数が多い）や副問合せのようにそれだけで遅そうな条件は最初に指定します。
例： “担当者管理(履歴)テーブルに３件以上登録されている社員で、年齢が２５歳の人数は？“
SELECT COUNT(*) FROM GUSER.TBL_EMPLOYEE
WHERE 2 > ( SELECT COUNT(*) FROM SUSER.TBL_CHARGE_EMPLOYEE_MGMT_LOG C
WHERE EMPLOYEE_NO = C.CHARGE_EMPLOYEE_NO )
AND FILL_AGE LIKE '25%' ;
最初に処理されるの年齢２５歳という条件で一気に絞られて、次にその社員番号が担当者管理（履歴）に３件以上存在するかが、チェックされます。 条件の順序を逆にすると、 比較する件数が増えるということが、想像できると思います。
7.4.5.2ORの条件は重そうな条件を下にする（インデックスがないとき）
ANDとはまったく逆で、上から下に実行されます。
8 おわりに
実はあえて記述していない、大きな前提があります。 これを書いとかないとフェアでないので、最後に触れます。 Ｏｒａｃｌｅの「オプティマイザ」には、ルールベース・オプティマイザと、コストベース・オプティマイザの２種類が存在します。 上述の内容の多くは、実はルールベースのオプティマイザをベースにしたときの考え方です。「ルール」はアクセスパスの優先順位のことを意味しています。
ルールベースは、「ルール」だけを使って最も優先順位の高いアクセスパスから実行計画を作成します。
|
参考： CIMAではコストベース・オプティマイザを使用しています。 |
一方、コストベースは、「ルール」によってまず使用可能なアクセスパスを調べ、次に各アクセスパスに選択度などのコスト面を加味して、最終的に最もコストの低い実行計画を作成します。 現在は、コストベースが主流であり、また今後はコストベースに１本化されると思われます。
コストベースオプティマイザは、「ルール」に合わないＳＱＬでも、ある程度はルールに合ったＳＱＬに変換してくれます。 例えば、「7.1.9章 インデック付きの列をORで並べない」 などは、ExplainPlanの結果を見ると分かりますが、ちゃんとインデックスを使っています。 じゃあ、上述の内容は無意味か、といえばＮＯで、基本は正しいＳＱＬで記述されていることです。 ExplainPlanツールを使ってみるとわかると思いますが、もとのＳＱＬが適切に記述されていないものは、 コストベースオプティマイザでも直してはくれません。 コストベースの考え方は、データの分布状態などがダイナミックに変わっても、その時々に合わせて実行コスト（ＣＰＵやＩ／Ｏ）が最も低い実行計画を作成する、ということにあり、ＳＱＬ文の変換はその過程で「それなり」に行われる処理に過ぎません。
また、パフォーマンスの良いＳＱＬを書こうと思ったら、やはり「ルール」を基準に考えた方が確実です。 コストの計算の仕方は、かなり面倒なのと、データが変われば、コストや「選択性」の値も変わるので、設計やコーディング、テストの段階でこれを基準にするのはあまり意味がありません。
したがって、ＳＱＬの書き方としては、「ルール」を基準にした形で、このドキュメントを作りました。
オプティマイザ自体は常に改良が加えられている、ということなので、将来は、「作法」や「コーディング基準」など無視しても、最適なＳＱＬに変換されて実行計画が作成できるかもしれません。 そうなれば、開発者は純粋に見やすく、生産性の高いコーディングをすることだけに専念できるのですが……・・。
以上
9 参考文献
・「Oracle7 Serverチューニング リリース７．３」第１版1996.08、 ＯＲＡＣＬＥ
・「Oracle7 ServerＳＱＬ言語リファレンス リリース７．３」第１版1996.05、 ＯＲＡＣＬＥ
・「Oracle7 Serverアプリケーション開発者ガイド リリース７．３」第１版1996.05、 ＯＲＡＣＬＥ
・「ＯＲＡＣＬＥデータベースチューニング」第２版1997.6.10、小幡一郎（訳）、翔永社
・「ＯＲＡＣＬＥ実践Ｑ＆Ａ（上級編)」第１刷1997.9.25、日本オラクル（編著）、ＳＲＣ
・「Oracleパフォーマンスチューニング 第２版」1998.6.19、Mark Gurry他、Ｏ‘ＲＥＩＬＬＹ
・ NiftyServe SORACLEフォーラム
・ 日本オラクル・ホームページ http://www.oracle.co.jp
10 付録A Explain Planの例
|
書式： explain plan set statement_id = xxxxxxx for ＳＱＬ文 ； xxxxxxx ：実行結果を識別するためのＩＤです。 以下の例ではすべて‘ＨＩＲＯ’を使っています。 実行結果は plan_tableというテーブルに追加されます。 何回も実行したい場合は、IDをその度に変更するか、作成済みのデータを削除してから実行してください。 |
簡単にExplainPlanコマンド使い方を紹介しておきます。
7.1.1章 「条件の左側には関数を使わない」 の実行計画
|
SQL> explain plan set statement_id= 'HIRO' for
QUERY_PLAN OBJECT_NODE -------------------------------------------------- --------------------------------- SELECT STATEMENT Cost = 56 2.1 SORT AGGREGATE 3.1 INDEX FULL SCAN EMPLOYEE_PK UNIQUE
【正解】 SQL> explain plan set statement_id= 'HIRO' for 2 select count(*) from guser.tbl_employee where employee_no like '857%' ;
QUERY_PLAN OBJECT_NODE -------------------------------------------------- --------------------------------- SELECT STATEMENT Cost = 2 2.1 SORT AGGREGATE 3.1 INDEX RANGE SCAN EMPLOYEE_PK UNIQUE |
|
ﾌﾟﾗｲﾏﾘｷｰを使って一発検索！ |
|
見当違いのインデックスで全表走査(FULL SCAN)している |
|
Costの違いに注目 |
|
SQL> explain plan set statement_id= 'HIRO' for 2 SELECT PROJECT_NAME FROM TBL_PROJECT 3 WHERE PROJECT_NO||ACCEPT_BRUNCH_NO = '2900GE7501' ;
QUERY_PLAN OBJECT_NODE -------------------------------------------------- ------------------------------- SELECT STATEMENT Cost = 5509 2.1 TABLE ACCESS BY ROWID TBL_PROJECT 3.1 BITMAP CONVERSION TO ROWIDS 4.1 BITMAP INDEX FULL SCAN PROJECT_IX1
SQL> explain plan set statement_id= 'HIRO' for 2 SELECT PROJECT_NAME FROM TBL_PROJECT 3 WHERE PROJECT_NO = '2900GE75' AND ACCEPT_BRUNCH_NO = '01' ;
QUERY_PLAN OBJECT_NODE -------------------------------------------------- -------------------------------- SELECT STATEMENT Cost = 3 2.1 TABLE ACCESS BY ROWID TBL_PROJECT 3.1 INDEX UNIQUE SCAN PROJECT_PK UNIQUE |
7.1.3章 「算術演算も関数です」 の実行計画
|
SQL> explain plan set statement_id= 'HIRO' for 2 SELECT COUNT(*) FROM TBL_SALES WHERE SALES_AMNT_YEN * 1.05 > 100000 ;
QUERY_PLAN OBJECT_NODE -------------------------------------------------- ---------------------------- SELECT STATEMENT Cost = 26 2.1 SORT AGGREGATE 3.1 INDEX FULL SCAN TEST_SALES_IX1 NON-UNIQUE
SQL> explain plan set statement_id= 'HIRO' for 2 SELECT COUNT(*) FROM TBL_SALES WHERE SALES_AMNT_YEN > 95238 ;
QUERY_PLAN OBJECT_NODE -------------------------------------------------- ----------------------------- SELECT STATEMENT Cost = 7 2.1 SORT AGGREGATE 3.1 INDEX RANGE SCAN TEST_SALES_IX1 NON-UNIQUE |
|
SQL> explain plan set statement_id ='HIRO' for 2 SELECT EMPLOYEE_NAME FROM GUSER.TBL_EMPLOYEE WHERE EMPLOYEE_NO = 123456 ;
QUERY_PLAN ------------------------------------------------- SELECT STATEMENT Cost = 68 2.1 TABLE ACCESS FULL TBL_EMPLOYEE
【正解】 SQL> explain plan set statement_id ='HIRO' for 2 SELECT EMPLOYEE_NAME FROM GUSER.TBL_EMPLOYEE WHERE EMPLOYEE_NO = '123456' ;
QUERY_PLAN ------------------------------------------------- SELECT STATEMENT Cost = 2 2.1 TABLE ACCESS BY ROWID TBL_EMPLOYEE 3.1 INDEX UNIQUE SCAN EMPLOYEE_PK UNIQUE |
7.1.5章 「データ型の混合」 の実行計画
7.1.6章 「列と列の＜、＞、＜＝、＞＝」 の実行計画
|
【インデックスが使われない例】 SQL> explain plan set statement_id = 'HIRO' for 2 select count(*) from TBL_BUYING where RTRND_GDS_INPUT_DATE > BUYING_DCSN_INPUT_DATE ;
QUERY_PLAN ------------------------------------------------------------ SELECT STATEMENT Cost = 550 2.1 SORT AGGREGATE 3.1 TABLE ACCESS FULL TBL_BUYING
【インデックスが使われる例】 SQL> explain plan set statement_id = 'HIRO' for 2 select count(*) from TBL_BUYING where RTRND_GDS_INPUT_DATE > 19981130;
QUERY_PLAN ------------------------------------------------------------ SELECT STATEMENT Cost = 4 2.1 SORT AGGREGATE 3.1 INDEX RANGE SCAN BUYING_IX7 NON-UNIQUE |
|
SQL> explain plan set statement_id= 'HIRO' for 2 SELECT COUNT(*) FROM TBL_GOODS WHERE GOODS_NAME_KANJI LIKE '%マイクロ%' ;
QUERY_PLAN OBJECT_NODE -------------------------------------------------- --------------------------------
2.1 SORT AGGREGATE 3.1 TABLE ACCESS FULL TBL_GOODS |
7.1.7章 「文字列の部分検索」 の実行計画
|
SQL> explain plan set statement_id= 'HIRO' for 2 SELECT GOODS_NAME_KANJI FROM TBL_GOODS ORDER BY MAKER_CODE;
QUERY_PLAN OBJECT_NODE ------------------------------------------------------- ------------------ SELECT STATEMENT Cost = 17561 2.1 SORT ORDER BY 3.1 TABLE ACCESS FULL TBL_GOODS |
7.1.8章 「NOT NULL定義されていない列でのORDER BY」 の実行計画
7.1.9章 「インデック付きの列をORで並べない」 の実行計画
|
SQL> explain plan set statement_id= 'HIRO' for 2 SELECT COUNT(*) FROM AUSER.TBL_JOURNAL 3 WHERE ACCOUNTING_SUBJECT_CD = '1003' OR ACCOUNTING_SUBJECT_CD = '1004' 4 OR ACCOUNTING_SUBJECT_CD = '1005' ;
QUERY_PLAN OBJECT_NODE ------------------------------------------------------- -------------------------- SELECT STATEMENT Cost = 216 2.1 SORT AGGREGATE 3.1 CONCATENATION 4.1 INDEX RANGE SCAN JOURNAL_IX1 NON-UNIQUE 4.2 INDEX RANGE SCAN JOURNAL_IX1 NON-UNIQUE 4.3 INDEX RANGE SCAN JOURNAL_IX1 NON-UNIQUE |
7.1.10章 「NULL検索、NOT NULL検索」 の実行計画
|
SQL> explain plan set statement_id= 'HIRO' for 2 SELECT EXTRL_CMPNY_NM_KANJI FROM SUSER.TBL_EXTERNAL_COMPANY 3 WHERE EXTRL_CMPNY_NM_KANA IS NULL ;
------------------------------------------------------- ----------------- SELECT STATEMENT Cost = 603 2.1 TABLE ACCESS FULL TBL_EXTERNAL_COMPANY |
|
SQL> explain plan set statement_id= 'HIRO' for 2 SELECT COUNT(*) FROM AUSER.TBL_JOURNAL 3 WHERE ACCOUNTING_SUBJECT_CD != '1003';
QUERY_PLAN OBJECT_NODE ------------------------------------------------------- ----------------- SELECT STATEMENT Cost = 26 2.1 SORT AGGREGATE 3.1 INDEX FULL SCAN JOURNAL_IX6 NON-UNIQUE |
7.1.11章 「NOT EQUAL ( != 、＜＞) 検索、 NOT IN検索」 の実行計画
7.1.15章 「MAXまたはMIN」 の実行計画
|
【インデックスが使われる例】 SQL> explain plan set statement_id = 'HIRO' for 2 SELECT MAX(PROJECT_NO) FROM GUSER.TBL_EMPLOYEE ;
------------------------------------------------------------ SELECT STATEMENT Cost = 67 2.1 SORT AGGREGATE 3.1 INDEX FULL SCAN EMPLOYEE_IX2 NON-UNIQUE
【インデックスが使われない例】 ① SQL> explain plan set statement_id = 'HIRO' for 2 SELECT MAX(PROJECT_NO),COUNT(*) FROM GUSER.TBL_EMPLOYEE GROUP BY PROJECT_NO;
QUERY_PLAN ------------------------------------------------------------ SELECT STATEMENT Cost = 67 2.1 SORT GROUP BY 3.1 TABLE ACCESS FULL TBL_EMPLOYEE ② SQL> explain plan set statement_id = 'HIRO' for 2 SELECT MAX(PROJECT_NO), MIN(PROJECT_NO) FROM GUSER.TBL_EMPLOYEE ;
QUERY_PLAN ------------------------------------------------------------ SELECT STATEMENT Cost = 67 2.1 SORT AGGREGATE 3.1 TABLE ACCESS FULL TBL_EMPLOYEE |
|
【インデックスが使われる例】 SQL> explain plan set statement_id = 'HIRO' for 2 SELECT EMPLOYEE_NO, EMPLOYEE_NAME FROM GUSER.TBL_EMPLOYEE ORDER BY EMPLOYEE_NO ;
QUERY_PLAN ----------------------------------------------------------- SELECT STATEMENT Cost = 5919 2.1 TABLE ACCESS BY ROWID TBL_EMPLOYEE 3.1 INDEX FULL SCAN EMPLOYEE_PK UNIQUE
【インデックスが使われない例】 SQL> explain plan set statement_id = 'HIRO' for 2 SELECT EMPLOYEE_NO, EMPLOYEE_NAME FROM GUSER.TBL_EMPLOYEE ORDER BY PROJECT_NO ;
QUERY_PLAN ----------------------------------------------------------- SELECT STATEMENT Cost = 338 2.1 SORT ORDER BY 3.1 TABLE ACCESS FULL TBL_EMPLOYEE |
7.1.8.1章 「ORDER BY」 の実行計画
7.2.2章 「複合インデックスを使う」 の実行計画
|
a)の条件 SQL> explain plan set statement_id= 'HIRO' for 2 SELECT COUNT(*) FROM AUSER.TBL_JOURNAL WHERE ACCOUNTING_SUBJECT_CD = '1000' ;
QUERY_PLAN OBJECT_NODE -------------------------------------------------- ---------------------------- SELECT STATEMENT Cost = 2 2.1 SORT AGGREGATE 3.1 INDEX RANGE SCAN JOURNAL_IX4 NON-UNIQUE
b)の条件 SQL> explain plan set statement_id= 'HIRO' for 2 SELECT COUNT(*) FROM AUSER.TBL_JOURNAL 3 WHERE ACCOUNTING_SUBJECT_CD = '1000' AND SUBSIDIAL_SUBJECT_CD = '0001';
QUERY_PLAN OBJECT_NODE -------------------------------------------------- ----------------------------- SELECT STATEMENT Cost = 2 2.1 SORT AGGREGATE 3.1 INDEX RANGE SCAN JOURNAL_IX4 NON-UNIQUE
c)の条件 SQL> explain plan set statement_id= 'HIRO' for 2 SELECT COUNT(*) FROM AUSER.TBL_JOURNAL WHERE ACCOUNTING_SUBJECT_CD = '1000' 3 AND SUBSIDIAL_SUBJECT_CD = '0001' AND PROJECT_NO = '2900GE75' ;
QUERY_PLAN OBJECT_NODE -------------------------------------------------- --------------------------------- SELECT STATEMENT Cost = 2 2.1 SORT AGGREGATE 3.1 INDEX RANGE SCAN JOURNAL_IX4 NON-UNIQUE
d)の条件 SQL> explain plan set statement_id= 'HIRO' for 2 SELECT COUNT(*) FROM AUSER.TBL_JOURNAL WHERE ACCOUNTING_SUBJECT_CD = '1000' 3 AND SUBSIDIAL_SUBJECT_CD = '0001' AND PROJECT_NO = '2900GE75' AND ACCEPT_BRANCH_NO ='01' ;
QUERY_PLAN OBJECT_NODE -------------------------------------------------- ----------------------------------- SELECT STATEMENT Cost = 1 2.1 SORT AGGREGATE 3.1 INDEX RANGE SCAN JOURNAL_IX4 NON-UNIQUE
正しく指定しない場合（ACCOUNTING_SUBJECT_CDが条件に存在しない） SQL> explain plan set statement_id= 'HIRO' for 2 SELECT COUNT(*) FROM AUSER.TBL_JOURNAL WHERE SUBSIDIAL_SUBJECT_CD = '0001' 3 AND PROJECT_NO = '2900GE75' AND ACCEPT_BRANCH_NO ='01' ;
QUERY_PLAN OBJECT_NODE -------------------------------------------------- --------------------------------- SELECT STATEMENT Cost = 26 2.1 SORT AGGREGATE 3.1 INDEX FULL SCAN JOURNAL_IX6 NON-UNIQUE |
7.3.2章 「副問合せ」 の実行計画
|
SQL> explain plan set statement_id= 'HIRO' for SELECT COUNT(*) FROM TBL_EMPLOYEE WHERE DEPARTMENT_CD NOT IN ( SELECT DEPARTMENT_CD FROM TBL_DEPARTMENT WHERE FORMAL_TITLE like '産%' ) ;
QUERY_PLAN ------------------------------------------------------------ SELECT STATEMENT Cost = 67 2.1 SORT AGGREGATE 3.1 FILTER 4.1 TABLE ACCESS FULL TBL_EMPLOYEE 4.2 TABLE ACCESS BY ROWID TBL_DEPARTMENT 5.1 BITMAP CONVERSION TO ROWIDS 6.1 BITMAP INDEX FULL SCAN DEPARTMENT_IX1
【正解】 SQL> explain plan set statement_id= 'HIRO' for SELECT COUNT(*) FROM TBL_EMPLOYEE e WHERE NOT EXISTS ( SELECT DEPARTMENT_CD FROM TBL_DEPARTMENT WHERE e.DEPARTMENT_CD = DEPARTMENT_CD AND FORMAL_TITLE like '産%' ) ;
QUERY_PLAN ------------------------------------------------------------ SELECT STATEMENT Cost = 67 2.1 SORT AGGREGATE 3.1 FILTER 4.1 TABLE ACCESS FULL TBL_EMPLOYEE 4.2 TABLE ACCESS BY ROWID TBL_DEPARTMENT 5.1 INDEX RANGE SCAN DEPARTMENT_PK UNIQUE |
7.3.3章 集合演算ＩＮＴＥＲＳＥＣＴ の実行計画
|
SQL> explain plan set statement_id ='HIRO' for SELECT BANK_CD2 FROM GUSER.TBL_EMPLOYEE INTERSECT SELECT BANK_CD FROM AUSER.TBL_BANK ;
QUERY_PLAN ------------------------------------------------- SELECT STATEMENT Cost = 217 2.1 INTERSECTION 3.1 SORT UNIQUE 4.1 TABLE ACCESS FULL TBL_EMPLOYEE 3.2 SORT UNIQUE 4.1 TABLE ACCESS FULL TBL_BANK
SQL> explain plan set statement_id ='HIRO' for SELECT DISTINCT e.BANK_CD2 FROM GUSER.TBL_EMPLOYEE e, AUSER.TBL_BANK b WHERE e.BANK_CD2 = b.BANK_CD ;
QUERY_PLAN -------------------------------------------------- SELECT STATEMENT Cost = 180 2.1 SORT UNIQUE 3.1 NESTED LOOPS 4.1 TABLE ACCESS FULL TBL_EMPLOYEE 4.2 INDEX UNIQUE SCAN BANK_PK UNIQUE
SQL> explain plan set statement_id ='HIRO' for SELECT DISTINCT BANK_CD2 FROM GUSER.TBL_EMPLOYEE e WHERE EXISTS ( SELECT COUNT(*) FROM AUSER.TBL_BANK b WHERE e.BANK_CD2 = b.BANK_CD ) ;
QUERY_PLAN ------------------------------------------------- SELECT STATEMENT Cost = 90 2.1 SORT UNIQUE 3.1 FILTER 4.1 TABLE ACCESS FULL TBL_EMPLOYEE 4.2 SORT AGGREGATE 5.1 INDEX UNIQUE SCAN BANK_PK UNIQUE |
|
SQL> explain plan set statement_id ='HIRO' for 2 SELECT BANK_CD2 FROM GUSER.TBL_EMPLOYEE 3 MINUS 4 SELECT BANK_CD FROM AUSER.TBL_BANK ;
QUERY_PLAN -------------------------------------------------- SELECT STATEMENT Cost = 217 2.1 MINUS 3.1 SORT UNIQUE 4.1 TABLE ACCESS FULL TBL_EMPLOYEE 3.2 SORT UNIQUE 4.1 TABLE ACCESS FULL TBL_BANK
SQL> explain plan set statement_id ='HIRO' for SELECT DISTINCT BANK_CD2 FROM GUSER.TBL_EMPLOYEE e WHERE NOT EXISTS ( SELECT COUNT(*) FROM AUSER.TBL_BANK b WHERE e.BANK_CD2 = b.BANK_CD ) ;
QUERY_PLAN --------------------------------------------------- SELECT STATEMENT Cost = 90 2.1 SORT UNIQUE 3.1 FILTER 4.1 TABLE ACCESS FULL TBL_EMPLOYEE 4.2 SORT AGGREGATE 5.1 INDEX UNIQUE SCAN BANK_PK UNIQUE |
7.3.3章 集合演算ＭＩＮＵＳ の実行計画
7.3.3章 集合演算ＵＮＩＯＮ の実行計画
|
SQL> explain plan set statement_id ='HIRO' for SELECT BANK_CD2 FROM GUSER.TBL_EMPLOYEE UNION SELECT BANK_CD FROM AUSER.TBL_BANK ;
QUERY_PLAN --------------------------------------------------- SELECT STATEMENT Cost = 217 2.1 SORT UNIQUE 3.1 UNION-ALL 4.1 TABLE ACCESS FULL TBL_EMPLOYEE 4.2 TABLE ACCESS FULL TBL_BANK
SQL> explain plan set statement_id ='HIRO' for SELECT BANK_CD2 FROM GUSER.TBL_EMPLOYEE UNION ALL SELECT BANK_CD FROM AUSER.TBL_BANK ;
QUERY_PLAN --------------------------------------------------- SELECT STATEMENT Cost = 79 2.1 UNION-ALL 3.1 TABLE ACCESS FULL TBL_EMPLOYEE 3.2 TABLE ACCESS FULL TBL_BANK |
11 付録B チェックリスト
本文中の注意事項をチェックリストとして添付します。
|
| チェック事項 | 説明 |
| 再利用可能SQL | 3章 | |
|
| コーディング基準を作成していますか？（以下、サンプル） ・ＳＱＬ文は大文字または小文字で統一する ・キーワード(from, where, and, orなど)は改行して、行の先頭に記述する ・ワード間の区切りはスペース１バイトとし、タブは使用しない ・ホスト変数を使用する ・テーブル名には別名をつけ、列名は別名で修飾する | 3 |
| 単純なSQL | 7.1章 | |
|
| 条件の左辺に関数 を使用していませんか？ | 7.1.1 |
|
| 条件の左辺に算術演算 を使用していませんか？ | 7.1.3 |
|
| 条件の左辺に文字連結 を使用していませんか？ | 7.1.2 |
|
| データ型を混合して 使用していませんか？ | 7.1.5 |
|
| 列同士の＜、＞、＜＝、＞＝ を使っていませんか？ | 7.1.5 |
|
| 文字列の部分検索 を使用していませんか？ | 7.1.7 |
|
| NOT NULL定義されていない列でORDER BYを使用していませんか？ | 7.1.8 |
|
| インデック付きの列をOR条件で並べていませんか? | 7.1.9 |
|
| IS NULL検索 をしていませんか？ | 7.1.10 |
|
| IS NOT NULL検索 をしていませんか？ | 7.1.10 |
|
| NOT EQUAL ( != 、＜＞) 検索 をしていませんか？ | 7.1.11 |
|
| NOT IN検索 をしていませんか？ | 7.1.11 |
|
| NOT INをNOT EXISTSに 書き換えられませんか？ | 7.1.11 |
|
| GROUP BY を使用していませんか？ | 7.1.12 |
|
| CONNECT BY を使用していませんか？ | 7.1.13 |
|
| NOT NULL定義されていない列でDISTINCTを使用していませんか？ | 7.1.14 |
|
| MAX（MIN）関数とGROUP BY を併用していませんか？ | 7.1.15 |
|
| MAX（MIN）関数を他の項目といっしょにSELECTしていませんか？ | 7.1.15 |
| 既存インデックスの有効利用 | 7.2章 | |
| 複合インデックス | インデックスを使える順番に列を指定していますか？ | 7.2.2 |
| 結合インデックス | 既存のインデックスを組合わせて使えますか？ | 7.2.3 |
| 複雑なSQL | 7.3章 | |
| 結合文 | 適切なソート方法が使われていますか？ | 7.2.3 |
|
| 結合条件はインデックスが使えるような適切な書式で書かれていますか？ | 7.2.3 |
| 副問合せ | 他の文に書き換えられませんか？ | 7.3.2 |
| 集合演算INTERSECT | 他の文に書き換えられませんか？ | 7.3.3 |
| 集合演算 MINUS | 他の文に書き換えられませんか？ | 7.3.3 |
| 集合演算UNION | 他の文に書き換えられませんか？ | 7.3.3 |
| 注意事項 |
| |
| オンライン／バッチ | CIMAはレスポンス（オンライン）重視です。バッチの場合は、ExplainPlanで実行計画を確認してください。 | 4.2章 |
| インデックスが有効な目安 | 全体の１５％～２０％以下の検索件数のときにインデックスは有効です。 | 6章 |
|
|
|
|
7万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
