前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >执行计划之表连接1:连接树(Join Trees)

执行计划之表连接1:连接树(Join Trees)

作者头像
SQLplusDB
发布于 2022-08-19 13:47:16
发布于 2022-08-19 13:47:16
68703
代码可运行
举报
运行总次数:3
代码可运行

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

概述

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

表连接概述

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

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

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
通过JOIN ON语句,进行表连接。
例:
select emp.deptno,emp.empno
from emp join dept
on emp.deptno = dept.deptno;

2.Oracle SQL语法:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
通过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)一般包括以下特点:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
・左深树是Oracle优化器最普遍使用的连接树
・嵌套循环连接的连接树仅可能是左深树

右深树(Right Deep Join Tree)

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

右深树(Right Deep Join Tree)一般包括以下特点:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
・通常在数据仓库的环境中使用,如:用于连接事实表和多个维度表的连接(星型模式)。
・哈希连接和排序合并连接的连接树有可能是右深树。
・哈希连接为右深树时,可能会消耗大量的PGA
因为哈希连接为右深树时,会同时有多个表被做成Hash表,从而消耗过多的PGA. (Ref Old Bug 4475206

浓密树(Bushy Join Tree)

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

浓密树(Bushy Join Tree)一般包括以下特点:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
・优化器无法选择其他树形连接是才会选择浓密树
・一般当查询包含子查询或者视图时,可能会产生浓密树。如无法进行视图合并等

连接树的处理

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
・从最左端的叶节点开始处理
・左节点的处理优先级高于右节点
・左节点驱动右节点
・子节点在父节点之前进行处理
・子节点处理完获得的数据返回给父节点。

连接树的执行计划例

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
--准备表
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)

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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)

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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. 右深树

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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. 浓密树

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-06-21,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 SQL和数据库技术 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
多项式拟合
可见a商品2018年与2019年的价格走势,都反应了年初到月份价格走低趋势,4月份过后到年底价格回升走高,具有一定的周期性。
用户1733462
2019/11/19
7460
多项式拟合
基于MLxtend绘制分类模型的决策边界
继续更新机器学习扩展包MLxtend的文章。本文介绍如何使用MLxtend来绘制与分类模型相关的决策边界decision_regions。
皮大大
2024/06/06
3150
机器学习分类模型决策边界,MLxtend轻松绘制!
https://rasbt.github.io/mlxtend/user_guide/classifier/LogisticRegression/
皮大大
2024/07/12
4030
python机器学习《基于逻辑回归的预测分类》
        本文所有代码均在阿里天池实验室运行,本机的jupyter notebook也可运行。除此之外,还需要导入numpy,matplotlib,sklearn,seaborn包。每期文章前面都会有环境搭建说明。文中的讲解知识点均是按照从上往下讲解,将一些平常未接触到的知识点先将清楚,再融合自己的想法去学习探索。
用户10271432
2023/01/02
7950
python机器学习《基于逻辑回归的预测分类》
机器学习扩展包MLXtend绘制多种图形
mlxtend(machine learning extensions,机器学习扩展)是一个用于日常数据分析、机器学习建模的有用Python库。mlxtend可以用作模型的可解释性,包括统计评估、数据模式、图像提取等。
皮大大
2024/06/13
2880
贷款违约预测-Task5 模型融合
Tip:此部分为零基础入门金融风控的 Task5 模型融合部分,欢迎大家后续多多交流。 赛题:零基础入门数据挖掘 - 零基础入门金融风控之贷款违约预测 项目地址:https://github.com/datawhalechina/team-learning-data-mining/tree/master/FinancialRiskControl
致Great
2020/10/10
9720
贷款违约预测-Task5 模型融合
机器学习 pipeline 可视化
作为任何数据科学项目的一部分,数据可视化在理解更多可用数据和识别任何主要模式方面发挥着重要作用。
McGL
2020/10/30
1.2K0
机器学习 pipeline 可视化
机器学习集成学习与模型融合!
对比过kaggle比赛上面的top10的模型,除了深度学习以外的模型基本上都是集成学习的产物。集成学习可谓是上分大杀器,今天就跟大家分享在Kaggle或者阿里天池上面大杀四方的数据科学比赛利器---集成学习。
Datawhale
2020/08/20
1.1K0
机器学习集成学习与模型融合!
Python机器学习:数据拟合与广义线性回归
机器学习中的预测问题通常分为2类:回归与分类。 简单的说回归就是预测数值,而分类是给数据打上标签归类。 本文讲述如何用Python进行基本的数据拟合,以及如何对拟合结果的误差进行分析。 本例中使用一个2次函数加上随机的扰动来生成500个点,然后尝试用1、2、100次方的多项式对该数据进行拟合。 拟合的目的是使得根据训练数据能够拟合出一个多项式函数,这个函数能够很好的拟合现有数据,并且能对未知的数据进行预测。 代码如下: [python] view plaincopy import matplotlib.py
机器学习AI算法工程
2018/03/13
1.3K0
Python机器学习:数据拟合与广义线性回归
python线性回归示例
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/haluoluo211/article/details/78761466
bear_fish
2018/09/14
9020
python线性回归示例
数据挖掘机器学习[五]---汽车交易价格预测详细版本{模型融合(Stacking、Blending、Bagging和Boosting)}
 题目出自阿里天池赛题链接:零基础入门数据挖掘 - 二手车交易价格预测-天池大赛-阿里云天池
汀丶人工智能
2022/12/21
6460
数据挖掘机器学习[五]---汽车交易价格预测详细版本{模型融合(Stacking、Blending、Bagging和Boosting)}
机器学习-LR模型
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
全栈程序员站长
2022/11/04
6680
机器学习:从数据中学习的艺术
机器学习,作为人工智能的核心领域之一,正以前所未有的速度改变着我们的世界。从智能手机上的语音助手到医疗诊断中的图像识别,机器学习算法正在各个领域发挥着越来越重要的作用。本文将带你走进机器学习的世界,探讨其基本概念、常用算法以及代码实现。
远方2.0
2025/02/07
1050
【机器学习-无监督学习】聚类
  本文开始我们讲解无监督学习算法。在之前的文章中,我们给模型的任务通常是找到样本
Francek Chen
2025/01/22
1930
【机器学习-无监督学习】聚类
机器学习决策树:sklearn分类和回归
1 逻辑回归和决策树分类比较 昨天的推送机器学习:对决策树剪枝,分析了决策树需要剪枝,今天再就这个话题,借助 sklearn 进一步分析决策树分类和回归时过拟合发生后,该如何解决的问题。 上周推送的机
double
2018/04/02
1.6K0
机器学习决策树:sklearn分类和回归
【解锁未来:深入了解机器学习的核心技术与实际应用】
机器学习(Machine Learning)是人工智能(AI)的一个重要分支,旨在让计算机系统从数据中学习并自动改进。它已经在许多领域产生了深远的影响,如图像识别、语音识别、自然语言处理和推荐系统等。在这篇博客中,我们将探讨机器学习的基础概念、常用算法和实际应用。
2的n次方
2024/10/15
1900
【解锁未来:深入了解机器学习的核心技术与实际应用】
机器学习:基于逻辑回归的分类预测
尽管名为“回归”,逻辑回归实际上是一种分类模型,广泛应用于各个领域。当今社会,深度学习在许多方面已取得了显著成果,使得传统方法相形见绌。然而,正是由于这些传统方法所具有的独特优势,它们依然在各个领域发挥着重要作用。
用户3578099
2023/09/01
4570
机器学习:基于逻辑回归的分类预测
sklearn库的学习
网上有很多关于sklearn的学习教程,大部分都是简单的讲清楚某一个方面。其实最好的教程就是官方文档(http://scikit-learn.org/stable/),但是官方文档讲述的太过于详细,同时很多人对官方文档的理解和结构认识上都不能很好的把握。我写这篇文章的目的是想用一篇文章讲清楚整个sklearn库,我会讲清楚怎么样用这个库,而不是讲清楚每一个知识点。(授人以鱼不如授人以渔)(本文很多都是从实践的角度出发,也仅仅只代表我个人的认识) 本篇文章主要从两个方面出发:1,介绍sklearn官方文档的类容和结构;2,从机器学习重要步骤出发讲清楚sklearn的使用方法。
全栈程序员站长
2022/11/02
4170
sklearn库的学习
小姐姐带你一起学:如何用Python实现7种机器学习算法(附代码)
2018 区块链技术及应用峰会(BTA)·中国 倒计时 1 天 2018,想要follow最火的区块链技术?你还差一场严谨纯粹的技术交流会——2018区块链技术及应用峰会(BTA)·中国将于2018年3月30-31日登陆北京喜来登长城饭店。追求专业性?你要的这里全都有:当超强嘉宾阵容遇上业界同好的脑洞大联欢,1+1=无限可能,目前门票预购火热进行中。 活动详情: http://dwz.cn/7FI1Ch 编译 | 林椿眄 出品 | 人工智能头条(公众号ID:AI_Thinker) 【AI科技大本营导读】P
用户1737318
2018/06/05
1.7K0
机器学习算法决策树
决策树是一种常见的分类模型,在金融风控、医疗辅助诊断等诸多行业具有较为广泛的应用。决策树的核心思想是基于树结构对数据进行划分,这种思想是人类处理问题时的本能方法。例如在婚恋市场中,女方通常会先询问男方是否有房产,如果有房产再了解是否有车产,如果有车产再看是否有稳定工作……最后得出是否要深入了解的判断。
@小森
2024/03/15
1170
机器学习算法决策树
推荐阅读
相关推荐
多项式拟合
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档