René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

January 17, 2008: On forcing a nested loop join instead of a hash join

I have two tables.. 
create table t1 (
  col_1 number
);

create table t2 (
  col_1 number
);
... that I want to join with a nested loop. 
explain plan for select * from t1 natural join t2;

select * from table(dbms_xplan.display);
However, Oracle chooses to join them with a hash join: 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
So, I try with the ordered and use_nl hint.. 
explain plan for select /*+ ordered use_nl(t1) */ * from t1 natural join t2;

select * from table(dbms_xplan.display);
.. but I have no success: 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
So, I leave out the ordered hint... 
explain plan for select /*+ use_nl(t1) */ * from t1 natural join t2;

select * from table(dbms_xplan.display);
... which joins the tables with a nested loop: 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |     1 |    26 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
So, curious, as I am, I try out what happens when I use t2 instead of t1 in the use_nl hint... 
explain plan for select /*+ ordered use_nl(t2) */ * from t1 natural join t2;

select * from table(dbms_xplan.display);
...and this time, I have a nested loop although I have the ordered hint. Remember, when I had the t1 in the use_nl hint together with the ordered hint, I had a hash join. 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |     1 |    26 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
What happens, if I have the t2 in the use_nl hint without the ordered hint? 
explain plan for select /*+ use_nl(t2) */ * from t1 natural join t2;

select * from table(dbms_xplan.display);
Again, I have a nested loop: 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |     1 |    26 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
When I did those tests, the tables were both empty. I now fill the tables, one with a thousand records, the other with a million records... 
begin

  
  for i in 1 .. 1000 loop
    insert into t1 values(i);
  end loop;

  for i in 1 .. 1000000 loop
    insert into t2 values(i);
  end loop;

end;
/

commit;
...and do the same queries again.
No hint: 
explain plan for select * from t1 natural join t2;

select * from table(dbms_xplan.display);
This resulted in a hash join with empty tables and results in a hash join with filled tables: 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 | 26000 |   475  (10)| 00:00:06 |
|*  1 |  HASH JOIN         |      |  1000 | 26000 |   475  (10)| 00:00:06 |
|   2 |   TABLE ACCESS FULL| T1   |  1000 | 13000 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1049K|    13M|   454   (7)| 00:00:06 |
---------------------------------------------------------------------------
ordered, use_nl, t1:
,
explain plan for select /*+ ordered use_nl(t1) */ * from t1 natural join t2;

select * from table(dbms_xplan.display);
This resulted in a hash join with empty tables and results in a hash join with filled tables: 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 | 26000 |   475  (10)| 00:00:06 |
|*  1 |  HASH JOIN         |      |  1000 | 26000 |   475  (10)| 00:00:06 |
|   2 |   TABLE ACCESS FULL| T1   |  1000 | 13000 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1049K|    13M|   454   (7)| 00:00:06 |
---------------------------------------------------------------------------
use_nl, t1: 
explain plan for select /*+ use_nl(t1) */ * from t1 natural join t2;

select * from table(dbms_xplan.display);
This resulted in a nested loop join with empty tables but results in a hash join with filled tables: 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 | 26000 |   475  (10)| 00:00:06 |
|*  1 |  HASH JOIN         |      |  1000 | 26000 |   475  (10)| 00:00:06 |
|   2 |   TABLE ACCESS FULL| T1   |  1000 | 13000 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1049K|    13M|   454   (7)| 00:00:06 |
---------------------------------------------------------------------------
ordered, use_nl, t2: 
explain plan for select /*+ ordered use_nl(t2) */ * from t1 natural join t2;

select * from table(dbms_xplan.display);
This resulted in a nested loop join with empty tables and results in a nested loop join with filled tables: 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 | 26000 |   452K  (7)| 01:30:27 |
|   1 |  NESTED LOOPS      |      |  1000 | 26000 |   452K  (7)| 01:30:27 |
|   2 |   TABLE ACCESS FULL| T1   |  1000 | 13000 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |    13 |   452   (7)| 00:00:06 |
---------------------------------------------------------------------------
use_nl, t2: 
explain plan for select /*+ use_nl(t2) */ * from t1 natural join t2;

select * from table(dbms_xplan.display);
This resulted in a nested loop join with empty tables but results in a hash join with filled tables: 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 | 26000 |   475  (10)| 00:00:06 |
|*  1 |  HASH JOIN         |      |  1000 | 26000 |   475  (10)| 00:00:06 |
|   2 |   TABLE ACCESS FULL| T1   |  1000 | 13000 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1049K|    13M|   454   (7)| 00:00:06 |
---------------------------------------------------------------------------
Interesting, the result is different from quering empty tables. Hmm... why is that?
In order to answer this question, it must be understood that the optimizer first decides on the join order and then for the join method. While it evaluates the possible join orders, each possible join method is evalued for each join order under investigation. During this evaluation, the optimizer finds what it thinks is the cheapest method to join the tables to be a hash join with t1 being the hashed table and t2 being the driving table. So, if we want to enforce the use_nl hint not to be ignored, we have to make the optimizer skip evaluating join orders. This is achieved with the ordered hint. This hint tells the optimizer that it should join the tables in the order indicated in the from clause of the select statement. When an ordered hint is followed by a join method hint (use_nl in our case), the optimizer also skips evaluation the join method since we tell it which one to use. Now, of course, the join method hint must make sense. It doesn't in the example where we hinted ordered use_nl(t1) because t1 specifies the second row source (or inner table for nested loops) and t1 is not the second table, but the first table.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.

Warning: require(): open_basedir restriction in effect. File(/var/www/virtual/adp-gmbh.ch/forum/comment.inc) is not within the allowed path(s): (/home/httpd/vhosts/renenyffenegger.ch/:/tmp/) in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2008/01/17.php on line 525

Warning: require(/var/www/virtual/adp-gmbh.ch/forum/comment.inc): failed to open stream: Operation not permitted in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2008/01/17.php on line 525

Fatal error: require(): Failed opening required '/var/www/virtual/adp-gmbh.ch/forum/comment.inc' (include_path='.:/home/httpd/vhosts/renenyffenegger.ch') in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2008/01/17.php on line 525