Oracle SQLアンチパターンの問題集

OracleのSQLのアンチパターンの問題集2

from句のテーブル数を減らす

山岸賢治[著]

2010/08/20 14:00

ポスト

ダウンロード SourceCode (1.1 KB)

修正後のSQLと解説

exists述語を使用

修正後のSQL1（exists述語を使用）

select ID,Val
  from SelfRef a
 where exists(select 1 from SelfRef b
               where b.ID = a.ID
              having min(case when b.Val >= 20
                              then 1
                              else 0 end)=1)
order by ID,Val;

　exists述語を使い、大本のselect文のfrom句にあるテーブルを2個から1個に減らすことができました。大本のselect文のfrom句にあるテーブルが1個になったので、表別名による列修飾を行ってる箇所を減らすことができました。

in述語を使用

修正後のSQL2（in述語を使用）

select ID,Val
  from SelfRef a
 where ID in(select ID
               from SelfRef
             group by ID
             having min(case when Val >= 20
                             then 1
                             else 0 end)=1)
order by ID,Val;

　別解としてin述語を使用したのが上記のSQLです。exists述語では、相関なサブクエリを使ってましたが、in述語では非相関なサブクエリを使ってますので、in述語の中のselect文を単独で実行可能です。

　解析しやすさも可読性の一部だと考えれば、SQLを解析する際にSQLの一部をコピーしてそのまま実行可能なin述語は、exists述語よりも可読性が高いかもしれません。

　さらに、列名はスコープの狭い方が優先されますので、表別名による列修飾が完全に不要になりました。

分析関数を使用

修正後のSQL3（分析関数を使用）

select ID,Val
from (select ID,Val,
      min(case when Val >= 20
               then 1 else 0 end)
      over(partition by ID) as willOut
      from SelfRef)
where willOut=1
order by ID,Val;

　IDごとでValが全て20以上であるかを、分析関数のmin関数と検索case式を組み合わせて判断するようにしたのが、上記のSQLです。これが最も可読性が高いSQLだと思います。

　上記のSQLのイメージは下記です。分析関数のmin関数に対応する黄緑線と、partition by IDに対応する赤線を引いてます。

SQLのイメージ