优化09-表连接

一、表连接介绍

表连接类型

表连接是关系型数据库关键特性，在关系型数据库中，表连接分为三类：循环嵌套连接（Nested Loops Join）、哈希连接（Hash Join）、合并排序连接（Merge Sort Join）。假设存在表A和表B,都存在ID列，通过id列连接。

Nested Loops Join：遍历A中所有id，依次拿id和表B中的id对比。

Hash Join：对A中的id做HASH运算，放入多个HASH Bucket中，根据B表的id列的hash值和HASH Bucket匹配。

Merge Sort Join：对A表和B表的id列进行排序，按照排序结果进行连接。

表连接分析

表的访问次数，由执行计划的starts列来表示

  • NL连接驱动表被访问0次或1次，被驱动表被访问0次或N次，N的值取决于驱动表的返回行数。
  • HASH连接，驱动表和被驱动表都是被访问0次或1次，大部分场景是驱动表和被驱动表都访问1次。
  • MS连接，驱动表和被驱动表都是被访问0次或1次，大部分场景是驱动表和被驱动表都访问1次。

驱动顺序

  • NL连接的性能与驱动顺序有关，一般小表做驱动表，性能更好。
  • HASH连接的性能和驱动顺序有关，一般小表做驱动表。
  • MS连接的性能和驱动顺序无关。

是否排序

  • NL连接不排序、不多余消耗内存
  • HASH连接不排序，但是hash area多消耗内存
  • MS连接排序，消耗sort area内存

使用限制

  • NL连接支持各种写法，无限制。
  • HASH连接支持等值连接，不支持>、<、like、<>等。
  • MS连接不支持>、<、like、<>等

适用场景

  • NL连接一般适用于OLTP系统
  • HASH连接、MS连接适用于OLAP系统

二、表连接测试

创建测试数据

--创建表T1和T2
CREATE TABLE t1 (
id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000));CREATE TABLE t2 (
id NUMBER NOT NULL,
t1_id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000));--插入测试数据
execute dbms_random.seed(0);
INSERT INTO t1
SELECT  rownum,  rownum, dbms_random.string('a', 50)
FROM dual
CONNECT BY level <= 100
ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
ORDER BY dbms_random.random;
COMMIT;--查看数据量 
SQL> select count(*) from t1;COUNT(*)
----------100
SQL> select count(*) from t2;COUNT(*)
----------100000

NL连接优化实验

--  两个表无索引执行计划
alter session set statistics_level=all;
select * from t1,t2 where t1.id=t2.t1_id and t1.n=19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.02 |    1016 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.02 |    1016 |  1000K|  1000K|  407K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    103K|    100K|00:00:00.01 |    1006 |       |       |          |
------------------------------------------------------------------------------------------------------------------驱动表创建索引create index t1_n on t1(n);select * from t1,t2 where t1.id=t2.t1_id and t1.n=19;select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |00:00:00.05 |    1008 |
|*  1 |  HASH JOIN                           |      |      1 |      1 |      1 |00:00:00.05 |    1008 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN                  | T1_N |      1 |      1 |      1 |00:00:00.01 |       1 |
|   4 |   TABLE ACCESS FULL                  | T2   |      1 |    103K|    100K|00:00:00.02 |    1006 |
-------------------------------------------------------------------------------------------------------
--被驱动表创建索引
CREATE INDEX t2_t1_id ON t2(t1_id);
select * from t1,t2 where t1.id=t2.t1_id and t1.n=19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    |Starts| E-Rows|A-Rows|   A-Time   |Buffers|Reads |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |     1|       |     1|00:00:00.01 |      7|     4|
|   1 |  NESTED LOOPS                         |         |     1|      1|     1|00:00:00.01 |      7|     4|
|   2 |   NESTED LOOPS                        |         |     1|      1|     1|00:00:00.01 |      6|     4|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1      |     1|      1|     1|00:00:00.01 |      3|     0|
|*  4 |     INDEX RANGE SCAN                  | T1_N    |     1|      1|     1|00:00:00.01 |      2|     0|
|*  5 |    INDEX RANGE SCAN                   | T2_T1_ID|     1|      1|     1|00:00:00.01 |      3|     4|
|   6 |   TABLE ACCESS BY INDEX ROWID         | T2      |     1|      1|     1|00:00:00.01 |      1|     0|
-----------------------------------------------------------------------------------------------------------
--大表驱动小表
SELECT /*+ leading(t2) use_nl(t1) */ *2  FROM t1, t23  WHERE t1.id = t2.t1_id4  AND t1.n = 19;
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |      1 |00:00:00.11 |    1013 |
|   1 |  NESTED LOOPS                |      |      1 |      1 |      1 |00:00:00.11 |    1013 |
|   2 |   NESTED LOOPS               |      |      1 |    103K|    100K|00:00:00.07 |    1011 |
|   3 |    TABLE ACCESS FULL         | T2   |      1 |    103K|    100K|00:00:00.01 |    1006 |
|*  4 |    INDEX RANGE SCAN          | T1_N |    100K|      1 |    100K|00:00:00.04 |       5 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T1   |    100K|      1 |      1 |00:00:00.02 |       2 |
-----------------------------------------------------------------------------------------------
--尽管两个表都建立了索引，但是如果大表驱动小表，反而消耗更大。
--总结：
--驱动表和被驱动表考虑创建索引
--确保小表驱动大表

HASH连接优化实验

--删除索引drop index t1_n;drop index t2_t1_id;select * from t1,t2 where t1.id=t2.t1_id and t1.n=19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.02 |    1016 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.02 |    1016 |  1000K|  1000K|  407K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    103K|    100K|00:00:00.01 |    1006 |       |       |          |
------------------------------------------------------------------------------------------------------------------创建索引
create index idx_t1_n on t1(n);--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |      1 |        |      1 |00:00:00.06 |    1008 |       |       |          |
|*  1 |  HASH JOIN                           |          |      1 |      1 |      1 |00:00:00.06 |    1008 |  1000K|  1000K|  402K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1       |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IDX_T1_N |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   4 |   TABLE ACCESS FULL                  | T2       |      1 |    100K|    100K|00:00:00.02 |    1006 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------总结
--驱动表和被驱动表考虑创建索引
--确保小表驱动大表
--由于HASH操作需要额外内存区域（hash area),如果需要做hash运算的数据太多，则会用的临时表空间，涉及磁盘IO会大大降低性能，索引尽量保证hash运算在内存中完成。

MS连接优化实验

--Merge Sort Join两表限制条件皆无索引SQL> SELECT /*+ leading(t2) use_merge(t1)*/ *2  FROM t1, t23  WHERE t1.id = t2.t1_id4   and t1.n=19;---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |      1 |        |      1 |00:00:00.06 |    1007 |       |       |          |
|   1 |  MERGE JOIN                           |          |      1 |      1 |      1 |00:00:00.06 |    1007 |       |       |          |
|   2 |   SORT JOIN                           |          |      1 |    100K|     20 |00:00:00.06 |    1005 |  9762K|  1209K| 8677K (0)|
|   3 |    TABLE ACCESS FULL                  | T2       |      1 |    100K|    100K|00:00:00.01 |    1005 |       |       |          |
|*  4 |   SORT JOIN                           |          |     20 |      1 |      1 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1       |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  6 |     INDEX RANGE SCAN                  | IDX_T1_N |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------限制条件有索引
create index idx_t1_n on t1(n);
create index idx_t2_n on t2(n);
SQL> SELECT /*+ leading(t2) use_merge(t1)*/ *2  FROM t1, t23  WHERE t1.id = t2.t1_id4  and t1.n=195  and t2.n=12;SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |      1 |        |      0 |00:00:00.01 |       5 |       |       |          |
|   1 |  MERGE JOIN                           |          |      1 |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|   2 |   SORT JOIN                           |          |      1 |      1 |      1 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2       |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | IDX_T2_N |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  5 |   SORT JOIN                           |          |      1 |      1 |      0 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   6 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1       |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  7 |     INDEX RANGE SCAN                  | IDX_T1_N |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------连接条件创建索引
set autotrace traceonly
SQL> SELECT /*+ leading(t1) use_merge(t2)*/ *2  FROM t1, t23  WHERE t1.id = t2.t1_id;
Statistics
----------------------------------------------------------1  recursive calls0  db block gets1012  consistent gets0  physical reads0  redo size13999  bytes sent via SQL*Net to client673  bytes received via SQL*Net from client8  SQL*Net roundtrips to/from client2  sorts (memory)0  sorts (disk)100  rows processedCREATE INDEX idx_t1_id ON t1(id);
CREATE INDEX idx_t2_t1_id ON t2(t1_id);
set autotrace traceonly
SQL> SELECT /*+ leading(t1) use_merge(t2)*/ *2  FROM t1, t23  WHERE t1.id = t2.t1_id;100 rows selected.Statistics
----------------------------------------------------------1  recursive calls0  db block gets1021  consistent gets0  physical reads0  redo size13999  bytes sent via SQL*Net to client673  bytes received via SQL*Net from client8  SQL*Net roundtrips to/from client1  sorts (memory)0  sorts (disk)100  rows processed

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.pswp.cn/bicheng/83748.shtml

如若内容造成侵权/违法违规/事实不符，请联系多彩编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

Dify工作流实践—根据word需求文档编写测试用例到Excel中

Dify工作流实践—根据word需求文档编写测试用例到Excel中

前言 这篇文章依赖到的操作可查阅我之前的文章&#xff1a; dify里的大模型是怎么添加进来的&#xff1a;在Windows本地部署Dify详细操作 flask 框架app.route()函数的开发和调用&#xff1a;PythonWeb开发框架—Flask工程创建和app.route使用详解 结构化提示词的编写&…
阅读更多...
AWTK 嵌入式Linux平台实现多点触控缩放旋转以及触点丢点问题解决

AWTK 嵌入式Linux平台实现多点触控缩放旋转以及触点丢点问题解决

前言 最近涉及海图的功能交互&#xff0c;多点触摸又开始找麻烦。 在PC/Web平台awtk是通过底层的sdl2库来实现多点触摸&#xff0c;但是在嵌入式Linux平台&#xff0c;可能是考虑到性能原因&#xff0c;awtk并没有采用sdl库来做事件处理&#xff0c;而是自己实现一个awtk-lin…
阅读更多...
Diffusion Planner:扩散模型重塑自动驾驶路径规划（ICLR‘25）

Diffusion Planner:扩散模型重塑自动驾驶路径规划（ICLR‘25）

1. 概述 2025年2月14日&#xff0c;清华大学AIR智能产业研究院联合毫末智行、中科院自动化所和香港中文大学团队&#xff0c;在ICLR 2025会议上发布了Diffusion Planner——一种创新性的基于Diffusion Transformer的自动驾驶规划模型架构。该系统联合建模周车运动预测与自车行…
阅读更多...
ESP32对接巴法云实现配网

ESP32对接巴法云实现配网

目录 序言准备工作巴法云注册与使用Arduino准备 开发开始配网 序言 本文部分内容摘抄原创作者巴法云-做优秀的物联网平台 代码有部分修改并测试运行正常 巴法云支持免费用户通过开发对接实现各智能音箱设备语音控制智能家居设备&#xff0c;并有自己的App进行配网和控制&…
阅读更多...
深度学习习题3

深度学习习题3

1.训练神经网络过程中&#xff0c;损失函数在一些时期&#xff08;Epoch&#xff09;不再减小, 原因可能是&#xff1a; 1.学习率太低 2.正则参数太大 3.卡在了局部最小值 A1 and 2 B. 2 and 3 C. 1 and 3 D. 都是 2.对于分类任务&#xff0c;我们不是将神经网络中的随机权重…
阅读更多...
【EasyExcel】导出时添加页眉页脚

【EasyExcel】导出时添加页眉页脚

一、需求 使用 EasyExcel 导出时添加页眉页脚 二、添加页眉页脚的方法 通过配置WriteSheet或WriteTable对象来添加页眉和页脚。以下是具体实现步骤&#xff1a; 1. 创建自定义页眉页脚实现类 public class CustomFooterHandler implements SheetWriteHandler {private final…
阅读更多...
c++ 类型转换函数

c++ 类型转换函数

测试代码&#xff1a; void testTypeTransfer() { // 测试类型转换函数class Distance {private:int meters;public:// 类型转换函数&#xff0c;int表示转化为int类型operator int() {std::cout << "调用了类型转换函数" << endl;return meters; }Dist…
阅读更多...
Conda 基本使用命令大全

Conda 基本使用命令大全

Conda 基本使用命令大全 Conda 是一个开源的包管理和环境管理系统&#xff0c;广泛用于 Python 开发、数据科学和机器学习。以下是 最常用的 Conda 命令&#xff0c;涵盖环境管理、包安装、配置等核心操作。 1. 环境管理 创建环境 conda create --name myenv # 创…
阅读更多...
基于SpringBoot和PostGIS的OSM时空路网数据入库实践

基于SpringBoot和PostGIS的OSM时空路网数据入库实践

目录 前言 一、空间表的设计 1、属性信息 2、空间表结构设计 二、路网数据入库 1、实体类设计 2、路网数据写入 3、pgAdmin数据查询 三、总结 前言 在当今数字化时代&#xff0c;随着信息技术的飞速发展&#xff0c;地理空间数据的应用范围越来越广泛&#xff0c;尤其是…
阅读更多...
代付入账是什么意思？怎么操作？

代付入账是什么意思？怎么操作？

代付入账就是指商户委托银行通过企业银行账户向指定持卡人账户划付款项&#xff0c;款项划入指定账户即为入账。 具体操作流程如下&#xff1a; 1. 向第三方支付公司指定账户充值加款。 2. 通过操作后台提交代付银行卡信息。 3. 第三方支付公司受理业务申请。 4. 第三方审…
阅读更多...
数学复习笔记 27

数学复习笔记 27

前言 太难受了。因为一些事情。和朋友倾诉了一下&#xff0c;也没啥用&#xff0c;几年之后不知道自己再想到的时候&#xff0c;会怎么考虑呢。另外&#xff0c;笔记还是有框架一点比较好&#xff0c;这样比较有逻辑感受。不然太乱了。这篇笔记是关于线代第五章&#xff0c;特…
阅读更多...
第四十五天打卡

第四十五天打卡

知识点回顾&#xff1a; tensorboard的发展历史和原理 tensorboard的常见操作 tensorboard在cifar上的实战&#xff1a;MLP和CNN模型 效果展示如下&#xff0c;很适合拿去组会汇报撑页数&#xff1a; 作业&#xff1a;对resnet18在cifar10上采用微调策略下&#xff0c;用tensor…
阅读更多...
使用高斯朴素贝叶斯算法对鸢尾花数据集进行分类

使用高斯朴素贝叶斯算法对鸢尾花数据集进行分类

高斯朴素贝叶斯算法通常用于特征变量是连续变量&#xff0c;符合高素分布的情况。 使用高斯朴素贝叶斯算法对鸢尾花数据集进行分类 """ 使用高斯贝叶斯堆鸢尾花进行分类 """ #导入需要的库 from sklearn.datasets import load_iris from skle…
阅读更多...
【docker】Windows安装docker

【docker】Windows安装docker

环境及工具&#xff08;点击下载&#xff09; Docker Desktop Installer.exe &#xff08;windows 环境下运行docker的一款产品&#xff09; wsl_update_x64 &#xff08;Linux 内核包&#xff09; 前期准备 系统要求2&#xff1a; Windows 11&#xff1a;64 位系统&am…
阅读更多...
量化Quantization初步之--带量化(QAT)的XOR异或pyTorch版250501

量化Quantization初步之--带量化(QAT)的XOR异或pyTorch版250501

量化(Quantization)这词儿听着玄&#xff0c;经常和量化交易Quantitative Trading (量化交易)混淆。 其实机器学习(深度学习)领域的量化Quantization是和节约内存、提高运算效率相关的概念&#xff08;因大模型的普及&#xff0c;这个量化问题尤为迫切&#xff09;。 揭秘机器…
阅读更多...
【Redis】zset 类型

【Redis】zset 类型

zset 一. zset 类型介绍二. zset 命令zaddzcard、zcountzrange、zrevrange、zrangebyscorezpopmax、zpopminzrank、zrevrank、zscorezrem、zremrangebyrank、zremrangebyscorezincrby阻塞版本命令&#xff1a;bzpopmax、bzpopmin集合间操作&#xff1a;zinterstore、zunionstor…
阅读更多...
Mermaid 绘图--以企业权限视图为例

Mermaid 绘图--以企业权限视图为例

文章目录 一、示例代码二、基础结构设计2.1 组织架构树2.2 权限视图设计 三、销售数据权限系统四、关键语法技巧汇总 一、示例代码 在企业管理系统开发中&#xff0c;清晰的权限视图设计至关重要。本文将分享如何使用 Mermaid 绘制直观的企业权限关系图&#xff0c;复制以下代…
阅读更多...
[pdf、epub]300道《软件方法》强化自测题业务建模需求分析共257页（202505更新）

[pdf、epub]300道《软件方法》强化自测题业务建模需求分析共257页（202505更新）

DDD领域驱动设计批评文集 做强化自测题获得“软件方法建模师”称号 《软件方法》各章合集 在本账号CSDN资源下载&#xff0c;或者访问链接&#xff1a; http://www.umlchina.com/url/quizad.html 如果需要提取码&#xff1a;umlc 文件夹中的“300道软件方法强化自测题2025…
阅读更多...
std__map,std__unordered_map,protobuf__map之间的性能比较

std__map,std__unordered_map,protobuf__map之间的性能比较

简单比较下 std::map、std::unordered_map 和 protobuf::Map 的性能&#xff0c;主要关注在 插入、查找 和 删除 操作上的效率以及内存管理的差异。 std::map 底层实现&#xff1a;std::map 使用红黑树作为底层数据结构&#xff0c;红黑树是一种平衡二叉查找树的变体结构&…
阅读更多...
文档处理组件Aspose.Words 25.5全新发布 ：六大新功能与性能深度优化

文档处理组件Aspose.Words 25.5全新发布 ：六大新功能与性能深度优化

在数字化办公日益普及的今天&#xff0c;文档处理的效率与质量直接影响到企业的运营效率。Aspose.Words 作为业界领先的文档处理控件&#xff0c;其最新发布的 25.5 版本带来了六大新功能和多项性能优化&#xff0c;旨在为开发者和企业用户提供更强大、高效的文档处理能力。 六…
阅读更多...
最新文章

Copyright @ 2022~2023