Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Girish.

Asked: August 30, 2017 - 9:49 am UTC

Last updated: September 06, 2017 - 12:46 am UTC

Version: 12c

Viewed 50K+ times! This question is

You Asked

Hi,

I have seen and used USE_NL hint in below format

1) USE_NL(t1 t2)
2) USE_NL(t1)

I have got code for review and USE_NL hint is used with more than two tables as shown below

USE_NL(t1 t1 t3 t4 t5)

Will this be used and considered by Oracle DB ? Is it right way to use ? Is it for only two tables ?

Thanks,
Girish

and Connor said...

All three variants are valid and possible, eg


SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL> create table t3 as select * from dba_objects;

Table created.

SQL>
SQL> create index ix1 on t1 ( object_id );

Index created.

SQL> create index ix2 on t2 ( object_id );

Index created.

SQL> create index ix3 on t3 ( object_id );

Index created.

SQL>
SQL> set autotrace traceonly explain

--
-- default is a hash join for all
--
SQL> select *
  2  from t1, t2, t3
  3  where t1.object_id = t2.object_id
  4  and   t2.object_id = t3.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1487401159

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 78268 |    29M|       |  3901   (1)| 00:00:01 |
|*  1 |  HASH JOIN          |      | 78268 |    29M|    10M|  3901   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | T3   | 78284 |     9M|       |   425   (1)| 00:00:01 |
|*  3 |   HASH JOIN         |      | 78275 |    19M|    10M|  1919   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   | 78283 |     9M|       |   425   (1)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| T1   | 78282 |     9M|       |   425   (1)| 00:00:01 |
------------------------------------------------------------------------------------

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

   1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
   3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan

SQL>
SQL>
--
-- this *looks* like it is inavlid/ignored, but see the last example for a reason why
--

SQL> select /*+ use_nl(t1) */ *
  2  from t1, t2, t3
  3  where t1.object_id = t2.object_id
  4  and   t2.object_id = t3.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1573120526

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 78268 |    29M|       |  3901   (1)| 00:00:01 |
|*  1 |  HASH JOIN          |      | 78268 |    29M|    10M|  3901   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | T3   | 78284 |     9M|       |   425   (1)| 00:00:01 |
|*  3 |   HASH JOIN         |      | 78275 |    19M|    10M|  1919   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   | 78282 |     9M|       |   425   (1)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| T2   | 78283 |     9M|       |   425   (1)| 00:00:01 |
------------------------------------------------------------------------------------

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

   1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
   3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan

SQL>
SQL> select /*+ use_nl(t1 t2) */ *
  2  from t1, t2, t3
  3  where t1.object_id = t2.object_id
  4  and   t2.object_id = t3.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 745910267

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      | 78268 |    29M|       |   158K  (1)| 00:00:07 |
|   1 |  NESTED LOOPS                |      | 78268 |    29M|       |   158K  (1)| 00:00:07 |
|   2 |   NESTED LOOPS               |      | 78276 |    29M|       |   158K  (1)| 00:00:07 |
|*  3 |    HASH JOIN                 |      | 78276 |    19M|    10M|  1919   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | T2   | 78283 |     9M|       |   425   (1)| 00:00:01 |
|   5 |     TABLE ACCESS FULL        | T3   | 78284 |     9M|       |   425   (1)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN          | IX1  |     1 |       |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |   132 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
   6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan

SQL>
SQL> select /*+ use_nl(t1 t2 t3) */ *
  2  from t1, t2, t3
  3  where t1.object_id = t2.object_id
  4  and   t2.object_id = t3.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1859668671

-----------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      | 78268 |    29M|   313K  (1)| 00:00:13 |
|   1 |  NESTED LOOPS                          |      | 78268 |    29M|   313K  (1)| 00:00:13 |
|   2 |   NESTED LOOPS                         |      | 78275 |    29M|   313K  (1)| 00:00:13 |
|   3 |    NESTED LOOPS                        |      | 78275 |    19M|   157K  (1)| 00:00:07 |
|   4 |     TABLE ACCESS FULL                  | T1   | 78282 |     9M|   425   (1)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2   |     1 |   132 |     2   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                  | IX2  |     1 |       |     1   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN                    | IX3  |     1 |       |     1   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID          | T3   |     1 |   132 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   7 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

--
-- the first example didnt use the nested loop because it was saying, if "t1" is not the "starting" table, then drive into with a nested loop.
-- But t1 *was* the starting table, so the hint did not apply, hence the hash join.  By saying that t2 is the leading (starting) table, 
-- then the hint now comes into play
--

SQL>
SQL> select /*+ leading(t2) use_nl(t1) */ *
  2  from t1, t2, t3
  3  where t1.object_id = t2.object_id
  4  and   t2.object_id = t3.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 745910267

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      | 78268 |    29M|       |   158K  (1)| 00:00:07 |
|   1 |  NESTED LOOPS                |      | 78268 |    29M|       |   158K  (1)| 00:00:07 |
|   2 |   NESTED LOOPS               |      | 78276 |    29M|       |   158K  (1)| 00:00:07 |
|*  3 |    HASH JOIN                 |      | 78276 |    19M|    10M|  1919   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | T2   | 78283 |     9M|       |   425   (1)| 00:00:01 |
|   5 |     TABLE ACCESS FULL        | T3   | 78284 |     9M|       |   425   (1)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN          | IX1  |     1 |       |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |   132 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
   6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan

SQL>



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.