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")