セカイモンの裏側

★毎週木曜日更新★ 海外ショッピングサイト『セカイモン』のブログです。私たちスタッフの仕事風景や日々の出来事など、     “セカイモンの舞台裏”とも言える日常を綴っていきます。

ORACLE SQL テクニック

実行計画のススメ

まず、大前提としてSQLを修正する際には書いたSQLを実行計画で確認することをお勧めする。

実際のSQLを投げるのと違い、直ぐに応答があるため動作チェックの意味でも投げることをお勧めする。

自分の書いたSQLがどのように動くかも含め確認するとよい。

また、実行計画ボタンの隣に自動トレースボタンがあるが、こちらは実行計画だけではなく

  • SQL再帰回数
  • 読み込みブロック数
  • ソート回数
  • 処理行数

等の内容も加えて表示される。(現状、権限がないので実行できなかった)

 

 

修正その1:EXISTSと相関サブクエリ

 

上記の画像のように本クエリとサブクエリ間で互いに参照をしているクエリを相関サブクエリと呼びます。

この相関サブクエリでは互いにデータを参照しあう関係上、データのアクセス数が膨大になりやすく、SQLの処理時間が

増大しやすくなります。

対応法は上記のように内部結合処理への書き換えを推奨します。

引用:EXISTSが速いという誤解

kkoudev.github.io

修正その2:ウィンドウ関数

SQLにて集合関数は重い処理にあたる。理由はその性質上、データの集計を行うため、基本全てのデータにアクセスする必要があるためである。

したがって、よくある履歴データ等の最新データを拾うために結合×集合関数(Max)等の処理を行う場合には

 駆動テーブルのデータ数 × 結合テーブル(Max対象テーブル)の全データ数

の処理が行われ、処理が重くなりがちである。

上記データに関するSQLの差分はSubversionのrevision:11372を参照のこと。

修正その3:IN句の失敗例

詳細は記載しないが、PHP等のプログラム側で条件項目を保持して、SQLを組み立てる場合がよくある。

その時に条件項目が膨れ上がり1000件を超えると下記のようになる。

また、上記エラーの件を除いても下記理由から避け、プレースホルダに変更すべきである。

  • 単純にIN句はパフォーマンスが低い
  • PHP側で展開するとSQLの文が異なり、キャッシュにヒットしない
  • SQLインジェクション対策 等

番外:複合インデックスについて

複合インデックスは複数のカラムを指定し、単一のインデックスよりより詳細な絞込み対象を作ることで高速化を行うインデックスとなる。

複合インデックスのカラム指定は単純に記載しがちだが、記載順序が重要となる。下記2項を特に注意すること。

  • 複合インデックスは先頭のカラム要素のみ使用といった具合に、部分的に使用可能なため頻度が高いものは先に記載するとよい
  • 逆に先頭のカラムを使用しない場合は、その後のカラムがインデックスにあっても使用されることがないため注意する
  • 範囲検索を行うと後続のインデックスは使用されなくなるため注意(Date型等は特に後ろに回した方がよい)

詳細なデータ検索範囲は下記ページに例がある

http://itdoc.hitachi.co.jp/manuals/3000/30003F5500/EEXD0043.HTM