SQL文の見直し

● 大文字/小文字/空白や改行の数を統一する

  処理内容が同じSQLでも、大文字/小文字/空白や改行の数が異なると、別々にキャッシュされてしまい、解析済みのSQLが共有されなくなるので、その分パフォーマンスが低下する。

  SQLのコーディング方法を統一して、キャッシュ上で共有されるようにしましょう。

下記4つの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';
   ※バインド変数の指定方法は、JAVA、SQLPLUS、ProCなど実行環境で異なる

● 初期化パラメータ「CURSOR_SHARING」を使用すれば、大文字/小文字/空白や改行の数を統一や、バインド変数を使用しなくても、
同じSQLを共有できるようになります。


しかしながら、「CURSOR_SHARING」の変更に伴って遅くなるSQLがでる可能性もあるので、「CURSOR_SHARING」の変更は慎重にする必要があります。

● 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句に列番号で指定した場合、SQL解析時に読み替え処理が発生するのでパフォーマンス低下に繋がる
 ○ 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句は条件に一致するレコード1件でもあればそこで処理は終了する為、暗黙のソート処理をしない分、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句は条件に一致するレコード1件でもあればそこで処理は終了する為、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が使用されない
  Bツリー・インデックスで関数を使用すると、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が使用されない
  Bツリー・インデックスの中にはNULL値は存在しないので、WHERE句に「IS NULL」を指定するとINDEXは使用されない。
  但し、ビットマップ・インデックスの場合は、NULL値はインデックスに含まれるので、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 = '%001';

 × select * from emp where emp_id = '%00%';

● 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を作成
 (1)emp_id
 (2)dept_id
 (3)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';