现在的位置: 首页 > 数据库 > 正文

Oracle表联结方法及原理介绍

2014年11月09日 数据库 ⁄ 共 3165字 暂无评论 ⁄ 阅读 1,379 次
文章目录

如果查询涉及多张表,在优化器确定了每个表最恰当的访问方法后,下一步就是确定将这些表联结起来的最佳方法以及最恰当的顺序。表之间的关系通过WHERE子句中的条件来定义,若未定义则会产生笛卡尔积。

联结的方法有:嵌套循环联结、排序-合并联结、散列联结及笛卡尔联结

每个联结方法都会选择一对表,所访问的第一张表通常被称为驱动表(the driving table),访问的第二张表则被称为内层表或被驱表(inner或driven-to table)。

优化器会根据WHERE子句筛选后得到的表行数进行估算,大小(块、数据行及字节)最小的表通常被作为驱动表。

通俗来讲,就是“小表驱动大表”。在FROM子句中,最小的表放在最后,作为驱动表来使用。“小表驱动大表”在两表都没有索引时常见,可能你跟我一样认为小驱大或大驱小没有差别。这里为了方便理解举个例子,但不一定正确,如:T1表有10行,T2表有10000行,1个块可以放10行数据。那么,以T1为驱动,则T1扫描1个块,内层T2扫描10次,共扫描块为(1+1000*10=10001);以T2为驱动,则T2扫描1000个块,内层T1扫描10000次,共扫描块为(1000+1*10000=11000)。所以,你看到了,小表驱动大表扫描块数更少。也就是说,表联结时循环块是固定的,主要差别在于扫描驱动表的块数。

有两种情况可能驱动表并非最小的表:

  • 当优化器可以确定联结的列在其中一张表基于UNIQUE或PRIMARY KEY约束时,即存在索引时,没有索引的表将被作为驱动表。
  • 当使用外联结时,外联结的表必须放在所联结表的后面。

嵌套循环联结

如果结果集大小有限并且联结条件列在其中一表上是索引时采用这种方法最高效。嵌套循环联结的运算成本主要是读取外层表(驱动表)中的每一行并将其与所匹配的内层表中的行联结所需要的成本(这个不好理解,还是理解我上面举的例子吧)。

当数据行经过外层条件筛选并被确认匹配后,这些行就会逐个进入到内层循环。然后基于联结列进行逐行检查是否与被联结表中的某一行相匹配,如果匹配就会被传递到查询计划的下一步或者如果没有更多步骤直接被包含在最终结果集中。

这种联结的强大之处在于使用的内存非常少,因为数据集一次只加工一行,所需要的开支也非常小。

在执行计划中NESTED LOOPS表明使用了嵌套循环联结。

下面举例说明当联结列在基本一表是索引时另一表作为驱动表的情形:

select empno,ename,dname,loc

from emp,dept

where emp.deptno = dept.deptno;

在本例中,尽管dept在FROM子句的最后,优化器也会选择emp将作为驱动表(外层表),dept作为被驱表(内层表)。首先对emp表进行全表扫描,所有块通过多块读方式读出,再逐行访问,并将联结列(deptno)传递给内层循环针对dept表的查询。对于内联结,每一行在dept表的deptno列有匹配值的数据行都将被返回。对于外联结,emp表的每一行都将被返回,dept表中无法匹配的列将用NULL值来填充。

优化器之所以不选择dept表(即小又在FROM子句最后)作为驱动表,原因如下:

emp表在deptno列上没有索引,访问它的唯一方法就是全表扫描。如果将dept表选为驱动表,对于dept表中的每一行都要在emp表中进行全表扫描。但若使用emp表作为驱动表,只需要对emp进行一次全表扫描,对于dept表则采用deptno上的索引进行索引扫描。针对索引而言,deptno又正好是主键列,所以采用INDEX UNIQUE SCAN速度极快。

当然,我们也可以通过hint提示强制优化器使用某张表为驱动表,语法如下:

select /*+ ordered use_nl(dept emp) */ empno,ename,dname,loc

from emp,dept

where emp.deptno = dept.deptno;

排序-合并联结

排序-合并联结独立地读取需要联结的两张表,分别对表中数据行按联结列进行排序,然后对排序后的行集进行合并。当然,在数据行排序前会经过WHERE子句筛选。

使用这种联结方式对排序的开销非常大,尤其是内存不足而使用临时磁盘空间时。但一旦数据行排序完成,合并的过程是非常快的。

在合并时,数据库轮流操作两个列表,比较最上面的数据行,丢弃在排序队列中比另一个列表中的最上面的行出现得早的数据行,并只返回匹配的行。

在执行计划中,MERGE JOIN表明使用了排序-合并联结。

在对两个表进行排序时,其实又加到了之前讲过的数据访问方式的选择。一般而言,在联结列在某表中正好是索引时,则可通过INDEX FULL SCAN来扫描,这样将避免排序操作。没有索引的表则只能通过全表扫描,然后进行排序了。

这种联结方式适用于:

  • 数据筛选条件有限并返回有限数据行的查询;
  • 没有可用的更直接访问数据的索引;
  • 在条件为非等式的时候,如谓语有between等;
  • 如果数据行源非常大,这种联结方式可能是唯一可行的选择;

同样,我们可以通过hint提示强制使用这种联结方式,如:

select /*+ ordered */ empno,ename,dname,loc

from emp,dept

where emp.deptno = dept.deptno;

散列联结

前面讲过,排序-合并联结用来处理特定的非等式条件,而散列联结则只有在等值联结时才能进行。散列联结与排序-合并联结类似,建立散列表所需要的数据块被读取,然后剩下的工作将会针对放在内存或临时磁盘空间的散列数据来进行。散列联结具体工作方式如下:

首先,两表都经过WHERE子句筛选得到行数据。然后基于表和索引的统计信息,被确定为返回最少行数的表被完全散列化(对联结列运用hash函数)到内存中。这个散列表包含了原表的所有数据行并被基于联结键的散列值(hash值)的随机函数载入到“散列桶”中。只要有足够的内存空间,这个散列表将一直放在内存中。如果没有足够的内存,散列表将会被写入临时磁盘空间。

其次,读取另一张较大的表并对联结键应用散列函数,然后利用得到的散列值跟内存中散列表进行匹配以寻找存放有相应行数据的“散列桶”。如果匹配成功,则返回这一行数据,否则丢弃。较大的表只读取一次,并检查其中的每一行来匹配。这与嵌套循环联结不同之处在于此处内层表被多次读取(内层表被多次读取的不应该是NESTED LOOP吗,而HASH JOIN好像才是内层表只被读一次吧)。

在执行计划中,散列联结用HASH JOIN来表示。

在散列联结的执行计划中,较小的散列表列在前面而探测表列在后面。决定哪个表最小的不仅取决于数据行数还取决于这些行的大小,因为整个行都必须要存放在散列表中。

当数据行源较大并且结果集也较大的情况下将更倾向于考虑散列联结,或者如果要联结的其中一张表确定总是返回同一数据行源,也可能会选用散列联结因为这样仅访问一次这张表。如果在这种情况下选用嵌套循环联结,这个数据行源就会被一遍一遍地访问,需要比单独访问一次多做很多工作。最后,如果较小的表可以放到内存中,散列联结也会很受欢迎。

笛卡尔联结

笛卡尔联结发生在当一张表中的所有行与另一张表中的所有行联结的时候。

在执行计算中,MERGE JOIN CARTESIAN表明使用笛卡尔联结。

笛卡尔联结可能会导致得到很多重复行的结果集,此时若使用distinct虽然可以去除重复行,但代价极高,需要先排序再却重。所以我们最好避免产生笛卡尔积。

外联结

外联结需要外联结表作为驱动表,这意味着有可能不能选用更加优化的联结执行顺序。因此,使用外联结要格外小心,因为它的选用有可能会影响到整个执行计划的性能。

声明:该笔记转载自《Oracle SQL高级编程》,仅用于个人学习,若侵犯到您的版权敬请告知!

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

给我留言

留言无头像?