执行计划之表连接1：连接树（Join Trees）

SQLplusDB

发布于 2022-08-19 21:47:16

4990

发布于 2022-08-19 21:47:16

文章被收录于专栏：Oracle数据库技术Oracle数据库技术

来源|SQL和数据库技术(ID:SQLplusDB)

概述

阅读官方在线文档无疑是学习Oracle最好的方法，本文参考在线文档介绍表连接以及连接树（Join Trees）。

表连接概述

Oralce可以把两个数据集通过一定的关联条件进行结合操作，即表连接（Join）。

Oralce数据库的表连接主要包括两种语法：

1.标准SQL语法：(Ansi Join)

通过JOIN ON语句，进行表连接。
例：
select emp.deptno,emp.empno
from emp join dept
on emp.deptno = dept.deptno;

2.Oracle SQL语法：

通过Where条件，进行表连接。
例：
select emp.deptno,emp.empno
from emp,dept
where emp.deptno=dept.deptno;

连接树（Join Trees）

多个数据集（表）进行连接时，执行过程可以表示成树形结构，即连接树。

根据访问表的顺序不同，连接树可以分为左深树（Left Deep Join Tree）、右深树（Right Deep Join Tree）、浓密树（Bushy Join Tree）。

左深树（Left Deep Join Tree）

如果连接树的每个连接的右节点都是一个表，就是左深树（Left Deep Join Tree）。

左深树（Left Deep Join Tree）一般包括以下特点：

?左深树是Oracle优化器最普遍使用的连接树
?嵌套循环连接的连接树仅可能是左深树

右深树（Right Deep Join Tree）

如果连接树的每个连接的左节点都是一个表，就是右深树（Right Deep Join Tree。

右深树（Right Deep Join Tree）一般包括以下特点：

?通常在数据仓库的环境中使用，如：用于连接事实表和多个维度表的连接(星型模式)。
?哈希连接和排序合并连接的连接树有可能是右深树。
?哈希连接为右深树时，可能会消耗大量的PGA
因为哈希连接为右深树时，会同时有多个表被做成Hash表，从而消耗过多的PGA. （Ref Old Bug 4475206）

浓密树（Bushy Join Tree）

如果连接树的左节点或右节点都有可能不是表，就是浓密树（Bushy Join Tree）。

浓密树（Bushy Join Tree）一般包括以下特点：

?优化器无法选择其他树形连接是才会选择浓密树
?一般当查询包含子查询或者视图时，可能会产生浓密树。如无法进行视图合并等

连接树的处理

连接树的一般处理规则如下：

?从最左端的叶节点开始处理
?左节点的处理优先级高于右节点
?左节点驱动右节点
?子节点在父节点之前进行处理
?子节点处理完获得的数据返回给父节点。

连接树的执行计划例

下面我们创建4个表，通过SQL文和HINT的组合来生成各种连接树的执行计划，以帮助我们加深对连接树的理解。

--准备表
SQL> conn scott/tiger
SQL> create table t1 (id number,col varchar2(20));
SQL> create table t2 (id number,col varchar2(20));
SQL> create table t3 (id number,col varchar2(20));
SQL> create table t4 (id number,col varchar2(20));


--插入数据
SQL> begin
for i in 1..100 loop
insert into t1 values(i,i||'t');
insert into t1 values(i+10,i||'t');
insert into t1 values(i+20,i||'t');
insert into t1 values(i+30,i||'t');
end loop;
commit;
end;
/


SQL> BEGIN
dbms_stats.gather_table_stats('scott','t1');
dbms_stats.gather_table_stats('scott','t2');
dbms_stats.gather_table_stats('scott','t3');
dbms_stats.gather_table_stats('scott','t4');
END;
/


---查看执行计划
SQL> SET AUTOTRACE TRACEONLY EXPLAIN

1.左深树（Hash Join）

SQL> SELECT /*+ ordered use_hash(t2,t3,t4) */ t1.*, t2.*, t3.*, t4.*
  2  FROM t1, t2, t3, t4
  3  WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;


执行计划
----------------------------------------------------------
Plan hash value: 894925296


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    82 |     9   (0)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    82 |     9   (0)| 00:00:01 |
|*  2 |   HASH JOIN          |      |     1 |    57 |     7   (0)| 00:00:01 |
|*  3 |    HASH JOIN         |      |     1 |    32 |     5   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   400 |  2800 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |     1 |    25 |     2   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | T3   |     1 |    25 |     2   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL  | T4   |     1 |    25 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------


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


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

2. 左深树（Nest loop）

SQL> SELECT /*+ ordered use_nl(t2,t3,t4) */ t1.*, t2.*, t3.*, t4.*
  2  FROM t1, t2, t3, t4
  3  WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;


执行计划
----------------------------------------------------------
Plan hash value: 4050478457

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    82 |   117   (0)| 00:00:01 |
|   1 |  NESTED LOOPS        |      |     1 |    82 |   117   (0)| 00:00:01 |
|   2 |   NESTED LOOPS       |      |     1 |    57 |   115   (0)| 00:00:01 |
|   3 |    NESTED LOOPS      |      |     1 |    32 |   113   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   400 |  2800 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| T2   |     1 |    25 |     0   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL | T3   |     1 |    25 |     2   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS FULL  | T4   |     1 |    25 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------


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


   5 - filter("T1"."ID"="T2"."ID")
   6 - filter("T2"."ID"="T3"."ID")
   7 - filter("T3"."ID"="T4"."ID")

3. 右深树

SQL> SELECT /*+ LEADING (t4 t3 t2 t1)
  2  USE_HASH(t3) SWAP_JOIN_INPUTS(t3)
  3  USE_HASH(t2) SWAP_JOIN_INPUTS(t2)
  4  USE_HASH(t1) SWAP_JOIN_INPUTS(t1)
  5  */ t1.*, t2.*, t3.*, t4.*
  6  FROM t2, t1, t3, t4
  7  WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;


执行计划
----------------------------------------------------------
Plan hash value: 2117174619


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    82 |     9   (0)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    82 |     9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T1   |   400 |  2800 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN          |      |     1 |    75 |     6   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | T2   |     1 |    25 |     2   (0)| 00:00:01 |
|*  5 |    HASH JOIN         |      |     1 |    50 |     4   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T3   |     1 |    25 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| T4   |     1 |    25 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------


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


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

4. 浓密树

SQL> SELECT /*+ ordered use_hash(b) */*
  2  FROM
  3  (SELECT /*+ ordered use_hash(t1) no_merge(a) */ t1.*
  4  FROM t1,
  5  (SELECT /*+ ordered use_hash(t2 t2) */ t2.*
  6  FROM t2,
  7  t3
  8  WHERE t2.id = t3.id) a
  9  WHERE t1.id = a.id ) b,
 10  t4
 11  WHERE b.id = t4.id;


执行计划
----------------------------------------------------------
Plan hash value: 1347472701


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


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


   1 - access("T1"."ID"="T4"."ID")
   2 - access("T1"."ID"="A"."ID")
   5 - access("T2"."ID"="T3"."ID")


Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 5 (U - Unused (5))
---------------------------------------------------------------------------


   1 -  SEL$F5BB74E1
         U -  ordered / duplicate hint


   1 -  SEL$F5BB74E1 / B@SEL$1
         U -  use_hash(b)


   3 -  SEL$F5BB74E1 / T1@SEL$2
         U -  use_hash(t1)


   6 -  SEL$3 / T2@SEL$3
         U -  use_hash(t2 t2) / duplicate hint
         U -  use_hash(t2 t2)


SQL>

参考

Database SQL Tuning Guide

https://docs.oracle.com/database/121/TGSQL/tgsql_join.htm#TGSQL95345

>About Joins

>>Join Trees

https://iggyfernandez.wordpress.com/2010/11/27/sql-101-deep-left-trees-deep-right-trees-and-bushy-trees-oh-my/

>SQL 101: Deep Left Trees, Deep Right Trees, and Bushy Trees! Oh, My!

http://www.oaktable.net/content/right-deep-left-deep-and-bushy-joins

>Right Deep, Left Deep and Bushy Joins

http://oradwstories.blogspot.com/2015/07/right-deep-join-trees-and-star-schema.html

>Right-Deep Join Trees and Star Schema Queries

本文参与?腾讯云自媒体分享计划，分享自微信公众号。