ここで一つの疑問が生じます。Full Outer Joinを使いながらPagination QueryのためにROWNUM条件を使用すればオラクルはどんな実行計画を作るでしょうか。Native Full Outer JoinはHash Joinでだけ支援されるがPagination QueryはNested Loops Joinともっと似合います。次に簡単なテスト結果があります。
まず次のように二つのテーブルを作ります。
create table t1 as
select level as c1, rpad('x',100) as c2
from dual
connect by level <= 100000
;
create table t2 as
select level as c1, rpad('x',100) as c2
from dual
connect by level <= 100000
;
alter table t1 modify c1 not null;
alter table t2 modify c2 not null;
create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false);
exec dbms_stats.gather_table_stats(user, 't2', no_invalidate=>false);
Full Outer Joinではない一般Outer Joinの場合はNested Loops Joinが選択されPaginationの目的によく合います。性能も優れるはずです。
explain plan for
select * from (
select rownum as r, x.* from (
select *
from t1 left join t2 on t1.c1 = t2.c1
) x where rownum <= 10
) where r >= 1
;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1430 | 24 (0)|
|* 1 | VIEW | | 10 | 1430 | 24 (0)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | NESTED LOOPS OUTER | | 11 | 2310 | 24 (0)|
| 4 | TABLE ACCESS FULL | T1 | 11 | 1155 | 2 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 105 | 2 (0)|
|* 6 | INDEX RANGE SCAN | T2_N1 | 1 | | 1 (0)|
-----------------------------------------------------------------------------
でもFull Outer Joinを使えばNative Full Outer Hash Joinが使用されます。Paginationの目的にはぜんぜん合いません。
explain plan for
select * from (
select rownum as r, x.* from (
select *
from t1 full join t2 on t1.c1 = t2.c1
) x where rownum <= 10
) where r >= 1
;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1430 | | 2302 (1)
|* 1 | VIEW | | 10 | 1430 | | 2302 (1)
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | VW_FOJ_0 | 100K| 12M| | 2302 (1)
|* 4 | HASH JOIN FULL OUTER| | 100K| 20M| 11M| 2302 (1)
| 5 | TABLE ACCESS FULL | T1 | 100K| 10M| | 596 (1)
| 6 | TABLE ACCESS FULL | T2 | 100K| 10M| | 596 (1)
--------------------------------------------------------------------------------
Native Full Outer Joinは次のパラメータで制御されます。
UKJA@ukja1106> @para outer_join
NAME VALUE IS_DEFAUL SES_MODIFI
------------------------------ -------------------- --------- ----------
SYS_MODIFI
----------
DESCRIPTION
------------------------------------------------------------------------
_optimizer_native_full_outer_j FORCE TRUE true
oin
immediate
execute full outer join using native implementaion
このパラメータをオフさせたらNative Full Outer JoinではなくてUNION ALLを利用したFull Outer Join(すなわち10gの方式)に変わるだけ、Paginationに適当なNested Loops Joinに変わることではないんです。
explain plan for
select * from (
select rownum as r, x.* from (
select /*+ opt_param('_optimizer_native_full_outer_join', 'off') */ *
from t1 full join t2 on t1.c1 = t2.c1
) x where rownum <= 10
) where r >= 1
;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1430 | | 3620 (1)|
|* 1 | VIEW | | 10 | 1430 | | 3620 (1)|
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 199K| 24M| | 3620 (1)|
| 4 | UNION-ALL | | | | | |
|* 5 | HASH JOIN OUTER | | 100K| 20M| 11M| 2302 (1)|
| 6 | TABLE ACCESS FULL | T1 | 100K| 10M| | 596 (1)|
| 7 | TABLE ACCESS FULL | T2 | 100K| 10M| | 596 (1)|
|* 8 | HASH JOIN RIGHT ANTI | | 99999 | 10M| 1664K| 1318 (1)|
| 9 | INDEX FAST FULL SCAN| T1_N1 | 100K| 488K| | 86 (2)|
| 10 | TABLE ACCESS FULL | T2 | 100K| 10M| | 596 (1)|
--------------------------------------------------------------------------------
無理にFIRST_ROWS(10)ヒントを与えるとようやく欲した形態の実行計画が現れます。
explain plan for
select * from (
select rownum as r, x.* from (
select /*+ first_rows(10)
opt_param('_optimizer_native_full_outer_join', 'off') */ *
from t1 full join t2 on t1.c1 = t2.c1
) x where rownum <= 10
) where r >= 1
;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1430 | 37 (0)|
|* 1 | VIEW | | 10 | 1430 | 37 (0)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 22 | 2860 | 37 (0)|
| 4 | UNION-ALL | | | | |
| 5 | NESTED LOOPS OUTER | | 11 | 2310 | 24 (0)|
| 6 | TABLE ACCESS FULL | T1 | 11 | 1155 | 2 (0)|
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 105 | 2 (0)|
|* 8 | INDEX RANGE SCAN | T2_N1 | 1 | | 1 (0)|
| 9 | NESTED LOOPS ANTI | | 11 | 1210 | 13 (0)|
| 10 | TABLE ACCESS FULL | T2 | 11 | 1155 | 2 (0)|
|* 11 | INDEX RANGE SCAN | T1_N1 | 1 | 5 | 1 (0)|
-------------------------------------------------------------------------------
これはちょっと失望名結果です。なぜならROWNUM条件を使えばオラクルは内部的にFIRST ROWS技法を真似するオプションを持っているからです。でも願っただけきちんと動作しませんでした。
UKJA@ukja1106> @para rownum_pred
NAME VALUE IS_DEFAUL SES_MODIFI
------------------------------ -------------------- --------- ----------
SYS_MODIFI
----------
DESCRIPTION
------------------------------------------------------------------------
_optimizer_rownum_pred_based_f TRUE TRUE true
kr
immediate
enable the use of first K rows due to rownum predicate
次のようにヒントを使用してNative Full Outer Hash Joinを制御できます。
UKJA@ukja1106> @hint native
NAME INVERSE VERSION
------------------------------ ------------------------------ ----------
NATIVE_FULL_OUTER_JOIN NO_NATIVE_FULL_OUTER_JOIN 10.2.0.3
NO_NATIVE_FULL_OUTER_JOIN NATIVE_FULL_OUTER_JOIN 10.2.0.3
次のように使用します。
explain plan for
select * from (
select rownum as r, x.* from (
select /*+ first_rows(10) no_native_full_outer_join */ *
from t1 full join t2 on t1.c1 = t2.c1
) x where rownum <= 10
) where r >= 1
;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1430 | 37 (0)|
|* 1 | VIEW | | 10 | 1430 | 37 (0)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 22 | 2860 | 37 (0)|
| 4 | UNION-ALL | | | | |
| 5 | NESTED LOOPS OUTER | | 11 | 2310 | 24 (0)|
| 6 | TABLE ACCESS FULL | T1 | 11 | 1155 | 2 (0)|
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 105 | 2 (0)|
|* 8 | INDEX RANGE SCAN | T2_N1 | 1 | | 1 (0)|
| 9 | NESTED LOOPS ANTI | | 11 | 1210 | 13 (0)|
| 10 | TABLE ACCESS FULL | T2 | 11 | 1155 | 2 (0)|
|* 11 | INDEX RANGE SCAN | T1_N1 | 1 | 5 | 1 (0)|
-------------------------------------------------------------------------------
伝統的なUSE_NLヒントも当然動作します。
explain plan for
select * from (
select rownum as r, x.* from (
select /*+ use_nl(t1) use_nl(t2) */ *
from t1 full join t2 on t1.c1 = t2.c1
) x where rownum <= 10
) where r >= 1
;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1430 | |
|* 1 | VIEW | | 10 | 1430 | |
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 199K| 24M| |
| 4 | UNION-ALL | | | | |
| 5 | NESTED LOOPS OUTER | | 100K| 20M| |
| 6 | TABLE ACCESS FULL | T1 | 100K| 10M| |
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 105 | |
|* 8 | INDEX RANGE SCAN | T2_N1 | 1 | | |
|* 9 | HASH JOIN RIGHT ANTI | | 99999 | 10M| 1664K|
| 10 | INDEX FAST FULL SCAN | T1_N1 | 100K| 488K| |
| 11 | TABLE ACCESS FULL | T2 | 100K| 10M| |
--------------------------------------------------------------------------
Nested Loops Joinが選択された場合とそうではない場合はPagination Queryで相当な性能の違いが存在します。
select /*+ gather_plan_statistics */ * from (
select rownum as r, x.* from (
select *
from t1 full join t2 on t1.c1 = t2.c1
) x where rownum <= 10
) where r >= 1
;
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Reads |
--------------------------------------------------------------------------------
|* 1 | VIEW | | 1 | 10 | 1547 | 682|
|* 2 | COUNT STOPKEY | | 1 | 10 | 1547 | 682|
| 3 | VIEW | VW_FOJ_0 | 1 | 10 | 1547 | 682|
|* 4 | HASH JOIN FULL OUTER| | 1 | 10 | 1547 | 682|
| 5 | TABLE ACCESS FULL | T1 | 1 | 100K| 1542 | 0|
| 6 | TABLE ACCESS FULL | T2 | 1 | 16 | 5 | 0|
--------------------------------------------------------------------------------
select /*+ gather_plan_statistics */ * from (
select rownum as r, x.* from (
select /*+ first_rows(10)
opt_param('_optimizer_native_full_outer_join', 'off') */ *
from t1 full join t2 on t1.c1 = t2.c1
) x where rownum <= 10
) where r >= 1
;
------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------
|* 1 | VIEW | | 1 | 10 | 16 |
|* 2 | COUNT STOPKEY | | 1 | 10 | 16 |
| 3 | VIEW | | 1 | 10 | 16 |
| 4 | UNION-ALL | | 1 | 10 | 16 |
| 5 | NESTED LOOPS OUTER | | 1 | 10 | 16 |
| 6 | TABLE ACCESS FULL | T1 | 1 | 10 | 5 |
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 10 | 11 |
|* 8 | INDEX RANGE SCAN | T2_N1 | 10 | 10 | 9 |
| 9 | NESTED LOOPS ANTI | | 0 | 0 | 0 |
| 10 | TABLE ACCESS FULL | T2 | 0 | 0 | 0 |
|* 11 | INDEX RANGE SCAN | T1_N1 | 0 | 0 | 0 |
------------------------------------------------------------------------------
ここでもう一つの疑問が持てます。多くのPagination QueryがORDER BY句を使用します。万一このORDER BYをインデックスで処理できないとしたらNested Loops Joinのメリットは消えてしまいます。こんな場合はNative Full Outer Hash Joinが本然のメリットを発揮することができるはずです。
私は個人的にFIRST_ROWS類のヒントやモードはなくなるべきだと思いますが、なお必要がありますね。:)
オプティマイザがだんだん賢くなっていますがたまにはとても簡単なクエリでも手動制御が必要な場合があります。われらが相変わらずヒントを身に付けなければならない理由となります。
Posts
No comments:
Post a Comment