« OTN Forum - すげ〜デザイン変わってる :) | トップページ | Oracle Database 12c R1 : コンテナデータベースとプラガブルデータベースを歩きやすくしてみる »

2013年7月 8日 (月)

rownum使って満足しちゃってると.....おまけのおまけ FETCH FIRST N ROWS ONLY編

20130628_233812


Oracle Database 12c R1 12.1.0.1.0 がリリースされたので、自分用のメモとしても使うネタから :)


実行環境は Oracle Database 12c R1 12.1.0.1.0 EE/Oracle Linux 6.4/VirtualBox for OS X 11g R2と同じデータをインポートし統計情報を再取得しています。


まず、rownum から!

おおおおお〜、 まあ想定通りではあるのですが、11gとは違う点が一つ。

TABLE ACCESS BY INDEX ROWID BATCHEDと見慣れないオペレーションが...興味深いですね。これ :)

別途調べるTODO :)

23:14:41 SCOTT@pdborcl> r
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

1000行が選択されました。

経過: 00:00:00.13

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

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 2952K| 4813 (1)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 2002 | 5910K| 4813 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 4004 | 7894K| 2810 (1)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TC | 4004 | 3953K| 585 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PK_TC | | | 14 (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
1059870 bytes sent via SQL*Net to client
1270 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed


次に、Oracle Database 12c R1 12.1.0.1.0 から実装された FETCH FIRST N ROWS ONLYとの比較

FETCH FIRST N ROWS ONLYを利用した場合TCを全表走査してしまったので、rownum利用時と同じオブジェクト参照させるためヒントでPK_TC索引を利用するようチューニングしてあります
ただ..なぜか、TABLE ACCESS BY INDEX ROWID BATCHEDではなくTABLE ACCESS BY INDEX ROWIDとなっています。


さらに不思議なことに、COSTが異常に跳ね上がっています。Rowsの見積もり件数で大きな差があることが影響しているように見えるんだが、なんでこんなに違うんだろう!
いろいろ試してみると、不思議なことがおきそうな気配w


Buffer Getsはほぼ同じなので、全表走査になりやすい?場合、索引が利用できそうなら使わせたほうがいいですな。多分。

23:12:22 SCOTT@pdborcl> r
1 select /*+ leading(tc) use_nl(tc tb ta) index(tc pk_tc) */ *
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* fetch first 1000 rows only

1000行が選択されました。

経過: 00:00:00.13

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

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 300K| 456M| 1141K (1)| 00:00:45 |
|* 1 | VIEW | | 300K| 456M| 1141K (1)| 00:00:45 |
|* 2 | WINDOW NOSORT STOPKEY | | 300K| 864M| 1141K (1)| 00:00:45 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 300K| 864M| 1141K (1)| 00:00:45 |
| 5 | NESTED LOOPS | | 600K| 1155M| 841K (1)| 00:00:33 |
| 6 | TABLE ACCESS BY INDEX ROWID| TC | 1200K| 1156M| 174K (1)| 00:00:07 |
|* 7 | INDEX RANGE SCAN | PK_TC | 1200K| | 3278 (1)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| TB | 1 | 1008 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_TB | 1 | | 0 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_TA | 1 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | TA | 1 | 1004 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

1 - filter("from$_subquery$_006"."rowlimit_$$_rownumber"<=1000)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=1000)
7 - access("TC"."VERSION">=1 AND "TC"."VERSION"<=10)
9 - access("TC"."VERSION"="TB"."VERSION" AND "TC"."BRANCH#"="TB"."BRANCH#")
filter("TB"."VERSION"<=10 AND "TB"."VERSION">=1)
10 - access("TB"."VERSION"="TA"."VERSION")
filter("TA"."VERSION">=1 AND "TA"."VERSION"<=10)


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

FETCH FISRSTを利用すると、rownumとは異なり、内部でROW_NUMBER() OVER( OVER BY NULL) <= N
のような変換が発生するんですね!!!! 


rownumの弱点はどうなったか確認してみます。

12c でも rownum は rownumですね! という確認から。

23:37:32 SCOTT@pdborcl> r
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 11 and 20
12* and rownum <= 1000

レコードが選択されませんでした。

経過: 00:00:08.21

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

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 2952K| 37940 (1)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1101 | 3250K| 37940 (1)| 00:00:02 |
| 4 | NESTED LOOPS | | 22019 | 42M| 26928 (1)| 00:00:02 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TC | 38230 | 36M| 5683 (1)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PK_TC | | | 109 (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">=11 AND "TC"."VERSION"<=20)
8 - access("TC"."VERSION"="TB"."VERSION" AND "TC"."BRANCH#"="TB"."BRANCH#")
filter("TB"."VERSION">=11 AND "TB"."VERSION"<=20)
9 - access("TB"."VERSION"="TA"."VERSION")
filter("TA"."VERSION">=11 AND "TA"."VERSION"<=20)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
1174942 consistent gets
176197 physical reads
0 redo size
913 bytes sent via SQL*Net to client
533 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


FETCH FIRST N ROWS ONLYでも弱点は同じです!

23:36:02 SCOTT@pdborcl> r
1 select /*+ leading(tc) use_nl(tc tb ta) index(tc pk_tc) */ *
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 11 and 20
12* fetch first 1000 rows only

レコードが選択されませんでした。

経過: 00:00:07.36

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

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29999 | 45M| 1141K (1)| 00:00:45 |
|* 1 | VIEW | | 29999 | 45M| 1141K (1)| 00:00:45 |
|* 2 | WINDOW NOSORT STOPKEY | | 29999 | 86M| 1141K (1)| 00:00:45 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 29999 | 86M| 1141K (1)| 00:00:45 |
| 5 | NESTED LOOPS | | 599K| 1155M| 841K (1)| 00:00:33 |
| 6 | TABLE ACCESS BY INDEX ROWID| TC | 1200K| 1156M| 174K (1)| 00:00:07 |
|* 7 | INDEX RANGE SCAN | PK_TC | 1200K| | 3278 (1)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| TB | 1 | 1008 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_TB | 1 | | 0 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_TA | 1 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | TA | 1 | 1004 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

1 - filter("from$_subquery$_006"."rowlimit_$$_rownumber"<=1000)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=1000)
7 - access("TC"."VERSION">=11 AND "TC"."VERSION"<=20)
9 - access("TC"."VERSION"="TB"."VERSION" AND "TC"."BRANCH#"="TB"."BRANCH#")
filter("TB"."VERSION">=11 AND "TB"."VERSION"<=20)
10 - access("TB"."VERSION"="TA"."VERSION")
filter("TA"."VERSION">=11 AND "TA"."VERSION"<=20)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
1174942 consistent gets
175116 physical reads
0 redo size
913 bytes sent via SQL*Net to client
533 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


最後に、ハッシュ結合にした場合のrownum vs fetch first n rows onlyの比較

NL結合にすべきか、ハッシュ結合にすべきかどうかは、悩むんですよ。状況に合わせて使い分ける必要があるのは、11gでも12cでも同じだと思います。理由は前回のエントリを参照のこと。

rownumでハッシュ結合にした場合、11g R2では黙っていても索引を利用していたのですが、12c R1では全表走査になる傾向が見られます。
11gの実行計画と同じオブジェクトを参照するようにヒントでチューニングしてありますが、やはりここでもTABLE ACCESS BY INDEX ROWID BATCHEDが行われていますね。

実に興味深い。

23:46:06 SCOTT@pdborcl> r
1 select /*+ leading(tc) use_hash(tc tb ta) index(ta pk_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

1000行が選択されました。

経過: 00:00:34.63

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

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 4923K| | 199K (1)| 00:00:08 |
|* 1 | COUNT STOPKEY | | | | | | |
|* 2 | HASH JOIN | | 2002 | 9857K| | 199K (1)| 00:00:08 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | TA | 10 | 10040 | | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_TA | 10 | | | 1 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 4004 | 7894K| 1170M| 199K (1)| 00:00:08 |
|* 6 | TABLE ACCESS FULL | TC | 1200K| 1156M| | 139K (1)| 00:00:06 |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| TB | 10001 | 9844K| | 1486 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | PK_TB | 10000 | | | 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)


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

FETCH FIRST N ROWS ONLYを利用した場合

00:20:36 SCOTT@pdborcl> r
1 select /*+ leading(tc) use_hash(tc tb ta) index(ta pk_ta) index(tb pk_tb) */ *
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* fetch first 1000 rows only

1000行が選択されました。

経過: 00:00:32.69

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

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 300K| 456M| | 199K (1)| 00:00:08 |
|* 1 | VIEW | | 300K| 456M| | 199K (1)| 00:00:08 |
|* 2 | WINDOW NOSORT STOPKEY | | 300K| 864M| | 199K (1)| 00:00:08 |
|* 3 | HASH JOIN | | 300K| 864M| | 199K (1)| 00:00:08 |
| 4 | TABLE ACCESS BY INDEX ROWID | TA | 10 | 10040 | | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | PK_TA | 10 | | | 1 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 600K| 1155M| 1170M| 199K (1)| 00:00:08 |
|* 7 | TABLE ACCESS FULL | TC | 1200K| 1156M| | 139K (1)| 00:00:06 |
| 8 | TABLE ACCESS BY INDEX ROWID| TB | 10001 | 9844K| | 1486 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | PK_TB | 10000 | | | 26 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

1 - filter("from$_subquery$_006"."rowlimit_$$_rownumber"<=1000)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=1000)
3 - access("TB"."VERSION"="TA"."VERSION")
5 - access("TA"."VERSION">=1 AND "TA"."VERSION"<=10)
6 - access("TC"."VERSION"="TB"."VERSION" AND "TC"."BRANCH#"="TB"."BRANCH#")
7 - filter("TC"."VERSION"<=10 AND "TC"."VERSION">=1)
9 - access("TB"."VERSION">=1 AND "TB"."VERSION"<=10)


統計
----------------------------------------------------------
1456 recursive calls
0 db block gets
516181 consistent gets
514506 physical reads
0 redo size
1045990 bytes sent via SQL*Net to client
1270 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
75 sorts (memory)
0 sorts (disk)
1000 rows processed


Top N に関してはrownumでも fetch first N rows onlyでも弱点は弱点なのでrownumと同じ考え方でチューニングの方向を決めないと、ね。


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

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/57746765

この記事へのトラックバック一覧です: rownum使って満足しちゃってると.....おまけのおまけ FETCH FIRST N ROWS ONLY編 :

コメント

コメントを書く