[20181220]Bushy Join Trees in Oracle 12.2.txt

--//oracle都是連線的結果再與另外的表連線,12.2c開始支援2個2個表之間的連線,也叫Bushy Join.樣子像這樣:

-----HJ-----

| |

---NL--- ---NL---

| | | |

T1 T2 T3 T4

--//感覺這樣的連線好處不是很明顯,自己測試看看.

--//直接使用連線的例子:https://www.pythian.com/blog/bushy-joins-trees-in-oracle-12-2/

1.環境:

SCOTT@test01p> @ ver1

PORT_STRING VERSION BANNER CON_ID

------------------------------ -------------- -------------------------------------------------------------------------------- ----------

IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

2.建立測試表:

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

--//分析略.

3.測試:

SCOTT@test01p> alter session set statistics_level=all;

Session altered.

SCOTT@test01p> SELECT * FROM t1, t2, t3, t4 WHERE t1.n1 = t2.n1 AND t3.n1 = t4.n1 AND t1.n2=t3.n2;

Plan hash value: 1007837908

--------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 892 (100)| | 100 |00:00:00.05 | 2129 | | | |

|* 1 | HASH JOIN | | 1 | 1 | 32 | 892 (2)| 00:00:01 | 100 |00:00:00.05 | 2129 | 1298K| 1298K| 1317K (0)|

|* 2 | HASH JOIN | | 1 | 100 | 2600 | 889 (2)| 00:00:01 | 100 |00:00:00.05 | 2119 | 1376K| 1376K| 1549K (0)|

| 3 | NESTED LOOPS | | 1 | 100 | 1600 | 303 (0)| 00:00:01 | 100 |00:00:00.01 | 23 | | | |

| 4 | NESTED LOOPS | | 1 | 100 | 1600 | 303 (0)| 00:00:01 | 100 |00:00:00.01 | 22 | | | |

| 5 | TABLE ACCESS FULL | T2 | 1 | 100 | 600 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 9 | | | |

|* 6 | INDEX RANGE SCAN | IDX_T1 | 100 | 1 | | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 13 | | | |

| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1 | 10 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 1 | | | |

| 8 | TABLE ACCESS FULL | T3 | 1 | 1000K| 9765K| 582 (2)| 00:00:01 | 1000K|00:00:00.03 | 2096 | | | |

| 9 | TABLE ACCESS FULL | T4 | 1 | 100 | 600 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 10 | | | |

--------------------------------------------------------------------------------------------------------------------------------------------------------------

--//連線順序是t2->t1->t3->t4.

4.當前版本預設還不支援bushy join:

SYS@test01p> @ hide _optimizer_bushy

old 10: and lower(a.ksppinm) like lower('%&1%')

new 10: and lower(a.ksppinm) like lower('%_optimizer_bushy%')

NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

------------------------------- ---------------------------------- ------------- ------------- -------------

_optimizer_bushy_cost_factor cost factor for bushy join TRUE 100 100

_optimizer_bushy_fact_dim_ratio bushy join dimension to fact ratio TRUE 20 20

_optimizer_bushy_fact_min_size minimumm fact size for bushy join TRUE 100000 100000

_optimizer_bushy_join enables bushy join TRUE off off

--//預設_optimizer_bushy_join=off.

SCOTT@test01p> alter session set "_optimizer_bushy_join"=on;

Session altered.

SCOTT@test01p> SELECT * FROM t1, t2, t3, t4 WHERE t1.n1 = t2.n1 AND t3.n1 = t4.n1 AND t1.n2=t3.n2;

Plan hash value: 3442393255

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 606 (100)| | 100 |00:00:00.01 | 48 | | | |

|* 1 | HASH JOIN | | 1 | 100 | 6800 | 606 (0)| 00:00:01 | 100 |00:00:00.01 | 48 | 1376K| 1376K| 1601K (0)|

| 2 | NESTED LOOPS | | 1 | 100 | 1600 | 303 (0)| 00:00:01 | 100 |00:00:00.01 | 23 | | | |

| 3 | NESTED LOOPS | | 1 | 100 | 1600 | 303 (0)| 00:00:01 | 100 |00:00:00.01 | 22 | | | |

| 4 | TABLE ACCESS FULL | T4 | 1 | 100 | 600 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 9 | | | |

|* 5 | INDEX RANGE SCAN | IDX_T3 | 100 | 1 | | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 13 | | | |

| 6 | TABLE ACCESS BY INDEX ROWID | T3 | 100 | 1 | 10 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 1 | | | |

| 7 | VIEW | VW_BUSHY_F79C84EE | 1 | 100 | 5200 | 303 (0)| 00:00:01 | 100 |00:00:00.01 | 25 | | | |

| 8 | NESTED LOOPS | | 1 | 100 | 1600 | 303 (0)| 00:00:01 | 100 |00:00:00.01 | 25 | | | |

| 9 | NESTED LOOPS | | 1 | 100 | 1600 | 303 (0)| 00:00:01 | 100 |00:00:00.01 | 23 | | | |

| 10 | TABLE ACCESS FULL | T2 | 1 | 100 | 600 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 9 | | | |

|* 11 | INDEX RANGE SCAN | IDX_T1 | 100 | 1 | | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 14 | | | |

| 12 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1 | 10 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 2 | | | |

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$EC45A358

4 - SEL$EC45A358 / T4@SEL$1

5 - SEL$EC45A358 / T3@SEL$1

6 - SEL$EC45A358 / T3@SEL$1

7 - SEL$4F4DF0AE / VW_BUSHY_F79C84EE@SEL$F79C84EE

8 - SEL$4F4DF0AE

10 - SEL$4F4DF0AE / T2@SEL$1

11 - SEL$4F4DF0AE / T1@SEL$1

12 - SEL$4F4DF0AE / T1@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("ITEM_1"="T3"."N2")

5 - access("T3"."N1"="T4"."N1")

11 - access("T1"."N1"="T2"."N1")

Note

-----

- this is an adaptive plan

--//邏輯讀僅僅48.比前面的少許多.主要是充分利用T3的索引.

--//id=7 出現的 VW_BUSHY_D96D1B60 ,明確表示使用了bushy

--//如果不設定引數"_optimizer_bushy_join"=on,可以使用如下提示,一樣產生同樣的效果.

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;

3.n1 = t4.n1 AND t1.n2=t3.n2;

[20181220]Bushy Join Trees in Oracle 12.2.txt

相關文章