SQLの分析関数はちゃんと覚えよう！
本日はJPOUG Advent Calendar 2012のエントリを書きます。
他の日は錚々たる方々が担当しているので、今日は大したことがない内容です。
、、、とハードルを下げておく。
実は、分析関数を3年位前まで存在を知らなかったんですね。SQLの入門書に記載されていなかったり、分析関数/ウィンドウ関数という名称で何だか難しいイメージがあり、全然勉強をしてなかった、、、大体最初に触ったDB(Oracle 7.3.4)には未だ搭載されていなかったし。
そんな訳で、存在を知る3年以上前までは、とんでもなく大変なSQLを書いたこともありました。
そんなSQLと分析関数で書き直したSQLを比較してみます。
検証環境
マシン：Amazon Web Service RDS(db.m1.small)
Oracle：Oracle Database Standard Edition One 11.2.0.2.v5
サンプルデータ：JdbcRunner テストキット Tiny TPC-Cの tpcc_load.js、tpcc.js で作成。
@sh2ndさん有難うございます。
昔はこう書いてた
各顧客の最新の注文で、一番高い金額を取得してみます。
昔は、こんな感じで書いていました。
SELECT o.o_id, o.o_d_id, o.o_w_id, MAX(o.o_id) as latest_order_date, (SELECT MAX(i.i_price) FROM item i, order_line ol2 WHERE i.i_id = ol2.ol_i_id AND ol2.ol_o_id = o.o_id AND ol2.ol_d_id = o.o_d_id AND ol2.ol_w_id = o.o_w_id) as highest_price FROM orders o, order_line ol WHERE o.o_c_id = '1124' AND o.o_d_id = '2' AND o.o_w_id = '13' AND o.o_id = ol.ol_o_id AND o.o_d_id = ol.ol_d_id AND o.o_w_id = ol.ol_w_id GROUP BY o.o_id, o.o_d_id, o.o_w_id ORDER BY o.o_id, o.o_d_id, o.o_w_id
う～ん、、、 カラムの中にSELECT文があるのが、かなり嫌な感じがします。
書き直してみる
カラムの中にあるSELECT文が嫌なので、FIRST関数を使ってみます。
まず、itemテーブルを普通にFROM句に持ってきて、FIRST関数のORDER BY句でオーダーID順でソートします。
SELECT o.o_id, o.o_d_id, o.o_w_id, MAX(o.o_id) as latest_order_date, MAX(i.i_price)KEEP(DENSE_RANK FIRST ORDER BY o.o_id DESC) as highest_price FROM orders o, order_line ol, item i WHERE o.o_c_id = '1124' AND o.o_d_id = '2' AND o.o_w_id = '13' AND o.o_id = ol.ol_o_id AND o.o_d_id = ol.ol_d_id AND o.o_w_id = ol.ol_w_id AND ol.ol_i_id = i.i_id GROUP BY o.o_id, o.o_d_id, o.o_w_id ORDER BY o.o_id, o.o_d_id, o.o_w_id
こんな感じでしょうか。余計なSELECT文が無くなってスッキリしました。
実行コストは？
SELECT文はスッキリしましたが、実行コストはどの程度改善させるのでしょう？
比較します。
昔のSQLの実行計画
とりあえず、autotrace を設定します。
SQL> set autotrace traceonly explain
昔のSQLの実行計画を確認します。
Execution Plan
----------------------------------------------------------
Plan hash value: 4157488715
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 7 (15)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 10 | 240 | 14 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | ORDER_LINE | 10 | 150 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | ORDER_LINE_PK | 10 | | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | ITEM_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 9 | 1 (0)| 00:00:01 |
| 8 | SORT GROUP BY | | 1 | 33 | 7 (15)| 00:00:01 |
| 9 | NESTED LOOPS | | 10 | 330 | 7 (15)| 00:00:01 |
| 10 | VIEW | VW_GBC_5 | 1 | 23 | 5 (20)| 00:00:01 |
| 11 | HASH GROUP BY | | 1 | 14 | 5 (20)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 14 | 4 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | ORDERS_IX1 | 1 | | 3 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | ORDER_LINE_PK | 10 | 100 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OL2"."OL_W_ID"=:B1 AND "OL2"."OL_D_ID"=:B2 AND "OL2"."OL_O_ID"=:B3)
6 - access("I"."I_ID"="OL2"."OL_I_ID")
13 - access("O"."O_W_ID"=13 AND "O"."O_D_ID"=2 AND "O"."O_C_ID"=1124)
14 - access("ITEM_1"="OL"."OL_W_ID" AND "ITEM_2"="OL"."OL_D_ID" AND
"ITEM_3"="OL"."OL_O_ID")あんまり重いデータではないので、こんなもんでしょうか？
書き直したSQLの実行計画
こちらも実行計画を確認します。
Execution Plan
----------------------------------------------------------
Plan hash value: 1892011866
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 17 (6)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 38 | 17 (6)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 9 | 342 | 16 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 9 | 261 | 7 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 14 | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | ORDERS_IX1 | 1 | | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| ORDER_LINE | 9 | 135 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | ORDER_LINE_PK | 9 | | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | ITEM_PK | 1 | | 0 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 9 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("O"."O_W_ID"=13 AND "O"."O_D_ID"=2 AND "O"."O_C_ID"=1124)
8 - access("OL"."OL_W_ID"=13 AND "OL"."OL_D_ID"=2 AND "O"."O_ID"="OL"."OL_O_ID")
9 - access("OL"."OL_I_ID"="I"."I_ID")書き直したSQLの方が%CPUは低いけどCostは大きいですね。各々のコストが小さいので余り良く分からない、、、
もう少し重いSQLで比較する
WHERE句から、以下の顧客を特定する条件を抜かして比較してみます。
o.o_c_id = '1124' AND o.o_d_id = '2' AND o.o_w_id = '13'
昔のSQLの実行計画 その２
Execution Plan
----------------------------------------------------------
Plan hash value: 414388587
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 240K| 4687K| | 14470 (2)| 00:02:54 |
| 1 | SORT AGGREGATE | | 1 | 24 | | | |
| 2 | NESTED LOOPS | | | | | | |
| 3 | NESTED LOOPS | | 10 | 240 | | 14 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| ORDER_LINE | 10 | 150 | | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | ORDER_LINE_PK | 10 | | | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | ITEM_PK | 1 | | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 9 | | 1 (0)| 00:00:01 |
| 8 | SORT GROUP BY | | 240K| 4687K| 7544K| 14470 (2)| 00:02:54 |
|* 9 | HASH JOIN | | 240K| 4687K| 5160K| 13008 (2)| 00:02:37 |
| 10 | VIEW | VW_GBF_5 | 240K| 2343K| | 11887 (2)| 00:02:23 |
| 11 | HASH GROUP BY | | 240K| 2343K| 91M| 11887 (2)| 00:02:23 |
| 12 | INDEX FAST FULL SCAN | ORDER_LINE_PK | 4799K| 45M| | 4399 (1)| 00:00:53 |
| 13 | INDEX FAST FULL SCAN | ORDERS_PK | 480K| 4687K| | 365 (1)| 00:00:05 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OL2"."OL_W_ID"=:B1 AND "OL2"."OL_D_ID"=:B2 AND "OL2"."OL_O_ID"=:B3)
6 - access("I"."I_ID"="OL2"."OL_I_ID")
9 - access("O"."O_ID"="ITEM_3" AND "O"."O_D_ID"="ITEM_2" AND "O"."O_W_ID"="ITEM_1")
書き直したSQLの実行計画 その２
Execution Plan
----------------------------------------------------------
Plan hash value: 1398604944
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 240K| 7968K| | 43683 (1)| 00:08:45 |
| 1 | SORT GROUP BY | | 240K| 7968K| 201M| 43683 (1)| 00:08:45 |
|* 2 | HASH JOIN | | 4760K| 154M| 10M| 26928 (1)| 00:05:24 |
| 3 | INDEX FAST FULL SCAN| ORDERS_PK | 480K| 4687K| | 365 (1)| 00:00:05 |
|* 4 | HASH JOIN | | 4760K| 108M| 2056K| 17923 (1)| 00:03:36 |
| 5 | TABLE ACCESS FULL | ITEM | 100K| 878K| | 308 (1)| 00:00:04 |
| 6 | TABLE ACCESS FULL | ORDER_LINE | 4799K| 68M| | 11352 (1)| 00:02:17 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."O_ID"="OL"."OL_O_ID" AND "O"."O_D_ID"="OL"."OL_D_ID" AND
"O"."O_W_ID"="OL"."OL_W_ID")
4 - access("OL"."OL_I_ID"="I"."I_ID")ん？
改めて比較すると不思議なことに、不細工な昔のSQLの方が３倍程度低Cost!!
実行計画から類推するとFIRST関数の場合は、FROM一度全データを全て展開してから集約するので、処理対象行数が最後まで少なくならないようですね。
