Forcing nested loop join with the use_nl hint

Posted on by

By Brian Fitzgerald

Straight to the point, “use_nl ( b )” means that b is the inner table. There is no driving table in the “use_nl” hint.

                select
                /*+
                ordered use_nl ( b )
                */
                b.cb, b.ca
                from
                a join b
                on b.ca = a.ca

The “ordered” hint here means that “a” is outer (driver) and “b” is inner. The “use_nl” hint will usually not work without the ordered or leading hint. If the ordered hint is used, then the tables must appear in the SQL as driver first, inner second.

set linesize 32767
set pagesize 50000
set trimspool on
column plan_table_output format a100

drop table a purge;
create table a
(
ca      number
)
;

insert into a ( ca )
select
mod( level, 10 )
from dual
connect by level <= 10;

commit;

select count(distinct ca), count(*) from a;

drop table b purge;
create table b
(
cb      number,
ca      number
)
;

insert into b ( cb, ca )
select
mod( level, 100),
mod( level, 10 )
from dual
connect by level  @ nldemo.setup.sql

Table dropped.


Table created.


10 rows created.


Commit complete.


COUNT(DISTINCTCA)   COUNT(*)
----------------- ----------
               10         10


Table dropped.


Table created.


100000 rows created.


Commit complete.


COUNT(DISTINCTCB) COUNT(DISTINCTCA)   COUNT(*)
----------------- ----------------- ----------
              100                10     100000


Session altered.


PL/SQL procedure successfully completed.


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  9pjqkg6n7sd24, child number 0
-------------------------------------
SELECT /*+   ordered use_nl ( b )   */ B.CB, B.CA FROM A JOIN B ON B.CA
= A.CA

Plan hash value: 4030965610

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100K|00:00:00.10 |    3053 |
|   1 |  NESTED LOOPS      |      |      1 |    112K|    100K|00:00:00.10 |    3053 |
|   2 |   TABLE ACCESS FULL| A    |      1 |     10 |     10 |00:00:00.01 |      16 |
|*  3 |   TABLE ACCESS FULL| B    |     10 |  11247 |    100K|00:00:00.05 |    3037 |
-------------------------------------------------------------------------------------

Notice from the “Starts” column that driving table A is scanned once and inner table B is scanned once per matching row in A, or 10 times. With “ordered”, “use_nl(b)” only works if the query is written as “A join B”.

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

   3 - filter("B"."CA"="A"."CA")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


25 rows selected.


PL/SQL procedure successfully completed.


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  fsa6akuaxq68d, child number 0
-------------------------------------
SELECT /*+   ordered use_nl ( a )   */ B.CB, B.CA FROM B JOIN A ON B.CA
= A.CA

Plan hash value: 4193326952

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100K|00:00:01.13 |     701K|
|   1 |  NESTED LOOPS      |      |      1 |    112K|    100K|00:00:01.13 |     701K|
|   2 |   TABLE ACCESS FULL| B    |      1 |    112K|    100K|00:00:00.02 |    1174 |
|*  3 |   TABLE ACCESS FULL| A    |    100K|      1 |    100K|00:00:00.95 |     700K|
-------------------------------------------------------------------------------------

This showed “ordered” with “b join a” and “use_nl(a)”. Driver B is scanned once. Inner table A is scanned once per matching row in B, or 100,000 times.

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

   3 - filter("B"."CA"="A"."CA")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


25 rows selected.


PL/SQL procedure successfully completed.


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  22fqf7qj5pykf, child number 0
-------------------------------------
SELECT /*+   leading ( a, b ) use_nl ( b )   */ B.CB, B.CA FROM B JOIN
A ON B.CA = A.CA

Plan hash value: 4030965610

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100K|00:00:00.10 |    3053 |
|   1 |  NESTED LOOPS      |      |      1 |    112K|    100K|00:00:00.10 |    3053 |
|   2 |   TABLE ACCESS FULL| A    |      1 |     10 |     10 |00:00:00.01 |      16 |
|*  3 |   TABLE ACCESS FULL| B    |     10 |  11247 |    100K|00:00:00.06 |    3037 |
-------------------------------------------------------------------------------------

In this case, the tables are joined in the order specified in the “leading (a, b)” hint, even though the SQL is written as “B join A”.

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

   3 - filter("B"."CA"="A"."CA")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


25 rows selected.


PL/SQL procedure successfully completed.


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  3pd082tfh9chp, child number 0
-------------------------------------
SELECT /*+   leading ( b, a ) use_nl ( a )   */ B.CB, B.CA FROM B JOIN
A ON B.CA = A.CA

Plan hash value: 4193326952

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100K|00:00:01.29 |     701K|
|   1 |  NESTED LOOPS      |      |      1 |    112K|    100K|00:00:01.29 |     701K|
|   2 |   TABLE ACCESS FULL| B    |      1 |    112K|    100K|00:00:00.03 |    1174 |
|*  3 |   TABLE ACCESS FULL| A    |    100K|      1 |    100K|00:00:01.10 |     700K|
-------------------------------------------------------------------------------------

Rounding out this blog article, here is “leading (b, a)”, with B as driver, A inner.

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

   3 - filter("B"."CA"="A"."CA")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


25 rows selected.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

Leave a Reply