create table wxh_tbd1 as select * from dba_objects;
create table wxh_tbd2 as select * from dba_objects;
create index t_1 on wxh_tbd1(object_name);
create index t_2 on wxh_tbd1(object_id);
create index t_3 on wxh_tbd2(object_name);
insert into wxh_tbd1 select * from wxh_tbd1;
insert into wxh_tbd2 select * from wxh_tbd2;
commit;

select /*+ NO_NLJ_BATCHING(b) */
dbms_rowid.rowid_block_number(b.rowid) bl, a.object_id,b.object_type
from wxh_tbd1 a, wxh_tbd2 b
where a.object_name = b.object_name
and a.object_id > 899 and a.object_id<902;

/*+ NO_NLJ_BATCHING(b) */
2   dbms_rowid.rowid_block_number(b.rowid) bl, a.object_id,b.object_type
3    from wxh_tbd1 a, wxh_tbd2 b
4   where a.object_name = b.object_name
5     and a.object_id > 899 and a.object_id<902;

        BL OBJECT_ID OBJECT_TYPE
---------- ---------- --------------------------------------
     23667        900 TABLE
     32537        900 TABLE
     23667        900 TABLE
     32537        900 TABLE
     23667        901 INDEX
     32537        901 INDEX
     23667        901 INDEX
     32537        901 INDEX

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1274335627

------------------------------------------------------------------------------------------
| Id | Operation                     | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    25 | 4200 |    13   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID | WXH_TBD2 |     6 |   534 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |          |    25 | 4200 |    13   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| WXH_TBD1 |     4 |   316 |     3   (0)| 00:00:01 |
|* 4 |     INDEX RANGE SCAN          | T_2      |     4 |       |     2   (0)| 00:00:01 |
|* 5 |    INDEX RANGE SCAN           | T_3      |     6 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("A"."OBJECT_ID">899 AND "A"."OBJECT_ID"<902)
5 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         12 recursive calls
          0 db block gets
        166 consistent gets
          0 physical reads
          0 redo size
        790 bytes sent via SQL*Net to client
        524 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
          8 rows processed

dbms_rowid.rowid_block_number(b.rowid) bl, a.object_id,b.object_type
2    3    from wxh_tbd1 a, wxh_tbd2 b
4   where a.object_name = b.object_name
5     and a.object_id > 899 and a.object_id<902;

        BL OBJECT_ID OBJECT_TYPE
---------- ---------- --------------------------------------
     23667        900 TABLE
     32537        900 TABLE
     23667        900 TABLE
     32537        900 TABLE
     23667        901 INDEX
     32537        901 INDEX
     23667        901 INDEX
     32537        901 INDEX

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 748461386

------------------------------------------------------------------------------------------
| Id | Operation                     | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    25 | 4200 |    13   (0)| 00:00:01 |
|   1 | NESTED LOOPS                 |          |       |       |            |          |
|   2 |   NESTED LOOPS                |          |    25 | 4200 |    13   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| WXH_TBD1 |     4 |   316 |     3   (0)| 00:00:01 |
|* 4 |     INDEX RANGE SCAN          | T_2      |     4 |       |     2   (0)| 00:00:01 |
|* 5 |    INDEX RANGE SCAN           | T_3      |     6 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | WXH_TBD2 |     6 |   534 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("A"."OBJECT_ID">899 AND "A"."OBJECT_ID"<902)
5 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         10 recursive calls
          0 db block gets
        166 consistent gets
          0 physical reads
          0 redo size
        790 bytes sent via SQL*Net to client
        524 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
          8 rows processed

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    | |   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    |   ------------------------------------------------
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         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.

nest loop內層迴圈會按照rowid排序嗎？

相關文章