2012-03-20

join strategies to cross join

There are three different join startegies the optimizer may perform a join. One of those is different considering a cross join.

In this example join is executed using nested loops. It may be hinted also to a merge join path.

select * from dual d1 cross join dual d2;

select * from table(dbms_xplan.display_cursor(format=>'basic'));

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  NESTED LOOPS      |      |
|   2 |   TABLE ACCESS FULL| DUAL |
|   3 |   TABLE ACCESS FULL| DUAL |
-----------------------------------

select /*+use_merge(d1 d2)*/ * from dual d1 cross join dual d2;

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  MERGE JOIN CARTESIAN|      |
|   2 |   TABLE ACCESS FULL  | DUAL |
|   3 |   TABLE ACCESS FULL  | DUAL |
-------------------------------------

The third approach would be hash join. But it is not possible.

select /*+use_hash(d1 d2)*/ * from dual d1 cross join dual d2;

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  NESTED LOOPS      |      |
|   2 |   TABLE ACCESS FULL| DUAL |
|   3 |   TABLE ACCESS FULL| DUAL |
-----------------------------------

Change the query to inner join and have a join condition. The USE_HASH hint is obeyed.
select /*+use_hash(d1 d2)*/ * from dual d1 inner join dual d2 on d1.dummy = d2.dummy;

select * from table(dbms_xplan.display_cursor(format=>'basic +predicate'));

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN         |      |
|   2 |   TABLE ACCESS FULL| DUAL |
|   3 |   TABLE ACCESS FULL| DUAL |
-----------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("D1"."DUMMY"="D2"."DUMMY")

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.