Bushy join trees in Oracle 12.2

by Vyacheslav Rasskazov on Apr 4, 2017 12:00:00 AM

There are multiple optimizer features introduced in the Oracle Database 12.2 Release. Bushy Join Trees is the one that I'll be demonstrating in this post. First, I'll create four tables with two indexes:

create table t1 as select rownum n1, rownum n2 from dual connect by level <= 1000000;
 create table t2 as select rownum n1, rownum n2 from dual connect by level <= 100;
 create table t3 as select rownum n1, rownum n2 from dual connect by level <= 1000000;
 create table t4 as select rownum n1, rownum n2 from dual connect by level <= 100;
 
 create index idx_t1 on t1(n1);
 create index idx_t3 on t3(n1);

Now, I'll run the next query:

select * from t1, t2, t3, t4 where t1.n1 = t2.n1 and t3.n1 = t4.n1 and t1.n2=t3.n2;

The most efficient "Bushy" execution plan for this query looks like the next one:

 -----HJ----- 
  | |
  | |
 ---NL--- ---NL---
 | | | |
 T1 T2 T3 T4

This plan joins T1 and T2 tables by Nested Loop using idx_t1 index, joins T3 and T4 tables by Nested Loop using idx_t3 index and finally joins results of the previous joins by the Hash Join. But Oracle has never been able to generate such execution plan automatically. You had to rewrite this query with subqueries and bunch of hints in order to force this kind of execution plan. The following example shows the typical execution plan that Oracle can generate:

Execution Plan
 ----------------------------------------------------------
 Plan hash value: 1007837908
 
 -----------------------------------------------------------------------------------------
 | Id | Operation  | Name | Rows | Bytes | Cost (%CPU)| Time |
 -----------------------------------------------------------------------------------------
 | 0 | SELECT STATEMENT  | | 1 | 32 | 888 (1)| 00:00:01 |
 |* 1 | HASH JOIN  | | 1 | 32 | 888 (1)| 00:00:01 |
 |* 2 | HASH JOIN  | | 100 | 2600 | 885 (1)| 00:00:01 |
 | 3 | NESTED LOOPS  | | 100 | 1600 | 303 (0)| 00:00:01 |
 | 4 | NESTED LOOPS  | | 100 | 1600 | 303 (0)| 00:00:01 |
 | 5 | TABLE ACCESS FULL  | T2 | 100 | 600 | 3 (0)| 00:00:01 |
 |* 6 | INDEX RANGE SCAN  | IDX_T1 | 1 | | 2 (0)| 00:00:01 |
 | 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 3 (0)| 00:00:01 |
 | 8 | TABLE ACCESS FULL  | T3 | 1000K| 9765K| 579 (1)| 00:00:01 |
 | 9 | TABLE ACCESS FULL  | T4 | 100 | 600 | 3 (0)| 00:00:01 |
 -----------------------------------------------------------------------------------------
 
 Predicate Information (identified by operation id):
 ---------------------------------------------------
 
  1 - access("T3"."N1"="T4"."N1")
  2 - access("T1"."N2"="T3"."N2")
  6 - access("T1"."N1"="T2"."N1")

We can see that a full T3 table scan and T3 table can be significantly large. Oracle 12.2 has introduced new BUSHY_JOIN hint and bunch of hidden "_optimizer_bushy" parameters: _optimizer_bushy_join _optimizer_bushy_fact_min_size _optimizer_bushy_fact_dim_ratio _optimizer_bushy_cost_factor. _optimizer_bushy_join parameter is 'off' by default and you have to set it to 'on' or to use a BUSHY_JOIN hint. Let's try with a hint:

select /*+ qb_name(main) BUSHY_JOIN(@"MAIN" ( "T1"@"MAIN" "T2"@"MAIN" )) */
  * from t1, t2, t3, t4 where t1.n1 = t2.n1 and t3.n1 = t4.n1 and t1.n2=t3.n2;
 
 100 rows selected.
 
 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 1929967733
 
 ----------------------------------------------------------------------------------------------------
 | Id | Operation  | Name  | Rows | Bytes | Cost (%CPU)| Time  |
 ----------------------------------------------------------------------------------------------------
 | 0 | SELECT STATEMENT  |  | 100 | 6800 | 606 (0)| 00:00:01 |
 |* 1 | HASH JOIN  |  | 100 | 6800 | 606 (0)| 00:00:01 |
 | 2 | NESTED LOOPS  |  | 100 | 1600 | 303 (0)| 00:00:01 |
 | 3 | NESTED LOOPS  |  | 100 | 1600 | 303 (0)| 00:00:01 |
 | 4 | TABLE ACCESS FULL  | T4  | 100 | 600 | 3 (0)| 00:00:01 |
 |* 5 | INDEX RANGE SCAN  | IDX_T3  | 1 |  | 2 (0)| 00:00:01 |
 | 6 | TABLE ACCESS BY INDEX ROWID | T3  | 1 | 10 | 3 (0)| 00:00:01 |
 | 7 | VIEW  | VW_BUSHY_D96D1B60 | 100 | 5200 | 303 (0)| 00:00:01 |
 | 8 | NESTED LOOPS  |  | 100 | 1600 | 303 (0)| 00:00:01 |
 | 9 | NESTED LOOPS  |  | 100 | 1600 | 303 (0)| 00:00:01 |
 | 10 | TABLE ACCESS FULL  | T2  | 100 | 600 | 3 (0)| 00:00:01 |
 |* 11 | INDEX RANGE SCAN  | IDX_T1  | 1 |  | 2 (0)| 00:00:01 |
 | 12 | TABLE ACCESS BY INDEX ROWID| T1  | 1 | 10 | 3 (0)| 00:00:01 |
 ----------------------------------------------------------------------------------------------------
 
 Predicate Information (identified by operation id):
 ---------------------------------------------------
 
  1 - access("ITEM_1"="T3"."N2")
  5 - access("T3"."N1"="T4"."N1")
  11 - access("T1"."N1"="T2"."N1")

We can see VW_BUSHY_D96D1B60 internal view at step 7, and this is a definitely a "bushy" plan. The feature still is not enabled by default, but you don't need to rewrite the query for a proper plan.

Bushy join trees in Oracle 12.2

No Comments Yet

Subscribe by email

Share this

Bushy join trees in Oracle 12.2

Share this

No Comments Yet

Subscribe by email