ステップアップ Oracle

ステップアップ Oracle（作成中）

初～中級SEのためのOracle情報です。

SQLパフォーマンスチューニング

データベースを利用してシステムを構築するとき、パフォーマンスはいつも大きな問題となります。根本的にはテーブル設計が大切なのですが、SQLの記述一つでパフォーマンスが劇的に変化してしまうようなことも少なくはありません。ここではいくつかの例を挙げて、パフォーマンスの良いSQLを書く方法をまとめたいと思います。
さらに詳しく知りたい方には、以下の本がおすすめです。

SEのためのOracleチューニングハンドブック

後藤孝憲名和満五嶋和彦井原秀樹

ソフトバンククリエイティブ(2003-11-17)

定価￥ 2,940

ルール1：　SQLの記述をスペースの数や大文字小文字の区別まで統一する。

OracleはSQLを発行するときにキャッシュを利用して効率を高めようとしますが、SQL文が同じ意味でも、スペースの位置や大文字小文字が違っただけでキャッシュヒットしません。SQLを記述するときはルールを徹底して、同じSQL文であれば一文字も違わないようになるようにする必要があります。

以下のSQLはすべて違うSQLとして扱われ、キャッシュを利用できません。

select id from emp;
SELECT ID FROM EMP;
SELECT ID FROM EMP ;
SELECT ID
FROM EMP;

ルール2：　SELECT * は利用しない。列名をきちんと記述する。

SELECT * は不要な列まで処理してしまいます。きちんと列名を記述したほうが、パフォーマンスは良くなります。

×SELECT * FROM EMP;
○SELECT ID, NAME FROM EMP;

ルール3：　1:n結合のときにDISTINCTはできるだけ使用しない。

DISTINCTは重複するレコードを表示しないオプションですが、実行時に暗黙ソートがかかるためパフォーマンスが落ちます。
EXISTSを使用して先に絞り込みを行っておくと暗黙ソートが行われずパフォーマンスの向上を期待できます。
以下はEMPテーブルとDEPTテーブルを1:nで結合する場合のSQLです。

×SELECT DISTINCT A.COL1, A.COL2 FROM DEPT A, EMP B WHERE A.COL1 = B.COL1;
○SELECT A.COL1, A.COL2 FROM DEPT A WHERE EXISTS(SELECT 'X' FROM EMP B WHERE A.COL1 = B.COL1);

ルール4：　ORDER BYには列番号ではなく列名を指定する。

ORDER BYは列番号で指定するよりも、列名を指定した方がパフォーマンスが良くなります。

×SELECT ID FROM EMP ORDER BY 1;
○SELECT ID FROM EMP ORDER BY ID;

ルール5：　BETWEENが使えるときはANDではなくBETWEENを使用する。

×SELECT ID FROM EMP WHERE AGE >= 20 AND AGE <=50;
○SELECT ID FROM EMP WHERE AGE BETWEEN 20 AND 50;

ルール6：　インデックス項目をnull（is null, is not null)と比較しない

インデックスが設定してある項目をis nullやis not nullなどnullと比較するとインデックスが有効になりません。

×SELECT ID FROM EMP WHERE AGE is not null;
○SELECT ID FROM EMP WHERE AGE >= 0;

ルール7：　!=や<>をインデックス列に使用しない

×SELECT ID FROM EMP WHERE AGE != 20;
○SELECT ID FROM EMP WHERE AGE >20 OR AGE <20;

ルール8：　インデックス列に計算式や関数を使用しない

×SELECT ID FROM EMP WHERE trim(NAME) = 'SCOTT';
×SELECT ID FROM EMP WHERE AGE/5 = 2;
○SELECT ID FROM EMP WHERE AGE = 2 * 5;

ルール9：　GROUP BYの結果に対して絞り込みを行う時以外は、HAVINGは使わずWHEREにする。

GROUP BYのHAVINGは処理が重いので、WHEREに置き換えます。

×SELECT ID, SALARY FROM EMP WHERE GROUP BY AGE HAVING DEPT_ID = '11777';
○SELECT ID, SALARY FROM EMP WHERE DEPT_ID = '11777' GROUP BY AGE;

ルール10：　INの使用時には頻度の高いモノから順に並べる。

INの使用時には、左から条件比較されるので、頻度の高いモノから順に並べるとパフォーマンスが最適化されます。

×SELECT ID FROM EMP WHERE AGE IN('100', '65', '30');
○SELECT ID FROM EMP WHERE AGE IN('30', '65', '100');