first_rows更傾向使用nested loop操作。

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 可以用在典型的一些分頁查詢中。