043.【FIRST_ROWS与ALL_ROWS效率比较】

【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优化

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7901922/viewspace-1060026/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7901922/viewspace-1060026/