oracle左连接没用_oracle嵌套循环和多结果集嵌套循环处理优化

嵌套循环连接处理的两个数据集被称为外部循环(outer loop&＃xff0c;也就是驱动数据源&＃xff0c;driving row soulce )和内部循环〔 inner loop )。外部循环为左子节点&＃xff0c;内部循环为右子节点。当外部循环执行一次的时候&＃xff0c;内部循环需要针对外部循环返回的每条记录执行一次。

外部循环称为外表或者驱动表&＃xff0c;而内部循环称为内表或者被驱动表。

嵌套循环连接处理过程

嵌套循环连接有以下几点特征&＃xff1a;

口左子节点(外部循环)只会执行一次&＃xff0c;而右子节点(内部循环)一般会执行很多次。

口在所有数据处理完之前&＃xff0c;就可以返回结果集的第一条记录。

口可以有效利用索引来处理限制条件与连接条件。

口支持所有类型的连接。

下面是个简单的两表嵌套循环连接的执行计划。同时也描述了如何使用提示1eading 与use_nl来强制使用嵌套循环连接。Leading提示要求先访问表tl &＃xff0c;也就是&＃xff0c;它指定了哪张表作为外部循环表使用。use_nl提示指定了具体使用哪种连接方法来将内部循环返回的数据(表t2 )与表t1 连接起来。有必要指出的是&＃xff1a;usenl 提示并没有引用表t1 。

SELECT /* &＃43; leading (t1) use_nl(t2) full(t1) full(t2)*/ * FROM t1 ,t2 WHERE tl.id &＃61;t2.id AND tl.n&＃61;19

NESTED LOOPS操作是一种相关联合型操作。它意味着是第二个子节点(内部循环)的执行是由第一个子节点(外部循环)控制的。在这个例子中&＃xff0c;执行计划的执行过程可以总结如下。

口 通过全表扫描访问表t1 的所有记录&＃xff0c;访问中应用限制条件n &＃61; 19 过滤数据口

口 前面一个步骤返回多少条记录&＃xff0c;就在表t2 上执行多少次全表扫描。

无疑&＃xff0c;当操作2 返回的记录超过1 条时&＃xff0c;这种执行计划就不是很有效了&＃xff0c;因此&＃xff0c;查询优化器几乎从来不会选择它。正是因为这个原因&＃xff0c;才必须指定两个访问提示(full)来强制查询优化器使用这个执行计划。另一方面&＃xff0c;如果外部循环只返回一条记录&＃xff0c;并且内部循环的选择性也很高&＃xff0c;对表t2 进行全表扫描可能也不错。为了展示这一点&＃xff0c;下面我们在表t1 的n 字段卜创建一个唯一索引&＃xff1a;

CREATE UNIQUE INDEX t1_n ON t1(n)

有了这个索引以后&＃xff0c;前面的语句就以下面的执行计一划执行了。由于操作3 (INDEX UNIQUE SCAN)的缘故&＃xff0c;可以确保内部循环只会被执行一次。

SELECT /* &＃43; leading (t1) use_nl(t2) index(t1) full(t2)*/ * FROM t1 ,t2 WHERE tl.id &＃61;t2.id AND tl.n&＃61;19

如果内部循环的选择性很低&＃xff0c;对内部循环适应索引扫描就是一个较好的选择。由于嵌套循环连接是相关联合型操作&＃xff0c;对内部循环来讲&＃xff0c;这个地方也可能利用到连接条件。例如&＃xff0c;在下面的执行计划中&＃xff0c;操作5就是利用操作3的返回值t1.id做了索引查找。

SELECT /* &＃43; leading (t1) use_nl(t2) index(t1) index(t2)*/ * FROM t1 ,t2 WHERE tl.id &＃61;t2.id AND tl.n&＃61;19

通过建立2个索引&＃xff0c;使得性能得到最好的优化。嵌套循环适合2个选择性低的效率数据量的选择&＃xff0c;到数据量大的时候优化器会默认选择hash join 连接&＃xff0c;除非我们手动选择提示&＃xff0c;并且通过索引来达到很好的效率。

总的来讲&＃xff0c;如果内部循环会执行多次&＃xff0c;只有具有好的选择性的访问路径以及导致比较少的逻辑读的路径才有意义

下面的执行计划是个典型的用嵌套循环连接实现的左深树。请注意各个表是如何通过索引进行访问的ordered 提示要求这些表按它们在FROM 子句中的顺序进行访问。use_nl提示要求后面的表与第一张表(或者前面一个操作的结果集)进行连接的时候使用嵌套循环连接。

SELECT /* &＃43; ordered use_nl(t2 t3 t4)*/ t1.*,t2.*,t3.*,t4.* FROM t1,t2,t3,t4 WHERE t1.id &＃61; t2.t1_id AND t2.id &＃61;t3.t2_idAND t3.id &＃61;t4.t3_id AND t1.n &＃61; 19

这种执行计划的执行流程可以总结如下(不考虑使用行预取的情况):

( 1)当读取第一条记录的时候(也就是说&＃xff0c;不是当这条语句解析或者执行的时候)&＃xff0c;在应用了表t1 上的限制条件tl.n &＃61; 19 并取得第一条记录的时候&＃xff0c;处理过程就开始了。( 2)表t2 基于表t1 找到的结果进行查找。数据库引擎会利用连接条件tl.id &＃61; t2.t1_id 来访问表t2。事实上&＃xff0e;表t2 上没有任何限制条件。只有第一条满足连接条件的记录才会返回给上级操作。( 3)表t3 基于表t2 找到的结果进行查找数据库引擎也是利用连接条件t2.id &＃61;t3.t2_id 来访问表t3 。只有第一条满足连接条件的记录才会返回给上一级操作。( 4)表t4 基于表t3 找到的结果进行查找。这儿也一样&＃xff0c;数据库引擎也是利用连接条件t3.id &＃61;t4.t3_id 来访问表t4 。满足条件的第一条记录会立即返回给客户端。( 5)后续的操作也是按照第一条记录样的行为来进行的。很明显&＃xff0c;执行过程是在下一次匹配(也可能是表t4 里面匹配的第二条记录&＃xff0c;如果有的话)的位置开始的。需要特别强调的是&＃xff0c;满足条件的记录会在第一时间被返回给客户端。换句话讲&＃xff0c;就是完全没有必要在返回第条记录之前完成整个执行过程。块预取

在一般情况下&＃xff0c;当缓存没有命中的时候&＃xff0c;基于单块处理(例如&＃xff0c;rowid 访问、索引范围扫描)的访问路径会导致一个单块的物理读。对于嵌套循环连接来讲&＃xff0c;特别是当有很多行数据需要处理的时候&＃xff0c;效率会比较差。实际上很多时候&＃xff0c;嵌套循环连接也会使用多个单块物理读来访问多个相邻的块。数据库引擎可以利用块预取功能来提高嵌套循环连接的效率。这种优化技巧的目的是&＃xff0c;对与多个相邻的块使用一次多块物理读取&＃xff0c;来代替多次单块物理读取。块预取对于表和索引都是适用的。无法通过查看执行计划来看出数据库引擎是否使用了块预取功能。唯一能够看到的方式是&＃xff0c;查看服务器进程执行的物理读&＃xff0c;特别是与物理读相关的等待事件。

口db file sequential read 是个与单块物理读取相关的事件。因此&＃xff0c;如果这个事件出现&＃xff0c;就意味着块预取要么没有被使用&＃xff0c;要么是无法被使用(例如&＃xff0c;由于请求的块己经在高速缓存中)。

口db file scattered read 是一个与多块物理读取相关的事件。因此&＃xff0e;如果在&＃xff5e;id 访问或者索引范围扫描中看到这个等待事件&＃xff0c;就表明在使用块预取功能。

需要特别提示的是&＃xff0c;我们无法控制块预取功能的使用。如何以及是否使用块预取功能是由数据库引擎决定的。

其他可选的执行计划可以用下面的执行计划来执行嵌套循环连接。

实际上&＃xff0c;在Oracle 的最近几个版本中&＃xff0c;只有当内部循环或者外部循环是基于唯一索引扫描(index unique scan )的时候才会使用这种类型的执行计划。下面我们来看看&＃xff0c;如果在列n 上的索引t1_n 如下面这样定义(为非唯一)时会如何&＃xff1a;

CREATE INDEX t1_n ON t1(n)

当换成这个索引时&＃xff0c;下面的执行计划将被采用。请注意&＃xff0c;表t2 上的rowid 访问是处在不同的位置的&＃xff0e;在前一个执行计划&＃xff0c;它是在操作4 &＃xff0c;然而在后一个执行计划中&＃xff0c;它是在操作1 。比较特别的是&＃xff0c;row 记访问(操作1)的子操作是一个嵌套循环连接(操作2 )。从我们的角度看&＃xff0c;这两个执行计划做了同一件事。下面的这个执行计划可能是为了利用一些内部优化(比如块预取)。

在oracle 11g 中&＃xff0c;可能会使用下面的执行计划&＃xff0c;而不是之前的那个。注意&＃xff0c;虽然查询始终是一个两表连接&＃xff0c;执行计划却含有两个嵌套循环连接&＃xff01;

--性能差begin for a in (select id,name,sex,idcard from people) loop for b in (select id,name,sex,idcard from english) loop if a.idcard &＃61; b.idcard then --do something end if; end loop; end loop;end;

优化后如下&＃xff1a;

--性能优&＃xff1a;将多表合并成一个结果集&＃xff0c;避免嵌套循环begin for a in (select p.id,p.name,p.sex,p.idcard from people p,english e where p.idcard &＃61; e.idcard) loop --do something end loop;end;

关于嵌套循环方面的内容就介绍到这了&＃xff0c;后面会分享更多DBA方面内容&＃xff0c;感兴趣的朋友可以关注下 &＃xff01;