oracle关联表嵌套数量,Oracle 表之间的连接方式–嵌套循环(nested loops)！

最新推荐文章于 2025-03-24 23:22:17 发布

努力提高做饭水平

最新推荐文章于 2025-03-24 23:22:17 发布

阅读量2.4k

点赞数

文章标签： oracle关联表嵌套数量

关于表之间的连接方式，nested loops算是非常常见的一种连接方式，一般在OLTP系统中比较常见，一般适用于关联比较小的表，效率比较高，这里说的小表，并不是整个表的大小来判断，后面再针对这个说明。

嵌套循环的原理就是从一张表中读取数据，然后访问另一张表来做匹配，这里通常是通过索引来访问，而嵌套循环之所以快还有一个原因：嵌套循环无需等到所有操作执行完毕就可以返回结果，只要是循环匹配中匹配到结果就返回。在文章刚开始的时候说到嵌套循环比较适用于关联比较小的表，这里要说明一下，这个小表，是查询条件过滤之后的数据，嵌套循环因为有驱动表的概念，所以一般小表作为驱动表，来驱动另一个来匹配数据。

这里可能有一个疑问，为什么要用小表(也就是过滤后的结果集比较小的表)来作为驱动表？不能用返回结果非常大的表作为驱动表呢？因为嵌套循环的驱动表返回多少条数据，那被驱动表就会扫描多少次。另外再次说明一下，这里的小表，分为两个概念，一个是看过滤后的返回条数，另一个就是看过滤后的表的体积，在嵌套循环中，这里指的是过滤后的返回条数，因为这个直接决定了被驱动表的扫描次数，如果被驱动表非常大，而驱动表返回条数比较多的话，那sql就不适合走嵌套循环，原因就是上面所说。

此外在被驱动表上一般会建立索引，原因也很简单，因为被驱动表会扫描多次(取决于驱动表返回的条数)，如果没有索引，那么被驱动表会全表扫描，这个无疑是不理想的。所以在被驱动表的连接列上会建立索引，如果select查询的列不是很多的情况下，还可以考虑组合索引，这样就避免了回表带来的IO。

另外嵌套循环不会消耗PGA，驱动表就是靠近关键字的那个表，这里的关键字指的是在执行计划中看到的NESTED LOOP，嵌套循环也可以支持不等值的连接。

下面看一个简单的例子：

PgSQL

SQL>; select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------

SQL_ID 70xt266nm9y32, child number 0

-------------------------------------

select e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal<2000

Plan hash value: 351108634

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 1 | NESTED LOOPS | | 1 | 4 | 8 |00:00:00.01 | 18 |

|* 2 | TABLE ACCESS FULL | EMP | 1 | 4 | 8 |00:00:00.01 | 8 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 8 | 1 | 8 |00:00:00.01 | 10 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 8 | 1 | 8 |00:00:00.01 | 2 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("E"."SAL"<2000)

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

21 rows selected.

SQL>;select*fromtable(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------

SQL_ID70xt266nm9y32,childnumber0

-------------------------------------

selecte.ename,e.job,d.dnamefromempe,deptdwheree.deptno=d.deptnoande.sal<2000

Planhashvalue:351108634

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

|1|NESTEDLOOPS||1|4|8|00:00:00.01|18|

|*2|TABLEACCESSFULL|EMP|1|4|8|00:00:00.01|8|

|3|TABLEACCESSBYINDEXROWID|DEPT|8|1|8|00:00:00.01|10|

|*4|INDEXUNIQUESCAN|PK_DEPT|8|1|8|00:00:00.01|2|

--------------------------------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):

---------------------------------------------------

2-filter("E"."SAL"<2000)

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

21rowsselected.

在这个执行计划中，EMP表和DEPT表直接用嵌套循环连接，EMP表为驱动表，且表的数据提取方式是全表扫描，因为这个表没有索引，这里E-Rows就是CBO估算出来的返回条数4条，实际A-Rows返回条数为8条，然后被驱动表通过唯一索引扫描，A-Rows也为8，表示被驱动表被扫描了8次。通过这个简单的例子，就可以证明上面所说的结论：驱动表返回的条数要少，被驱动表的体积(segment_size)要小，走嵌套循环才高效。而且被驱动表上通过索引扫描，被驱动表的体积就会降到最小。

关于嵌套循环的优化，如果在一个执行计划中，有很多嵌套循环，一般优化需要从最先执行的那个嵌套循环开始，查看返回的条数和被驱动表的体积，查看是否适合嵌套循环，然后一步一步优化就比较简单了。

最后编辑：2014-02-13作者：Jerry