rownum使って満足しちゃってると.....おまけ Tweet

rownumを使っているチューニングしました！　（キリっ

と、しているのに以下の例ようにハッシュ結合が行われ残念な結果になっていることってありませんか？　（私はよく目にします (@@)

rownumの弱点を説明している実行計画をよーーーく見返してください。　ハッシュ結合にはしていません

なぜだか分かりますか？

11:14:44 SCOTT> l
  1  select *
  2  from
  3     tc join tb
  4     on
  5             tc.version = tb.version
  6             and tc.branch# = tb.branch#
  7     join ta
  8     on
  9             tb.version = ta.version
 10  where
 11     tc.version between 1 and 10
 12* and rownum <= 1000
11:14:44 SCOTT>  /

1000行が選択されました。

経過: 00:00:59.08

実行計画
----------------------------------------------------------
Plan hash value: 2217783216

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |  1000 |  4923K|       |   201K  (1)| 00:40:14 |
|*  1 |  COUNT STOPKEY                 |       |       |       |       |            |          |
|*  2 |   HASH JOIN                    |       |  1001 |  4928K|       |   201K  (1)| 00:40:14 |
|   3 |    TABLE ACCESS BY INDEX ROWID | TA    |    10 | 10040 |       |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN           | PK_TA |    10 |       |       |     1   (0)| 00:00:01 |
|*  5 |    HASH JOIN                   |       |  2002 |  3947K|  1161M|   201K  (1)| 00:40:14 |
|*  6 |     TABLE ACCESS FULL          | TC    |  1190K|  1148M|       |   141K  (1)| 00:28:20 |
|   7 |     TABLE ACCESS BY INDEX ROWID| TB    | 10001 |  9844K|       |  1456   (0)| 00:00:18 |
|*  8 |      INDEX RANGE SCAN          | PK_TB |       |       |       |    26   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=1000)
   2 - access("TB"."VERSION"="TA"."VERSION")
   4 - access("TA"."VERSION">=1 AND "TA"."VERSION"<=10)
   5 - access("TC"."VERSION"="TB"."VERSION" AND "TC"."BRANCH#"="TB"."BRANCH#")
   6 - filter("TC"."VERSION"<=10 AND "TC"."VERSION">=1)
   8 - access("TB"."VERSION">=1 AND "TB"."VERSION"<=10)


統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     514382  consistent gets
     514341  physical reads
          0  redo size
    1050353  bytes sent via SQL*Net to client
       1245  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

ハッシュ結合などは典型的なのですが、操作を始めるとその操作が終了するまで次の操作に移行しないんですよ。

上記の例では赤い部分のハッシュ結合が終了するまでCOUNT STOPKEY操作(Id=1の部分）つまり、rownumの効果が発揮できないわけです。（残念！

結合がありrownumを使って行数制限している場合には、”大抵の場合" Nested Loop結合にしたほうがいいんですよ！　OLTP系のようにレスポンスを重視する傾向が強い場合は... (弱点もありますが...

レスポンス重視かスループット重視か、オプティマイザが実行計画を組み立てる際に影響をパラメータがありますよね、　 レースカーのフロントウイングやりやウィングのように立てたり寝かしたりするようなパラメータが.... :)

話がそれてしまいましたが...w

11:13:28 SCOTT> l
  1  select /*+ leading(tc) use_nl(tc tb ta) */ *
  2  from
  3     tc join tb
  4     on
  5             tc.version = tb.version
  6             and tc.branch# = tb.branch#
  7     join ta
  8     on
  9             tb.version = ta.version
 10  where
 11     tc.version between 1 and 10
 12* and rownum <= 1000
11:13:29 SCOTT> /

1000行が選択されました。

経過: 00:00:00.29

実行計画
----------------------------------------------------------
Plan hash value: 2389411418

-----------------------------------------------------------------------------------------
| Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |  1000 |  2952K|  3798   (1)| 00:00:46 |
|*  1 |  COUNT STOPKEY                  |       |       |       |            |          |
|   2 |   NESTED LOOPS                  |       |       |       |            |          |
|   3 |    NESTED LOOPS                 |       |  1001 |  2955K|  3798   (1)| 00:00:46 |
|   4 |     NESTED LOOPS                |       |  2002 |  3947K|  2796   (1)| 00:00:34 |
|   5 |      TABLE ACCESS BY INDEX ROWID| TC    |  4004 |  3953K|   571   (0)| 00:00:07 |
|*  6 |       INDEX RANGE SCAN          | PK_TC |       |       |    13   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| TB    |     1 |  1008 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN         | PK_TB |     1 |       |     0   (0)| 00:00:01 |
|*  9 |     INDEX UNIQUE SCAN           | PK_TA |     1 |       |     0   (0)| 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | TA    |     1 |  1004 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=1000)
   6 - access("TC"."VERSION">=1 AND "TC"."VERSION"<=10)
   8 - access("TC"."VERSION"="TB"."VERSION" AND "TC"."BRANCH#"="TB"."BRANCH#")
       filter("TB"."VERSION"<=10 AND "TB"."VERSION">=1)
   9 - access("TB"."VERSION"="TA"."VERSION")
       filter("TA"."VERSION">=1 AND "TA"."VERSION"<=10)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2418  consistent gets
          0  physical reads
          0  redo size
    1050353  bytes sent via SQL*Net to client
       1245  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

＞結合がありrownumを使って行数制限している場合には、”大抵の場合" Nested Loop結合にしたほうがいいんですよ！　OLTP系の場合は特に. (弱点もありますが...

と書きましたが、”大抵の場合”と書いたのには理由があります。

あえてハッシュ結合にする場合もあるんです。　え！、え！、え〜〜〜〜〜っ！w

”常に遅いけど、Nested Loop結合+rownumにした場合の弱点”は絶対避けたいような場合です。

Nested Loop結合＋rownumにした場合の弱点の処理時間が、ハッシュ結合+rownumにした場合の処理時間を大幅に上回り、想定しているバッチ処理時間を超過してしまう可能性が高い場合がそれです。

状況に合わせて使い分けたいですよね。机上だけだと難しいのですが、実マシン、実データで検証を繰り返していれば対処できる問題だと思います。

---

・rownum使って満足しちゃってると..... #1
・rownum使って満足しちゃってると..... #2
・rownum使って満足しちゃってると..... #3
・rownum使って満足しちゃってると..... #4