本格SEもすなる、技術ブログといふものを、駄目SEもしてみむとて、するなり

SQLの分析関数はちゃんと覚えよう!

本日はJPOUG Advent Calendar 2012のエントリを書きます。
他の日は錚々たる方々が担当しているので、今日は大したことがない内容です。
、、、とハードルを下げておく。
実は、分析関数を3年位前まで存在を知らなかったんですね。SQLの入門書に記載されていなかったり、分析関数/ウィンドウ関数という名称で何だか難しいイメージがあり、全然勉強をしてなかった、、、大体最初に触ったDB(Oracle 7.3.4)には未だ搭載されていなかったし。

そんな訳で、存在を知る3年以上前までは、とんでもなく大変なSQLを書いたこともありました。
そんなSQLと分析関数で書き直したSQLを比較してみます。

検証環境

マシン:Amazon Web Service RDS(db.m1.small)
OracleOracle Database Standard Edition One 11.2.0.2.v5
サンプルデータ:JdbcRunner テストキット Tiny TPC-Cの tpcc_load.js、tpcc.js で作成。
@さん有難うございます。

昔はこう書いてた

各顧客の最新の注文で、一番高い金額を取得してみます。
昔は、こんな感じで書いていました。

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の実行計画 その2
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の実行計画 その2
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の方が3倍程度低Cost!!
実行計画から類推するとFIRST関数の場合は、FROM一度全データを全て展開してから集約するので、処理対象行数が最後まで少なくならないようですね。

まとめ

まあ、何でも使える関数を使うだけでなく、ちゃんと実行計画は確認しろって事ですね。
必ずしも、無暗に使える関数でSQLを書くなと。

明日のJPOUG Advent Calendarは、@さんです。よろしくお願いします。


何とか JPOUG Advent Calendar の記事が終わったけど、ブログ書き続けていけるかな?