详解SQL中几种常用的表连接方式.docx

《详解SQL中几种常用的表连接方式.docx》由会员分享，可在线阅读，更多相关《详解SQL中几种常用的表连接方式.docx（11页珍藏版）》请在优知文库上搜索。

1、多表关联查询，查询优化器的执行步骤具体如下。1)访问路径：查询语句中涉及多个对象，可以基于成本确定每一个对象数据的检索方式，是选择全表扫描还是索引访问等。2)连接方式：结果集之间的关联方式，主要包括嵌套循环、哈希连接、排序合并连接等。优化器对结果集之间连接方式的判断尤为重要，因为判断结果将会直接影响SQL的执行效率。3)关联顺序：当关联对象超过2个时，首先选取两个对象关联得到的结果集，再与第三个结果集相关联。下面我们重点介绍几种常见的连接方式。Ol嵌套循环连接图1所示的是嵌套循环连接示意图。OuterInputInnerInput图1嵌套循环连接示意图嵌套循环查询流程具体如下。1)两表关联，优

2、化器首先会确定驱动表，也称外部表(outertable),另一张则是被驱动的表，也称为内部表(innertable)一般情况下，优化器会把数据量小的定义为驱动表，执行计划中，驱动表在上，被驱劫表在下。2）驱动表确认之后，会从其中提取一行有效数据，在被驱动表（内部表）中查找和匹配有效数据并提取。3）将数据返回给客户端。从以上步骤中我们可以看出，驱动表返回的行数直接影响了被驱动表的访问次数，比如，驱动表根据筛选条件最终返回了1（）行有效数据，每返回一条就会传值给被驱动表进行匹配，驱动表一共需要循环访问10次。示例代码如下：SQLSELECT/*+USE_NL(ed)*/e.firSjname,e.

3、last_name,e,salary,d.department_nameFROMhr.employeese,hr.departmentsdWHERECLdepartmenJnameIN(Marketing,Sales)ANDe.department_id=d.department_id;SQLSeleet*fromtab【e(dbms_xplan.D工SPLAY_CURSOR(rwll,null,，ALLSTATSLAST,)；SQL_ID_3nsghdhl50bx5jchildnumber0SELECT/*+USE_NL(ed)*/e.first-nameje.Iastjame,e.sala

4、ryjd.department_nameFROMhr.employeese,hr.departmentsdWHEREd.depaname工N(Marketing,Sales)ANDe,department_idd.department_idPlanhashvalue:2968905875IdIOPeratiOnINameIStartSlE-RoWSlA-RowsATimeIBuffersI0ISELECTSTATEMENT136100:00:00.01I23厂I1INESTEDLPS11936100:00:00.01I23J*2ITABLEACCESSFULL!DEPARTMENTS122j0

5、0：0。f0.01I8IPTTTABLEACCESSFULLEMPLOYEESj103600:00:00.01I15I从上述示例代码中我们可以看出，DEPARTMENTS为驱动表，StanS为1,说明只访问1次，返回2行有效数据（A-ROWS为实际返回的行数），EMPLOYEES为被驱动表，StartS为2,说明访问2次。学过C+编程的同学应该记得，C+中的嵌套循环与下面的循环有些类似：#includeintmain()Uiti,j；for(i=l;i100;i+)for(j=l;j(i/j?)pitf(dn,i)7return0;j的循环次数取决于i的取值范围，我们可以将i看作驱动表，j看作

6、被驱动表。嵌套循环连接性能主要受限于以下几点。- 驱动表的返回行数。- 被驱动表的访问方式：如果被驱动表的连接列基数小且选择性差，会导致全表扫描的访问方式，其效率变得非常低，所以我们建议连接列存在索引，且基数大选择性高。- 驱动表筛选后将返回少量数据。- 被驱动表关联字段需要有索引(连接列基数较大或选择性较高)。- 两表关联后将返回少量数据。- 适合于OLTP系统。Tips如果优化器选择了错误的连接方式，那么我们可以使用提示(hint)强制执行使用嵌套循环的连接方式：“/*+USE_NL(TABLE1,TABLE2)LEADING(TABLE1)*,其中TABLEl和TABLE2为关联表的别名

7、，LEADlNG(TABLEl)用于将TABLEl指定为驱动表。02哈希连接图2所示的是哈希连接示意图。图2哈希连接示意图Rfl哈希函数哈希函数嵌套循环连接适用于两表关联后将返回少量数据的情况，那么返回大盘数据时该采用哪种连接方式呢？答案是采用哈希连接。哈希连接的查询流程具体如下。1）两表等值关联。2）优化器将数据量小的表作为驱动表，在PGA的SQL工作区域（workareas）中，将驱动表的连接列构建成一张哈希表。3）读取大表，对连接列进行哈希运算（检查哈希表，以查找连接的行）。4）将数据返回给客户端。从以上步骤中我们可以看出，通过哈希值进行匹配的方式，更适用于两表等值关联。示例代码如下：S

8、QLSELECT/*+USE_HASH(o1)*o.customer-idj1.unit_price*l.quantlty2 FROMoe.orderso,oe.order_items13 WHEREl.order_id=o.order_id;SQLSeleCt*fromtable(dbms_xplan.D(SPLAY_CURSOR(null,null,ALLSTATSLAST1);SQL_IDcu980xxpu0mmqjchildnumber0StLECi/*+USE_HASH(o1)*o.customer_id,1.unit_price*!.quantityFROMoe.ordersofo

9、e.order_items1WHEREl.order_id=o.order_idPlanhashvalue:864676608IIdIOperationNameStartsE-RowsA-RowsA-TimeIBuffersIReadsOMemlMemUsed-MemI0ISELECTSTATEMENT|IjII665J00：00J00.04I57I5IIlI1*1HASHJOINiII665I665100:00：00.04I57I51888K1888K1531K(0)I200.04ITABLEiiACCESSFULLORDERS一5ijii1I105I105100:00:J_LITABLEA

10、CCESSFULLORDERITEMSiII665I665100:00:00.01EH0IIlI从上述示例代码中我们可以看出，ORDERS为驱动表，StartS为1,说明访问1次，返回105行有效数据（A-ROWS为实际返回的行数），ORDERTEMS为被驱动表，StartS也为1,说明仅访问1次。其中，OMem、IMem为执行所需的PGA评估值，USed-Mem为实际执行时PGA中SQL工作区域消耗的内存（即发生磁盘交换的次数），当驱动表较大，PGA的SQL工作区域无法完全容纳时，就会溢出到临时表空间产生磁盘交互，进而影响性能。哈希连接性能主要受限于以下两点。- 等值连接。- PGASQL工

11、作区域较小，且驱动表为大表时，容易出现性能问题。当同时满足以下条件时，哈希连接方式将会非常有用。- 两表等值关联后返回大量数据。- 不同于嵌套循环连接，哈希连接被驱动表的连接字段时不需要有索弓I。Tips同样，我们也可以使用提示强制执行使用哈希连接的方式：”/*+USE_HASH(TABLE1,TABLE2)LEADING(TABLE1)*,o03排序合并连接图3所示的是排序合并连接示意图。PGA,临时表空间图3排序合并连接示意图哈希连接适用于两表等值关联后返回大量数据的情况，那么非等值关联返回大量数据的情况又该采用哪种连接方式呢？答案是排序合并连接。同时满足以下条件时，排序合并连接的性能要比

12、哈希连接得好。- 两表非等值关联（、=、V=、O）O- 数据源自身有序。- 不必额外执行排序操作。排序合并连接方式中没有驱动表的概念，连接查询流程具体如下。1）两表根据关联列各自排序。2）在内存中进行合并处理。从以上实现步骤中我们可以看出，由于匹配的对象是连接列各自排序后的值，因此排序合并连接方式更适用于两表非等值关联的情形，示例代码如下：SQLSELECTo.customer_id,l.unit_price*1quantity上RoMoe.orderso,oe.order_items1WHEREJl.order_ido.orderJ,d;32233rowsselected.SQLselect

13、*fromtable(dbms_xplan.DISPLAY_CURSOR(null,null,ALLSTATSLAST);SQL_IDajyppymnhwfyfjhiIdnumber1SELECTo.customer_id,1.unit_price*1.quantityFROMoe.orderso,oe.order_items1WHERELordejido.order_idPlanhashvalue:2696431709IIdIOperationNameIStartSlE-RoWSA-RowsA-TilneIBIrfferSlOMemIlMemUSed_MemI0ISELECTSTATEMEN

14、T13223300:00:00.10I21IIIII1IMERGEJOIN1345803223300:00:00.10I21IIIII2ISORTJOINII1I105l100700:00.01I4111264111264110240(0)I3ITABLEACCESSFULLnORDERS1105厂10500?00:00.01I4IIII*4ISORTJOlNII1056653223300:00:00.05I17159392I59392I53248(3)J5JTABLEACCESSFULLIORDERTEMSl166566500：00:00.01I17IllI一从上述示例所示的执行计划中我们可以看出，ID=3的ORDERS表SIartS为1,说明访问1次，返回105行有效数据（A-RoWS为实际返回行数），ORDERTEMS表的StartS为1,说明也只访问1次，但ID=4的SORTJolN表StartS为105,说明在内存中进行了105次匹配。其中，OMem、IMem为执行排序操作所需的PGA评估值，USed-Mem为实际执行时