值得反复研读的表连接之NL(嵌套循环)方式

最新推荐文章于 2024-08-15 10:22:48 发布
最新推荐文章于 2024-08-15 10:22:48 发布
阅读量760 收藏 1
点赞数
分类专栏： SQL优化 文章标签： oracle 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011868279/article/details/127439320
版权

在多表关联的时候，一般情况下只能是两个表先关联，两表关联之后的结果再和其他表、结果集关联。

嵌套循环（NESTED LOOPS）

嵌套循环的算法：驱动表返回一行数据，通过连接列传值给被驱动表，驱动表返回多少行，被驱动表就要被扫描多少次。

如果 SQL中添加了HINT:FIRST_ROWS，在两表关联的时候，优化器更倾向于嵌套循环。

scott@orclpdb1:orclcdb> set autot trace
scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> select /*+ gather_plan_statistics use_nl(e,d) leading(e)*/
  2   e.ename, e.job, d.dname
  from emp e, dept d
  4   where e.deptno = d.deptno;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   420 |    17   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    14 |   420 |    17   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |         |    14 |   420 |    17   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | EMP     |    14 |   238 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("E"."DEPTNO"="D"."DEPTNO")

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

   3 -  SEL$1 / E@SEL$1
         U -  use_nl(e,d)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         25  consistent gets
          0  physical reads
          0  redo size
       1076  bytes sent via SQL*Net to client
        493  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

scott@orclpdb1:orclcdb>

 

declare
  cursor cur_emp is
    select ename, job, deptno from emp;
  v_dname dept.dname%type;
begin
  for x in cur_emp loop
    select dname into v_dname from dept where deptno = x.deptno;
    dbms_output.put_line(x.ename || ' ' || x.job || ' ' || v_dname);
  end loop;
end;

尽量避免游标循环里面套用SQL（原因是纯天然的嵌套循环）

尽量避免游标里面套游标。

当两表使用外部进行

[oracle@MaxwellDBA ~]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 21 11:01:24 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

sys@cdb$root:orclcdb> ALTER SESSION SET CONTAINER=ORCLPDB1;

Session altered.

sys@cdb$root:orclcdb> conn SCOTT/TIGER@ORCLPDB1;
Connected.
scott@orclpdb1:orclcdb> 
explain plan for select /*+ use_nl(d,e) leading(e) */
  2  *
from dept d
  4  left join emp e on d.deptno = e.deptno;

Explained.
scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> set linesize 200 pagesize 200
scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2022884187

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    15 |   870 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|      |    15 |   870 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     4 |   152 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   3 - filter("D"."DEPTNO"="E"."DEPTNO"(+))

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

   1 -  SEL$2BFA4EE4
         U -  leading(e)

   2 -  SEL$2BFA4EE4 / D@SEL$1
         U -  use_nl(d,e)

25 rows selected.

scott@orclpdb1:orclcdb>

[oracle@MaxwellDBA ~]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 21 11:01:24 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

sys@cdb$root:orclcdb> ALTER SESSION SET CONTAINER=ORCLPDB1;

Session altered.

sys@cdb$root:orclcdb> conn SCOTT/TIGER@ORCLPDB1;
Connected.
scott@orclpdb1:orclcdb>
explain plan for select /*+ use_nl(d,e) leading(e) */
  2  *
from dept d
  4  left join emp e on d.deptno = e.deptno;

Explained.
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> set linesize 200 pagesize 200
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2022884187

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    15 |   870 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|      |    15 |   870 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     4 |   152 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   3 - filter("D"."DEPTNO"="E"."DEPTNO"(+))

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

   1 -  SEL$2BFA4EE4
         U -  leading(e)

   2 -  SEL$2BFA4EE4 / D@SEL$1
         U -  use_nl(d,e)

25 rows selected.

scott@orclpdb1:orclcdb>

 

use_nl(d,e) 表示让两表走嵌套循环，HINT时，SQL语句中的表有别名的时候用别名，否则HINT不生效。

leading(e) 表示让emp表作为驱动表

为什么两表关联外连接时，走嵌套却无法更改驱动表呢？

因为嵌套循环需要传值，主表传值给从表之后，如果发现从表没有关联上。显示为NULL即可。

因此两表关联是外连接的时候，走嵌套循环驱动表只能固定为主表。

如果外连接中从表有过滤条件，那么此时外连接会变为内连接。

scott@orclpdb1:orclcdb> explain plan for select /*+ use_nl(d,e) leading(e) */
  2  *
from dept d
  4  left join emp e on d.deptno = e.deptno
  5  where e.sal < 3000;

Explained.

scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    12 |   696 |    15   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    12 |   696 |    15   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |         |    12 |   696 |    15   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |    12 |   456 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   3 - filter("E"."SAL"<3000)
   4 - access("D"."DEPTNO"="E"."DEPTNO")

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

   3 -  SEL$AA565B58 / E@SEL$1
         U -  use_nl(d,e)

25 rows selected.

scott@orclpdb1:orclcdb>

为什么外连接的从表有过滤条件会变成内连接？

因为外连接的从表有过滤条件已经排除了从表与主表没有关联上显示为NULL的情况。

scott@orclpdb1:orclcdb> explain plan for select /*+ use_nl(d,e) leading(e) */
  2  *
from dept d
  4  left join emp e on d.deptno = e.deptno
  5  where e.sal < 3000;

Explained.

scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    12 |   696 |    15   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    12 |   696 |    15   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |         |    12 |   696 |    15   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |    12 |   456 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   3 - filter("E"."SAL"<3000)
   4 - access("D"."DEPTNO"="E"."DEPTNO")

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

   3 -  SEL$AA565B58 / E@SEL$1
         U -  use_nl(d,e)

25 rows selected.

scott@orclpdb1:orclcdb>

Q1: 两表关联是否走NL是看两表关联之后返回的数据量多少？还是看驱动表返回的数据量多少？

若1（驱动表）：N(N很大，被驱动表)关系，此刻即便驱动表返回数据量很少，也不能走NL，因为两表关联后返回的数据量会很大。

判断两表关联是否走NL 应该查看两表关联之后返回的数据量，数据量少，走NL, 数据量大走HASH JOIN

Q2: 大表是否可以当NL驱动表？

若大表过滤后返回的数据量很少就可以当NL驱动表。

小表与大表关联，可以考虑让小表驱动大表，大表走联接列索引（如果大表有过滤条件与连接列组合起来创建组合索引），从而避免大表被全表扫描。

DB架构
关注
专栏目录
Python编程基础：第十二节 嵌套循环Nested Loops
wzy628810的博客
07-13 1093
nested loops = The "inner loop" will finish all of it's iterations before finishing one iteration of the "outer loop"
嵌套循环连接(Nested Loops Join)
viviliving的专栏
08-08 4041
嵌套循环连接(Nested Loops Join)是一种两个表在做表连接时依靠两层嵌套循环（分别为外层循环和内存循环）来得到连接结果集的表连接方法。即外层循环对应的驱动结果集有多少条记录，遍历被驱动表的内层循环就要做多少次，这就是所谓的“嵌套循环”的含义。 对于嵌套循环连接的优缺点及适用场景如下： a,如果驱动表所对应的驱动结果集的记录数较少，同时在被驱动表的连接列上又存在唯一性索引（或者在被驱动表的连接列上存在选择性好的非唯一性索引），那么此时使用嵌套循环连接的执行效率就会非常高；但如果驱动表所对应的
循环结构(四)——循环嵌套
最新发布
fzy20110826的博客
08-15 853
有不懂的地方可以si我，和我一起学习C++吧。
Oracle表联结之嵌套循环
weixin_33908217的博客
08-15 182
1.单表访问：分表，分区，建索引，全表扫描---开并行，永远把它 放内存，压缩2.多表关联，任何时刻只能是2个表关联，得到的结果集再和其他表关联。3.嵌套循环：Oracle从较小结果集(驱动表/外部表)中读取一行，然后和较大结果集(被探查表/内部表)中的所有数据逐条进行比较(嵌套循环可以用于非等值连接)，如果符合规则，就放入结果集中，然后取较小结果集的下一条数据继续进行循...
嵌套循环连接,哈希连接,排序合并连接（2015-2-4学习日记）
weixin_34109408的博客
02-04 386
==>>嵌套循环连接,哈希连接,排序合并连接-->>嵌套循环连接select * from /*+leading(t1) use_nl(t2)*/ from t1,t2where t1.id=t2.t1_idand t1.n=19;这个HINT的含义：leading（t1）表示强制先访问表t1，use_nl表示强制使用嵌套循环连接方式。表t1是驱动表...
嵌套循环连接
雅冰石的专栏
11-09 4429
这里介绍一下嵌套循环 (Nested  Loops 简称NL) 的连接方式。   嵌套循环，顾名思义就是将一个表为出发点，将该表全部记录逐条去遍历另外一张表的记录，符合条件的就是写入结果集。   www.2cto.com   例如： select a.*, b *  from EMP a, DEPT b  where a.DEPTNO = b.DEPTNO
Oracle学习笔记（二）——嵌套循环连接
biww620的博客
06-19 7737
连接方式 Oracle的表连接方式有以下三种： 嵌套循环链接（Nested Loops Join）哈希链接（Hash Join）排序合并（Merge Sort Join） 连接涉及到两个表A和B，通俗的讲嵌套循环链接相当于遍历A中的每一条记录（满足A表条件），然后再在B表中遍历记录直至找到匹配的记录，等同于两层循环。而哈希链接和排序合并，可以看作是先各自处理自身的记录（排序或ha
uboot研读笔记 | 08 – 移植uboot 2012.04到JZ2440（设置mtd分区表）
01-09
1. mtd分区规划及其作用 uboot支持各种设备之后，接下来的工作就是烧写内核、烧写文件系统，所以需要对整块Nand Flash的空间作以规划，大致分为以下四个空间即可： bootloader空间 内核参数空间 ...
文献研读：在集装箱班轮航运网络中逆转港口循环方向
02-06
此外，该文献提出了一个混合整数规划模型，嵌套了多商品网络流模型，以优化港口循环方向并最小化总成本。这个模型的实际应用证明了优化港口循环方向的有效性，对于航运公司的策略制定具有重要指导价值。 总的来说，...
论文研读与个人复现：携手并进，共探技术前沿的宝藏之旅.pdf
07-24
### 论文研读与个人复现：携手并进，共探技术前沿的宝藏之旅 #### 一、引言：为何研读论文与复现如此重要？ - **技术前沿的窗口**：学术论文代表着最前沿的技术发展和研究成果，是科研工作者们智慧的结晶。通过对...
指向语言表达：语文教材研读的基本原则与实践探索（宁大）-副本.ppt
11-20
【冯铁山诗意教育系列讲座】探讨了语文教材研读的核心原则与实践策略，强调了教师作为引导者在连接教材与学生之间的关键作用。讲座中提到了三种不同的引导方式：引介式，教师仅作为媒介引入文本，由学生自行解读；...
Lucene倒排索引简述 之索引表
Lucene/Solr解密
09-27 4235
Lucene倒排索引的核心内容，索引表，你对这部分真的熟悉了吗？那你知道FST用什么地方吗？FST又存储了什么内容呢？有什么功能呢？关于Burst-Trie，你知道Lucene是如何采用它的思想来加速Lucene搜索性能的吗？
两表连接一：嵌套循环连接
cltt980248285的博客
04-30 471
一、前言：对于一名有志于成为SQL调优的开发人员或SQL的DBA，就很有必要了解下ORACLE数据库在对两个表进行连接时的运行机制，因为再复杂的执行计划也是每次分解成两个表的连接去执行的。ORACLE数据库有常见的三种连接表的方...
oracle 多表连接全关联,两表连接一：嵌套循环连接
weixin_39559804的博客
04-02 971
一、前言：对于一名有志于成为SQL调优的开发人员或SQL的DBA，就很有必要了解下ORACLE数据库在对两个表进行连接时的运行机制，因为再复杂的执行计划也是每次分解成两个表的连接去执行的。ORACLE数据库有常见的三种连接表的方法：嵌套循环连接、合并连接、哈希连接。本文档通过实验的方法总结这三种连接的应用和影响性能的因素；二、嵌套循环连接的预备知识：本文档主要介绍嵌套循环连接，因此先了解嵌套循环连...
嵌套循环
sangmojiu的博客
04-13 801
//嵌套循环 //一个循环结构包含另一个完整的循环结构，循环结构可以任意嵌套，没有层次的限制 //但是考虑到效率和性能问题，建议嵌套的越少越好 //要求控制台输入三个班级，每个班级5名学生的成绩，计算每个班级学生成绩的平均分 //考虑使用嵌套循环实现，外层循环控制班级的个数，内层循环控制学生的人数 // Scanner input=new Scanner(System.in)...
sql a 表 若包含b表 则a 表 列显示_技术分享|Oracle表连接方式优化之嵌套循环（NESTED LOOP） ——金审平台系列技术优化实例分享（一）...
weixin_35399228的博客
01-22 391
曾若潇数据质量管理处· 编者按金融审计数据分析平台(FAD)作为我行对接国家审计署及分行特派办的重要系统，使用银行传统关系型数据库Oracle，含数据量200+T，覆盖我行30+个业务系统重要贴源数据，可谓是银行审计检查及监管报送的重要系统支撑和数据保障。面对海量增、存量数据的挑战，如何对数据库进行优化已成为一场持久命题战。后续将为大家分享金融审计数据分析平台在做性能调优时所用...
Oracle数据库中的嵌套循环连接（Nested Loops Join）核心原理解析
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
03-25 1066
总之，嵌套循环连接适用于小表驱动大表且有高效索引的情况，但在大数据量下需谨慎使用，因为它可能会造成性能瓶颈。在实际应用中，需要结合具体场景和资源消耗情况选择最合适的连接方式。Oracle数据库中的嵌套循环连接（Nested Loops Join）是一种基本的表连接算法，主要用于将两个表的数据根据连接条件进行合并。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值