[20181220]Bushy Join Trees in Oracle 12.2.txt

来源：这里教程网时间：2026-03-03 12:48:03 作者：

[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 -------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推