同じ結果を返すSQLでも、
パフォーマンスに差が出たりすることは、ご存知かと思います。
今日はその一番わっかりやすい例を出したいと思います。
?
?
以下はT1というテーブルの構成列です。
このテーブルからIDが1のものについて、IDと合算したVALの値を取得する場合
2種類のSQLが出てきたりします。
1) SELECT ID, SUM(VAL) FROM T1 WHERE ID = 1 GROUP BY ID ;
2) SELECT ID, SUM(VAL) FROM T1 GROUP BY ID HAVING ID = 1;
この2つのSQL、期待した動作としてはどちらも正しいのですが、
2)については、駄目だしされます。
さて、なぜでしょう？
?
?
これはSQLトレースを取って見るとわかりやすいので、
上記2パターンでとったSQLトレースの内容を提示してみます。
#今回はわかりやすかったのでOracle10gでの実行計画です。
1)のトレースは以下のものになります。
================================================================================
SQL> select sum(val) from t1 where id = 1 group by id ;
実行計画
----------------------------------------------------------
Plan hash value: 2965048539
-----------------------------------------------------------------------------
| Id? | Operation??????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
-----------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???? |????? |? 3529K|??? 87M|? 3706?? (8)| 00:00:45 |
|?? 1 |? SORT GROUP BY NOSORT|????? |? 3529K|??? 87M|? 3706?? (8)| 00:00:45 |
|*? 2 |?? TABLE ACCESS FULL? | T1?? |? 3529K|??? 87M|? 3706?? (8)| 00:00:45 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - filter("ID"=1)
Note
-----
?? - dynamic sampling used for this statement
統計
----------------------------------------------------------
????????? 0? recursive calls
????????? 0? db block gets
????? 12723? consistent gets
????????? 0? physical reads
????????? 0? redo size
??????? 437? bytes sent via SQL*Net to client
??????? 384? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????????? 1? rows processed
================================================================================
2)のトレースは以下のものになります。
================================================================================
SQL> select sum(val) from t1 group by id having id = 1;
実行計画
----------------------------------------------------------
Plan hash value: 2966258745
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7058K| 175M| 4864 (30)| 00:00:59 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 7058K| 175M| 4864 (30)| 00:00:59 |
| 3 | TABLE ACCESS FULL| T1 | 7058K| 175M| 3659 (6)| 00:00:44 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- dynamic sampling used for this statement
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
12723 consistent gets
0 physical reads
0 redo size
437 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
================================================================================
?
?
この2つの違いはどこにあるでしょうか？
?
?
1)ではテーブルアクセス時にID＝1でフィルターがかかっているのがわかります。
# |*? 2 |?? TABLE ACCESS FULL? | T1?? |? 3529K|??? 87M|? 3706?? (8)| 00:00:45 |
#???? 2 - filter("ID"=1)
#? この2行でテーブルアクセス時にID=1でフィルターをかけているとわかります。
2)ではGROUP BYした後にID=1でフィルターがかかっているのがわかりますね。
#??? |*? 1 |? FILTER???????????? |????? |?????? |?????? |??????????? |????????? |
#??? 1 - filter("ID"=1)
#??? |?? 2 |?? HASH GROUP BY???? |????? |? 7058K|?? 175M|? 4864? (30)| 00:00:59 |
# この3行でグループ化の後にID=1でフィルターをかけているのがわかります。
?
?
?
・・・ということはですよ。
2)のSQLは全件読み取ってID毎にグループ化してから必要なデータを探し出していますね。
よって、どちらが早くて良いSQLかと問われたら・・・・1)のSQLとなりますね。
?
この様にして"SQLの動作の違い"が見え始めると、
実はオプティマイザといわれているものがどのように動いているのか、
どのようにSQLが実行されていくのかが見えるようになってきます。
?
今あなたの目の前にあるSQLクン、
実は自分の頭の中で想像している動作と違う動きをしているかも知れませんよ？
?
そんなときは実行計画をとってみて、
どういう処理しているの？って見てあげると楽しいかと思います♪
?