SQLパフォーマンスへのチェックポイント

大文字/小文字/空白や改行の数を統一する 　　処理内容が同じSQLでも、大文字/小文字/空白や改行の数が異なると、別々にキャッシュされてしまい、解析済みのSQLが共有されなくなるので、その分パフォーマンスが低下する。 　　SQLのコーディング方法を統一して、キャッシュ上で共有されるようにしましょう。 下記４つのＳＱＬは、処理内容は同じですが、それぞれ、メモリ上にキャッシュされ、再利用されません。 大文字/小文字/空白や改行の数は異なるので、違うSQLと解釈される。 　select * from dept; 　select * from DEPT; 　select 　　* from dept; 　select * 　　　 　from dept; バインド変数を使用する 　　SQLで、変数の値を設定する場合は、バインド変数を使用することによって、SQLが共有されます。 　○　select * from dept where dept_id = :dept_id; 　×　select * from dept where dept_id = 'D001'; SELECT * は使用しない 　　SELECTで*を使用すると、解析/IOともにパフォーマンスが低下するので、必要な項目のみ指定します。 　○ select dept_id, dept_name from dept; 　× select * from dept; SELECT COUNT(*)は使用しない 　　COUNT関数を使用する時は、COUNT(列名）で指定する。 　　但し、COUNT(列名）は、指定した列がNULLの場合は、カウントしないので、プライマリキー項目などのNOT NULL列を指定する。 　○　select count(dept_id) from dept; 　×　select count(*) from dept; ANDよりBETWEENの使用を検討する 　　BETWEENは、指定された範囲評価の操作が1回で済むのに対し、ANDは複数回の操作となるので、ANDよりBETWEENの使用を検討する 　○　select * from emp where salary between 0 and 3000; 　×　select * from emp where salary >= 0 and salary <= 3000; HAVINGよりWHEREの使用を検討する 　　HAVINGは非常に重い処理なので、集計結果の判定以外は、HAVING句よりWHERE句の使用を検討する。 　×　select dept_id, sum( salary ) from emp group by dept_id having dept_id = 'D02'; 　○　select dept_id, sum( salary ) from emp where dept_id = 'D02' group by dept_id; UNIONよりUNION ALLの使用を検討する 　　UNIONはSELECTの結果をマージした後に、暗黙のソート処理をして重複データを排除しますが、重複データが無いことが分かっている場合は、暗黙のソート処理を回避するために、UNION ALLを使用する 　　※UNIONの他にも、DISTINCTや、GROUP BY、INTERSEST、MINUS等にも暗黙のソート処理が実行されるので、極力使用を避けた方が良い。 テーブルに別名を付ける 　　テーブルに別命を付けると、解析速度が向上するので、別名を付けるようにする 　○ select a.emp_id, a.emp_name from emp a; 　× select emp_id, emp_name from emp; ROWIDの使用を検討する 　　ROWIDとは、データベース内のレコードのアドレスを表すOracleの内部的な管理情報です。 　　その為、WHERE句にROWIDを使用すると、テーブルのレコードに最速でアクセスできます。 　　但し、Export／Importや表の移動（alter table moveコマンド）などでROWIDは変更されてしまう為、ROWIDの使用は、SELECTでデータを取得後に再度アクセスする場合などに限定されます。 　例）データを検索して、そのレコードを更新する。 　select emp_id,salary,rowid from emp; 　update emp set salary = 250 where rowid = 'AAAF+OAAIAAABmMABL'; 列番号は使用しない 　　ORDER BY句に列番号で指定した場合、ＳＱＬ解析時に読み替え処理が発生するのでパフォーマンス低下に繋がる 　○ select a.emp_id, a.emp_name from emp a order by salary; 　× select a.emp_id, a.emp_name from emp a order by 5; DISTINCT句よりEXISTS句の使用を検討する 　　DISTINCTは、条件に一致するレコードを取り出し暗黙のソート処理後に重複レコードを排除することに対し、EXISTS句は条件に一致するレコード１件でもあればそこで処理は終了する為、暗黙のソート処理をしない分、DISTINCTに比べると負荷が小さくなる 　× select distinct a.emp_id, a.dept_id from emp a,dept b 　　　　　　　　　　　　where a.dept_id = b.dept_id; 　○ select a.emp_id, a.dept_id from emp a 　　　　　　　where exists (select 'X' from dept_b where a.dept_id = b.dept_id); 　※DISTINCTの他にも、GROUP BYやUNION、INTERSEST、MINUS等は暗黙のソート処理が実行されるので、極力使用を避けた方が良い。 NOT IN句よりNOT EXISTS句の使用を検討る 　　NOT IN句は、内部的にソートマージの結合をすることでテーブルをフルスキャンするのに対し、NOT EXISTS句は条件に一致するレコード１件でもあればそこで処理は終了する為、NOT IN句に比べると負荷が小さくなる 　× select distinct a.emp_id, a.dept_id from emp a 　　　　where a.dept_id not in (select b.dept_id from dept where job_id = 'J01'); 　○ select a.emp_id, a.dept_id from emp a 　　　where not exists (select 'X' from dept_b where a.dept_id = b.dept_id and job_id = 'J01'); INDEX列で論理比較のNOTの使用は、INDEXが使用されない。 　×　select * from emp where emp_id <> 'E01'; 　×　select * from emp where emp_id not in ('E01','E02','E03'); INDEX列で関数の使用は、INDEXが使用されない。 　　Ｂツリー・インデックスで関数を使用すると、INDEXが使用されない。 　　但し、ファンクション・インデックスを作成した場合は関数が使用できる。 　×　select * from emp where substr(emp_id,1,1) = 'E'; INDEX列で演算子の使用は、INDEXが使用されない。 　○　select * from emp where salary > 100; 　×　select * from emp where salary - 50 > 100; INDEX列でNULL比較の使用は、INDEXが使用されない。 　　Ｂツリー・インデックスの中にはNULL値は存在しないので、WHERE句に「IS NULL」を指定するとINDEXは使用されない。 　　但し、ビットマップ・インデックスの場合は、ＮＵＬＬ値はインデックスに含まれるので、INDEXは使用される。 　×　select * from emp where emp_name is null; INDEX列でLIKEは前方一致以外は、INDEXが使用されない。 　○　select * from emp where emp_id = 'E00%'; 　×　select * from emp where emp_id = '1'; 　×　select * from emp where emp_id = '%'; ORDER BY句の指定列が全てINDEXに指定ないと、ソートにINDEXが使用されない。 　ORDER BY 句で指定する項目は、全てINDEXに含まれ、かつ、NOT NULL項目の場合は、高速にソートされます。 暗黙の型変換は、INDEXが使用されない。 　　WHERE句で指定した、列の型とデータの型が異なる場合、ORACLEは自動的に型変換を実行するが、型を自動変換する場合はINDEXが使用されない。 　　　　　dept_id が cahr型で定義されている場合 　×　select * from dept where dept_id = 123; 　○　select * from dept where dept_id = '123'; 複合INDEXは先頭列から指定しないと、INDEXが使用されない。 　　　以下の順番で複合INDEXを作成 　（１）emp_id 　（２）dept_id 　（３）job_id 　　上記の複合INDEXの場合の、INDEXの使用/未使用となるWHERE句パターン 　○ select * from emp where emp_id = 'E01' and dept_id = 'D01' and job_id = 'J01'; 　○ select * from emp where emp_id = 'E01' and dept_id = 'D01'; 　○ select * from emp where emp_id = 'E01' and job_id = 'J01'; 　○ select * from emp where emp_id = 'E01'; 　× select * from emp where dept_id = 'D01'; 　× select * from emp where job_id = 'J01'; 　× select * from emp where dept_id = 'D01' and job_id = 'J01';