How can I force a bushy tree join in Oracle SQL?

As far as I know the default join is the deep left join. How can I force a bushy tree join on 4 tables?

Well, if you really want to do this, this is a possible way:

create table t1 (id number, c1 number);
create table t2 (id number, c2 number);
create table t3 (id number, c3 number);
create table t4 (id number, c4 number);

Original:

select
  t1.id, t1.c1, t2.c2, t3.c3, t4.c4
from
  t1, t2, t3, t4
where
  t1.id = t2.id and t2.id = t3.id and t3.id = t4.id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3021121231

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |   104 |     8   (0)| 00:00:01 |
|*  1 |  HASH JOIN            |      |     1 |   104 |     8   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |    78 |     6   (0)| 00:00:01 |
|*  3 |    HASH JOIN          |      |     1 |    52 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL | T1   |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   6 |    BUFFER SORT        |      |     1 |    26 |     4   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL | T4   |     1 |    26 |     2   (0)| 00:00:01 |
|   8 |   TABLE ACCESS FULL   | T3   |     1 |    26 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("T2"."ID"="T3"."ID" AND "T3"."ID"="T4"."ID")
   3 - access("T1"."ID"="T2"."ID")

Bushy:

with
  ta as (select /*+ no_merge */ t1.id, t1.c1, t2.c2 from t1, t2 where t1.id = t2.id),
  tb as (select /*+ no_merge */ t3.id, t3.c3, t4.c4 from t3, t4 where t3.id = t4.id)
select
    ta.id, ta.c1, ta.c2, tb.c3, tb.c4
from
  ta, tb
where
  ta.id = tb.id
;

Execution Plan
----------------------------------------------------------
Plan hash value: 975173817

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    78 |     8   (0)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    78 |     8   (0)| 00:00:01 |
|   2 |   VIEW               |      |     1 |    39 |     4   (0)| 00:00:01 |
|*  3 |    HASH JOIN         |      |     1 |    52 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   6 |   VIEW               |      |     1 |    39 |     4   (0)| 00:00:01 |
|*  7 |    HASH JOIN         |      |     1 |    52 |     4   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| T3   |     1 |    26 |     2   (0)| 00:00:01 |
|   9 |     TABLE ACCESS FULL| T4   |     1 |    26 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("TA"."ID"="TB"."ID")
   3 - access("T1"."ID"="T2"."ID")
   7 - access("T3"."ID"="T4"."ID")

StackExchange Database Administrators Q#62945, answer score: 5