- 浏览: 74712 次
- 性别:
- 来自: 苏州
最新评论
关系型数据库并不是最早出现的数据库表现形式,之前还存在层次、网状数据库结构。随着关系型数据库的出现,以数据表的方式进行信息留存的方案迅速发展起来。关系型数据库的重要元素包括数据表和表连接,借助各种类型的表连接,可以将平铺直叙的信息加以组装拼接。
在Oracle数据库中,应对不同的连接情况和数据特征,数据库CBO会生成不同的执行计划和连接操作。本篇介绍各种连接中的一种——Nest Loop Join嵌套循环。
1、嵌套循环原理
嵌套循环Nest Loop Join是一种古老的连接方式。SQL中的连接,本质上就是将两个数据集合依据连接条件进行匹配操作。Nest Loop Join就是通过两层循环手段进行依次的匹配操作,最后返回结果集合。SQL语句只是描述出希望连接的对象和规则,而执行计划和执行操作要切实将一行行的记录进行匹配。
Nest Loop Join的操作过程很简单,很想我们最简单的排序检索算法,两层循环结构。进行连接的两个数据集合(数据表)分别称为外侧表(驱动表)和内测表(被驱动表)。首先处理外侧表中每一行符合条件的数据,之后每一行数据和内测表进行连接匹配操作。最后可以获取到结果集合。
具体来说,Nest Loop Join的执行过程如下:
ü Oracle CBO首先将一系列的连接关系,拆分为若干层的Nest Loop Join,确定连接顺序。如a.field1=b.field1 and b.field2=c.field2,就可以组织成表A和表B先进行nest loop join操作,之后操作的结果集合再与数据表C进行nest loop join操作。所以,我们查看到的连接操作,通常都是分层次的;
ü 在确定每次Nest Loop Join的两端对象之后,确定外侧连接表和内侧连接表。将外侧连接表作为连接驱动表,根据SQL中对驱动表的连接条件,进行筛选。最后获取到驱动表数据集合;
ü 从驱动表每条记录入手,检索内侧表记录,获取符合连接条件的记录。形成连接行;
注意:此处有两个需要注意的问题。其一是驱动表的确定。另一个就是检索内侧表的方法。这两个问题在CBO时代的回答都是成本问题,Oracle通过成本试算获取到。对Nest Loop Join而言,条件列、连接列上的索引是会很大程度上影响执行计划的。
下面是一个SQL语句的执行计划,由于CBO操作的复杂性,本SQL使用hint来进行强制的Nest Loop路径。
SQL> create table tabs as select * from dba_tables;
Table created
SQL> create table cols as select owner,table_name, column_name, data_type from dba_tab_cols;
Table created
SQL> create index idx_tabs_owner on tabs(owner);
Index created
SQL> create index idx_cols_name on cols(table_name);
Index created
SQL> set linesize 10000;
SQL> set pagesize 1000;
SQL> explain plan for select /*+use_nl(tabs,cols) */* from tabs, cols where tabs.table_name=cols.tab
le_name and tabs.owner='SCOTT';
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2834620917
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1355 | 381K| 360 (0)| 00:00:05 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1355 | 381K| 360 (0)| 00:00:05 |
| 3 | TABLE ACCESS BY INDEX ROWID| TABS | 117 | 28314 | 9 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_TABS_OWNER | 117 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_COLS_NAME | 12 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | COLS | 12 | 552 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TABS"."OWNER"='SCOTT')
5 - access("TABS"."TABLE_NAME"="COLS"."TABLE_NAME")
已选择19行。
在执行计划中,伴随着两个Nest Loop Join。首先,通过条件owner=’SCOTT’,检索索引idx_tabs_owner,获取符合条件的rowid列表。之后利用rowid从tabs表中取出结果集合。这个集合就成为第一层Nest Loop Join的外侧表(Outer)。
第一层Nest Loop Join的两端外侧表是TABS结果集合,内侧表(集合)则是COLS数据表对应的索引IDX_COLS_NAME,进行匹配的条件是table_name相等。第一层Nest Loop Join的结果集合是TABS所有符合条件行字段,外加上对应COLS数据表的rowid。
第二层Nest Loop Join就是通过获取到的COLS rowid找到COLS记录的全部内容。
2、Nest Loop Join检索图示
下面通过一张示意,表达在没有连接列索引的情况下,Nest Loop Join的工作方式。
在没有索引的情况下,首先Oracle会检索驱动表外侧表(全表扫描),获取到符合外侧表单独条件的记录行集合(Row1和Row2)。
之后针对row1和row2,分别对inner表进行全表匹配查询,就是对每个outer的结果行,要进行inner表的所有块查询。最后发现符合条件的row3和row4,将结果返回。
通过图示,我们也可以发现Nest Loop Join的一个致命缺陷:存在大量的随机读。为一个outer行,就需要进行inner表的全表扫描。如果inner表很庞大,那么这个执行计划效率可想而知。
3、索引优化与Nest Loop Join
在目前的Oracle执行计划中,如果inner和outer表均没有索引等优化方式,而且不包含那些很复杂的连接对应条件,出现Nest Loop Join的机会还是很低的。因为Oracle CBO会选择其他替代执行计划(如Hash Join)来参与执行计划。
在条件列,特别是连接条件列上添加索引,可以大幅度的减少Nest Loop Join的随机读。见下图示意:
如果在inner表(或者outer)的连接条件列上添加索引,在进行Nest Loop Join的时候,Row1/Row2可以直接确定符合连接条件的Inner Table数据行对应的rowid。不需要直接对inner table进行检索,就可以获取到rowid了。由于索引对应的体积要远远小于Inner Table,所以进行的块读取要少很多。
结论:如果确定需要使用嵌套循环Nest Loop Join,那么最好考虑保证连接列上能存在索引对象。这样可以很大程度上提高Nest Loop Join的连接效率。
发表评论
-
Oracle的schema、方案、模式、用户
2012-12-07 12:11 1024今天才对这几个概念 ... -
DBMS_STATS.GATHER_TABLE_STATS详解
2012-08-15 15:12 1679作用:DBMS_STATS.GATHER_TABLE_S ... -
Oracle表连接操作——Merge Sort Join(合并排序连接)
2012-08-15 15:12 3284关系型数据库并不是最早出现的数据库表现形式,之前还存在层 ... -
Oracle hash join
2012-08-15 15:13 975hash join是oracle里面一个 ... -
Oracle表连接操作——Hash Join(哈希连接)下
2012-08-15 15:13 1611Hash Join是Oracle CBO时代经常出现的一 ... -
Oracle表连接操作——Hash Join(哈希连接)上
2012-08-14 14:19 13884在Oracle中,确定连接操作类型是执行计划生成的重要方 ... -
oracle 递归查询,向上和向下遍历
2012-08-14 11:10 19228--查询全部资源信息 select * from urm_cl ... -
ORACLE中Like与Instr性能大比拼(转)
2012-08-14 11:09 912http://zhaolinjnu.blog.sohu.com ... -
SQL语句优化技术分析
2012-08-14 11:08 906一、操作符优化1、IN 操 ... -
CBO和RBO介绍
2012-08-14 11:08 850Rule Based Optimizer(RBO) ... -
Install Oracle10gXE on Ubuntu Server using apt
2012-03-19 17:22 792With Oracle Database XE in prod ... -
Merge into 详细介绍
2012-03-15 15:40 887/*Merge into 详细介绍 MERGE ... -
ora-12516 tns listener could not find available handler with matching protocol
2012-03-12 15:51 8656公司数据库安装的是 Oracle10g XE(Oracle D ... -
CUUG《Oracle 监听和TNS配置》- 笔记
2012-02-24 22:07 2157监听负责多个实例 相关文件:Listener.ora和 ... -
批处理备份oracle
2012-02-24 22:07 787@echo off @title Oracle简易数据备份工具 ... -
oracle-ORA的原理
2012-02-07 14:15 10011、在一个应用环境当中,所有的服务器使用和管理同一个数据库,目 ... -
Oracle的卸载过程
2012-02-07 12:15 844修改注册表,在开始运行中执行regedit命令,进入注册表, ... -
创建表空间文件
2012-02-07 12:12 2198--1 创建表空间文件 --1.1 创建ASIT 实体表空间 ... -
SQL触发器实例讲解
2012-02-07 12:10 807SQL触发器实例1 定义: ... -
oracle 10g 默认用户名密码及解锁
2012-02-07 12:04 9471. 安装时选择的自动安装,忘了用户名和密码,导致现在试了几个 ...
相关推荐
数据库中JOIN操作的实现主要有三种:嵌套循环连接(Nested Loop Join),归并连接(Merge Join)和散列连接或者哈稀连接(Hash Join)。其中嵌套循环连接又视情况又有两种变形:块嵌套循环连接和索引嵌套循环连接。
Oracle的三种表连接方式 详细讲述• sort merge join(SMJ) • nest loop(NL) • hash join(HJ)
NEST Oracle 3.0介绍NEST Oracle 3.0是NEST协议的坚固智能合约实现,可通过分散机制提供唯一的链上价格预言。 白皮书描述了最初的想法。贡献感谢您考虑提供源代码帮助! 我们欢迎互联网上任何人的贡献,并感谢即使...
介绍通过设计asyncio嵌套其事件循环。 这带来了一个实际的问题:在事件循环已经在运行的环境中,无法运行任务并等待结果。 尝试这样做将产生错误“ RuntimeError: This event loop is already running ”。 该...
10g、11g的优化器差别导致同样SQL生成不同的计划,从而导致返回结果顺序的不同,此种现象可以通过在11g中增加optimizer_features_enable提示设定优化器或者在session级别禁掉_nlj_batching_enabled来实现返回结果...
matlab 代码续行 Matlab-nestloop ...--最内层的循环,表示每种输入参数的原子baseFunc操作需要重复的次数,最少为1 vars --需要迭代的变量以及常量值,默认所有的数组都是需要遍历的,如果想传入一
教你怎样看懂Oracle的执行计划。
hash join/merge join/nest loop(cluster join)/index join 2:不借助第三方工具,怎样查看sql的执行计划 set autot on explain plan set statement_id = &item_id for &sql; select * from table(dbms_...
hash join/merge join/nest loop(cluster join)/index join 2:不借助第三方工具,怎样查看sql的执行计划 set autot on explain plan set statement_id = &item_id for &sql; select * from table(dbms...
NEST软件处理SAR数据的基本操作流程
你是否想知道一句sql语句如何执行,它是否走索引,是否采用不同得驱动表,是否用nestloop join,hash join…..?这一切对你是否很神秘呢?或许你会说execution plan能看到这些东西,但是你是否清楚execution plan是...
NEST协议版本3.5 介绍 发展 安装依赖项 npm install npm install hardhat npm install @nomiclabs/hardhat-waffle ethereum-waffle chai @nomiclabs/hardhat-ethers ethers npm install hardhat-contract-sizer ...
Nest是可嵌套的服务器模板库。 它使您可以创建外观类似于标准html的页面,并轻松地使用自定义标记库添加功能。 巢式范本是格式正确的html,可编译成标准php。
Nestjs基础到实战
Probabilistic fault diagnosis method based on the combination of nest-loop fisher discriminant analysis and analysis of relative changes
使用matlab使用嵌套乘法计算多项式,有效提高计算效率
前端开源库-html-nest-ruleHTML嵌套规则,HTML规范的嵌套规则实现
《音乐音频 | 语音识别与音乐流派分类》博客配套资源 包含两个文件: fma-rock-vs-hiphop.csv echonest-metrics.json