【FIRST_ROWS与ALL_ROWS效率比较】
--1.返回全部数据COST比较
SQL> set autot trace exp
SQL> select /*+all_rows*/*
2 from scott.t_group1 t
3 left join scott.t_zt z on(z.id=t.id);
Execution Plan
----------------------------------------------------------
Plan hash value: 1625069349
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 4326K| 86M| | 11649 (4)|
00:02:20 |
|* 1 | HASH JOIN RIGHT OUTER| | 4326K| 86M| 4480K| 11649 (4)|
00:02:20 |
| 2 | TABLE ACCESS FULL | T_ZT | 254K| 1492K| | 92 (8)|
00:00:02 |
| 3 | TABLE ACCESS FULL | T_GROUP1 | 4326K| 61M| | 5682 (5)|
00:01:09 |
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("Z"."ID"(+)="T"."ID")
SQL> select /*+first_rows no_index(t)*/*
2 from scott.t_group1 t
3 left join scott.t_zt z on(z.id=t.id);
Execution Plan
----------------------------------------------------------
Plan hash value: 1214657265
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 4326K| 86M| 4338K (1)| 1
4:27:37 |
| 1 | NESTED LOOPS OUTER | | 4326K| 86M| 4338K (1)| 1
4:27:37 |
| 2 | TABLE ACCESS FULL | T_GROUP1 | 4326K| 61M| 5682 (5)| 0
0:01:09 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_ZT | 1 | 6 | 1 (0)| 0
0:00:01 |
|* 4 | INDEX UNIQUE SCAN | PRY_T_ZT | 1 | | 0 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("Z"."ID"(+)="T"."ID")
2.在PL/SQL DEVELOPER中测试返回10条记录的用时
--用时0.2秒
select /*+all_rows*/*
from scott.t_group1 t
left join scott.t_zt z on(z.id=t.id);
--用时0.04秒
select /*+first_rows*/*
from scott.t_group1 t
left join scott.t_zt z on(z.id=t.id);
【小结】
1.FIRST_ROWS用的是NESTED LOOPS(COST为4338K),ALL_ROWS用的是HASH JION(COST为11649)
2.FIRST_ROWS展示前10条用时0.04秒,ALL_ROWS用时0.2秒
3.报表展示页面的SQL可以尝试使用FIRST_rows优化
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7901922/viewspace-1060026/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7901922/viewspace-1060026/