Batching NLJ optimization and ordering

You might be aware of the batching nested loops join(batching NLJ) optimization introduced at Oracle 11g. For who are not aware of this new feature, let me show you two different execution plans(Oracle 10g vs. 11g) of the same SQL.

-- Oracle 10g
------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|*  1 |  COUNT STOPKEY                 |       |
|   2 |   TABLE ACCESS BY INDEX ROWID  | T2    |  <-- Here
|   3 |    NESTED LOOPS                |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1    |
|*  5 |      INDEX RANGE SCAN          | T1_N1 |
|*  6 |     INDEX RANGE SCAN           | T2_N1 |
------------------------------------------------

-- Oracle 11g
------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|*  1 |  COUNT STOPKEY                 |       |
|   2 |   NESTED LOOPS                 |       |
|   3 |    NESTED LOOPS                |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1    |
|*  5 |      INDEX RANGE SCAN          | T1_N1 |
|*  6 |     INDEX RANGE SCAN           | T2_N1 |
|   7 |    TABLE ACCESS BY INDEX ROWID | T2    |   <-- And here
------------------------------------------------

You might note that Oracle 10g and 11g have very different location of the table lookup operation on the table T2. This is a symptom of the nested loops join optimization of Oracle 11g. By using batching NLJ, you might see a decreased logical reads and improved performance.

So far, so good, but a couple of days ago, I hit a somewhat strange ordering problem with the batching NLJ. Following is a replayable test case. Just note that the query is using index(t1_n1) to implement the pagination without the overhead of ORDER BY.

create table t1
as
select 1 as c1, mod(level, 4) as c2, level as c3, level as c4, rpad('x',1000) as dummy
from dual
connect by level <= 1000;

create table t2
as
select 1001-level as c1, level as c2, rpad('x',1000) as dummy
from dual
connect by level <= 100;

create index t1_n1 on t1(c1, c2, c3);
create index t2_n1 on t2(c1);

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');

explain plan for
select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
	rownum as rnum,
	t2.c1,
	t1.c4,
	t2.c2
from t1, t2
where 
	t1.c3 = t2.c1
	and t1.c1 = 1
	and t1.c2 = 0
	and rownum <= 20
;

select * from table(dbms_xplan.display);

-- Read from the disk
alter system flush buffer_cache;

select * from (
	select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
		rownum as rnum,
		t2.c1,
		t1.c4,
		t2.c2
	from t1, t2
	where 
		t1.c3 = t2.c1
		and t1.c1 = 1
		and t1.c2 = 0
		and rownum <= 20
) where rnum >= 15
;

-- Read from the buffer cache
select * from (
	select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
		rownum as rnum,
		t2.c1,
		t1.c4,
		t2.c2
	from t1, t2
	where 
		t1.c3 = t2.c1
		and t1.c1 = 1
		and t1.c2 = 0
		and rownum <= 20
) where rnum >= 15
;

-- Disable exceptions for buffer cache misses
alter session set "_nlj_batching_misses_enabled" = 0;

-- Read from the disk
alter system flush buffer_cache;

select * from (
	select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
		rownum as rnum,
		t2.c1,
		t1.c4,
		t2.c2
	from t1, t2
	where 
		t1.c3 = t2.c1
		and t1.c1 = 1
		and t1.c2 = 0
		and rownum <= 20
) where rnum >= 15
;

-- Read from the buffer cache
select * from (
	select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
		rownum as rnum,
		t2.c1,
		t1.c4,
		t2.c2
	from t1, t2
	where 
		t1.c3 = t2.c1
		and t1.c1 = 1
		and t1.c2 = 0
		and rownum <= 20
) where rnum >= 15
;

To save your time, let me show you the result of the above test case with some comments appended.

-- Case1 : batching NLJ enabled
-- when the query reads from the disk
      RNUM         C1         C4         C2
---------- ---------- ---------- ----------
        15        960        960         41
        16        964        964         37
        17        980        980         21  <-- Why 980 here?
        18        968        968         33
        19        972        972         29
        20        976        976         25


-- when the query reads from the buffer cache
      RNUM         C1         C4         C2
---------- ---------- ---------- ----------
        15        960        960         41
        16        964        964         37
        17        968        968         33
        18        972        972         29
        19        976        976         25
        20        980        980         21


-- Case 2: batching NLJ disabled
-- when the query reads from the disk
      RNUM         C1         C4         C2
---------- ---------- ---------- ----------
        15        960        960         41
        16        964        964         37
        17        968        968         33
        18        972        972         29
        19        976        976         25
        20        980        980         21

-- when the query reads from the buffer cache
      RNUM         C1         C4         C2
---------- ---------- ---------- ----------
        15        960        960         41
        16        964        964         37
        17        968        968         33
        18        972        972         29
        19        976        976         25
        20        980        980         21

The simple explanation is :
“The new optimization code of the nested loops join does not guarantee that the rows would be returned as an order of the outer table, especially when it reads the data from the disk.”

This could be a limitation when you want to get the ordered rows using index and NLJ in the pagination query, but I don’t believe that this could be classified as a bug. The only way to ensure the order of the returning rows is always to use ORDER BY clause.

But if you still want to use INDEX not ORDER BY in the above query(which was a natural selection in Oracle 10g), the workarounds is one of the followings.

• Set parameter – _nlj_batching_misses_enabled = 0;
• Set parameter – _nlj_batching_enabled = 0;
• Append hint – NO_NLJ_BATCHING(t2)

It seems that I need to do further research on the batching NLJ – it’s exact mechanism and the pitfalls.