引用：http://www.itpub.net/viewthread.php?tid=1254018&highlight=hint

2.NLJ_BATCHING，NO_NLJ_BATCHING
這是一種11G新的內部最佳化演算法。目前在網上幾乎沒有任何參考資料講如何實現的。
從《Troubleshooting Oracle Performance》書上的Chapter 10 Optimizing Joins我可以找到隻言片語：
“As of Oracle Database 11g, the following execution plan might be observed instead of the
previous one. Note that even if the query is always the same (that is, a two-table join), the
execution plan contains two nested loop joins! A simple performance test showed an improvement
of about 10 percent using it. This is probably because of a new internal optimization
that applies only to the new execution plan. To control this new execution plan, the hints
nlj_batching and no_nlj_batching are available.”
讓我舉個例，一個很簡單的兩個表的neested loop join：
create table t1 as select * from dba_objects;
create index t1idx on t1(object_id);
create table t2 as select * from dba_objects;
create index t2idx on t2(object_id);
select /*+use_nl(t1 t2) index(t1) index(t2) ordered NO_NLJ_BATCHING(t2)*/
t2.* from t1,t2
where t1.object_id=999
and t2.object_id=t1.object_id;
-------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    | 19511 |  4191K| 282 (1)| 00:00:04 |
| 1 |  TABLE ACCESS BY INDEX ROWID| T2 | 140 | 28980 |    2 (0)| 00:00:01 |
| 2 |   NESTED LOOPS             |    | 19511 |  4191K| 282 (1)| 00:00:04 |
|*  3 | INDEX RANGE SCAN       | T1IDX | 140 |  1820 |    1 (0)| 00:00:01 |
|*  4 | INDEX RANGE SCAN       | T2IDX | 56 |    |    1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"=999)
4 - access("T2"."OBJECT_ID"=999)

接著看看如果使用NLJ_BATCHING後SQL PLAN會變成什麼樣：
select /*+use_nl(t1 t2) index(t1) index(t2) ordered NLJ_BATCHING(t2)*/
t2.* from t1,t2
where t1.object_id=999
and t2.object_id=t1.object_id;
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    | 19511 |  4191K| 282 (1)| 00:00:04 |
| 1 |  NESTED LOOPS             |    |    |    |          |       |
| 2 | NESTED LOOPS             |    | 19511 |  4191K| 282 (1)| 00:00:04 |
|*  3 | INDEX RANGE SCAN       | T1IDX | 140 |  1820 |    1 (0)| 00:00:01 |
|*  4 | INDEX RANGE SCAN       | T2IDX | 56 |    |    1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 140 | 28980 |    2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"=999)
4 - access("T2"."OBJECT_ID"=999)
可以看到，當我們使用NLJ_BATCHING後，一個兩個表的neested loop join在執行計劃裡會顯示兩個neested loops。
經過我的若干大資料量的實驗，並沒有發現使用NLJ_BATCHING有特別大的如前面文件中所述的10%的improvement。
對於如下的小實驗，使用NLJ_BATCHING反而有微小的performance degradation。
create table t1 as select * from dba_objects where rownum<=1000;
create table t2 as select * from dba_objects where rownum<=1000;
update t1 set object_id=999;
update t2 set object_id=999;
create index t1idx on t1(object_id);
create index t2idx on t2(object_id);
select /*+use_nl(t1 t2) index(t1) index(t2) ordered NO_NLJ_BATCHING(t2)*/
t2.* from t1,t2
where t1.object_id=999
and t2.object_id=t1.object_id;
Elapsed: 00:00:56.73

Statistics
----------------------------------------------------------
      324  recursive calls
      0  db block gets
   149706  consistent gets
      6  physical reads
      0  redo size
45993532  bytes sent via SQL*Net to client
   733849  bytes received via SQL*Net from client
   66668  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
1000000  rows processed
select /*+use_nl(t1 t2) index(t1) index(t2) ordered NLJ_BATCHING(t2)*/
t2.* from t1,t2
where t1.object_id=999
and t2.object_id=t1.object_id;

Elapsed: 00:00:57.15
Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
   149674  consistent gets
      0  physical reads
      0  redo size
45993532  bytes sent via SQL*Net to client
   733849  bytes received via SQL*Net from client
   66668  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
1000000  rows processed
BTW，這裡有個隱藏引數控制是否預設開啟NLJ_BATCHING：
_nlj_batching_enabled ： enable batching of the RHS IO in NLJ