SQL> select * from test,test1 where test.object_id=test1.object_id;
50351 rows selected.
Elapsed: 00:00:01.68
Execution Plan
----------------------------------------------------------
Plan hash value: 16165315
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50351 | 9145K| | 828 (2)| 00:00:10 |
|* 1 | HASH JOIN | | 50351 | 9145K| 5168K| 828 (2)| 00:00:10 |
| 2 | TABLE ACCESS FULL| TEST | 50351 | 4572K| | 161 (3)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TEST | 50351 | 4572K| | 161 (3)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TEST"."OBJECT_ID"="OBJECT_ID")
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
4722 consistent gets
0 physical reads
0 redo size
4244355 bytes sent via SQL*Net to client
37301 bytes received via SQL*Net from client
3358 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50351 rows processed
SQL> select /*+ first_rows(5) */ * from test,test1 where test.object_id=test1.object_id;
50351 rows selected.
Elapsed: 00:00:01.75
Execution Plan
----------------------------------------------------------
Plan hash value: 4136908276
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 930 | 7 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 5 | 930 | 7 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TEST | 50351 | 4572K| 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 93 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_TEST | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TEST"."OBJECT_ID"="OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
108083 consistent gets
0 physical reads
0 redo size
4244355 bytes sent via SQL*Net to client
37301 bytes received via SQL*Net from client
3358 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50351 rows processed
first_rows更倾向使用nested loop操作。这是由于nested loop的内外表都没集合(块操作)操作。非常容易返回前N条记录。而HASH JION至少内表是需要执行完毕,即需要在内存中把hash bucket构建完毕,才能让外表去探测去返回记录。first_rows 可以用在典型的一些分页查询中。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-672052/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-672052/