ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle表连接操作——Nest Loop Join(嵌套循环)

Oracle表连接操作——Nest Loop Join(嵌套循环)

原创 Linux操作系统 作者:realkid4 时间:2011-06-01 22:24:35 0 删除 编辑

 

关系型数据库并不是最早出现的数据库表现形式,之前还存在层次、网状数据库结构。随着关系型数据库的出现,以数据表的方式进行信息留存的方案迅速发展起来。关系型数据库的重要元素包括数据表和表连接,借助各种类型的表连接,可以将平铺直叙的信息加以组装拼接。

 

 

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列表。之后利用rowidtabs表中取出结果集合。这个集合就成为第一层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记录的全部内容。

 

 

2Nest Loop Join检索图示

 

下面通过一张示意,表达在没有连接列索引的情况下,Nest Loop Join的工作方式。

 

 

在没有索引的情况下,首先Oracle会检索驱动表外侧表(全表扫描),获取到符合外侧表单独条件的记录行集合(Row1Row2)。

 

之后针对row1row2,分别对inner表进行全表匹配查询,就是对每个outer的结果行,要进行inner表的所有块查询。最后发现符合条件的row3row4,将结果返回。

 

 

通过图示,我们也可以发现Nest Loop Join的一个致命缺陷:存在大量的随机读。为一个outer行,就需要进行inner表的全表扫描。如果inner表很庞大,那么这个执行计划效率可想而知。

 

 

3、索引优化与Nest Loop Join

 

在目前的Oracle执行计划中,如果innerouter表均没有索引等优化方式,而且不包含那些很复杂的连接对应条件,出现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的连接效率。

NestLoopJoin1.JPG

NestLoopJoin2.JPG

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-696917/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7754461