表连接方式经常和表连接查询搞混,今天我来给大家理理清楚。
一、连接查询
1、链接查询可以分为以下几种:
1)相等连接 2)不等连接 3)自连接 4)内连接和外连接
2、举例说明
首先,使用连接查询的时候一定要在where中指定连接条件,不然会产生笛卡尔积,下面我们来一一举例
1)相等连接
概念:相等连接是指使用相等比较符(=)指定连接条件的连接查询
例子:查询用户和用户所在的部门
select a.ename,b.dname from emp a ,dept b where a.deptno=b.deptno;
当然也可以在后面加上条件
select a.ename,b.dname from emp a ,dept b where a.deptno=b.deptno and b.deptno=10;
2)不等连接
概念:不等连接是指在连接条件中使用出相等比较符号以外的其他比较操作符的连接查询
例子:下面以显示所有雇员的名称、工资、及其工资级别为例
select a.empno,a.ename,b.grade from emp a,salgrade b where a.sal between b.losal and b.hisal;
3)自连接
概念:自连接是指同一张表之间的连接查询,它主要作用是参照表显示上下级关系
例子:查询BLACK的经理姓名,工作
select m.ename,m.job from emp m,emp w where m.empno=w.mgr and w.ename='BLAKE';
4)内连接和外连接
二者的区别:内连接返回满足连接条件的记录;外连接则是内连接的扩展,它不仅返回满足连接的所有条件,而且还会返回不满足连接条件的记录。
a.内连接(连接默认为内连接)
内连接有三种形式
①select a.ename,b.dname from emp a ,dept b where a.deptno=b.deptno and b.deptno=10;(是不是和相等连接一样呢,我去。。。)
②select a.ename,b.dname from emp a inner join dept bon a.deptno=b.deptno and b.deptno=10;(这个是正宗的写法)
③9i以后还可以这么写,但是一般不推荐
select dname,ename from dept natural join emp;
b.外连接,分为左外连接、右外连接、全外连接。
①左外连接:使用left join选项来实现,使用是不仅返回满足连接条件的全部表,还会返回不满足连接条件的连接操作符左边的其他的行
select a.ename,b.dname from emp a left join dept bona.deptno=b.deptno and b.deptno=10;
②右外连接:使用right join选项来实现,使用是不仅返回满足连接条件的全部表,还会返回不满足连接条件的连接操作符右边的其他的行
select a.ename,b.dname from emp a right join dept bona.deptno=b.deptno and b.deptno=10;
③全外连接:使用right join选项来实现,使用是不仅返回满足连接条件的全部表,还会返回不满足连接条件的连接操作符其他的行
select a.ename,b.dname from emp a full join dept bona.deptno=b.deptno and b.deptno=10;
喝口水,上面的比较简单,下面重点的来了
二、表连接方式
表的连接方式其实是多表查询时候表连接查询时候采用的方式
1、表连接方式分类
1)ORACLE 从6的版本开始,优化器使用4种不同的表的连接方式:
a.嵌套循环连接(NESTED LOOP JOIN)
b.群集连接(CLUSTER JOIN)
c.排序合并连接(SORT MERGE JOIN)
d.笛卡尔连接 (CARTESIAN JOIN)
2)ORACLE 7.3中,新增加了
a.哈希连接(HASH JOIN)。
3)在ORACLE 8中,新增加了
b.索引连接(INDEX JOIN)。
2、让我们来逐一讲解
1)嵌套循环连接的内部处理的流程:
a)Oracle 优化器根据基于规则RBO或基于成本CBO的原则,选择两个表中的一个作为驱动表,并指定其为外部表。
b)Oracle 优化器再将另外一个表指定为内部表。
c)Oracle从外部表中读取第一行,然后和内部表中的数据逐一进行对比,所有匹配的记录放在结果集中。
d)Oracle读取外部表中的第二行,再和内部表中的数据逐一进行对比,所有匹配的记录添加到结果集中。
e)重复上述步骤,直到外部表中的所有纪录全部处理完。
f)最后产生满足要求的结果集。
通过查询SQL语句的执行计划可以看出哪个表是外部表,哪个为内部表。
使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。在驱动行源表(就是正在查找的记录,
外部表)
较小、或者内部行源表已连接的列有
惟一的索引或
高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两个行记录源都可以使用嵌套循环连接,所以嵌套循环连接是非常灵活的。
然而, 如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时,嵌套循环连接效率是很低的。如果驱动表的记录非常庞大时,其他的连接方法可能更加有效。 可以通过在SQL语句中添加HINTS(use_nl),强制ORACLE优化器产生嵌套循环连接的执行计划。
2) 群集连接(CLUSTER JOIN)
群集连接实际上是嵌套循环连接的一种特例。如果所连接的两张源表是群集中的表,即两张表属于同一个段(SEGMENT),,那么ORACLE能够使用群集连接。处理的过程是:ORACLE从第一张行源表中读取第一行,然后在第二张行源表中使用CLUSTER索引查找能够匹配到的纪录;继续上面的步骤处理行源表中的第二行,直到所有的记录全部处理完。
群集连接的效率极高,因为两个参加连接的行源表实际上处于同一个物理块上。但是,群集连接也有其限制,没有群集的两个表不可能用群集连接。所以,群集连接实际上很少使用。
3)排序合并连接(SORT MERGE JOIN)
a) 优化器判断第一个源表是否已经排序,如果已经排序,则到第3步,否则到第2步。
b) 第一个源表排序
c) 优化器判断第二个源表是否已经排序,如果已经排序,则到第5步,否则到第4步。
d) 第二个源表排序
e) 已经排过序的两个源表进行合并操作,并生成最终的结果集。
在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(所选的数据超过表记录数的5%)时,排序合并连接将比嵌套循环连更加高效。
排列合并连接需要比较大的临时内存块,以用于排序,这将导致在临时表空间占用更多的内存和磁盘I/O。
可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生排序合并连接的执行计划。
排序合并连接是基于RBO的。
4) 笛卡尔连接 (CARTESIAN JOIN)
笛卡尔连接是指在sql语句中没有写出表连接的条件,优化器把第一个表的每一条记录和第二个表的所有纪录相连接。如果第一个表的纪录数为m, 第二个表的纪录数为m,则会产生m*n条纪录数。
下面的查询,未指名连接条件,就会产生笛卡尔连接。
select a.empno,a.ename,b.dname, a.deptno,b.deptno from emp a,dept b
由于笛卡尔连接会导致性能很差的SQL,因此一般也很少用到。
5) 哈希连接
当内存能够提供足够的空间时,哈希(HASH)连接是Oracle优化器通常的选择。哈希连接中,优化器根据统计信息,首先选择两个表中的小表,在内存中建立这张表的基于连接键的哈希表;优化器再扫描表连接中的大表,将大表中的数据与哈希表进行比较,如果有相关联的数据,则将数据添加到结果集中。
当表连接中的小表能够完全cache到可用内存的时候,哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的成本。
但是,如果哈希表过大而不能全部cache到可用内存时,优化器将会把哈希表分成多个分区,再将分区逐一cache到内存中。当表的分区超过了可用内存时,分区的部分数据就会临时地写到磁盘上的临时表空间上。因此,分区的数据写磁盘时,比较大的区间(EXTENT)会提高I/O性能。ORACLE推荐的临时表空间的区间是1MB。临时表空间的区间大小由UNIFORM SIZE指定。
当哈希表构建完成后,进行下面的处理:
a) 第二个大表进行扫描
b) 如果大表不能完全cache到可用内存的时候,大表同样会分成很多分区
c) 大表的第一个分区cache到内存
d) 对大表第一个分区的数据进行扫描,并与哈希表进行比较,如果有匹配的纪录,添加到结果集里面
e) 与第一个分区一样,其它的分区也类似处理。
f) 所有的分区处理完后,ORACLE对产生的结果集进行归并,汇总,产生最终的结果。
当哈希表过大或可用内存有限,哈希表不能完全CACHE到内存。随着满足连接条件的结果集的增加,可用内存会随之下降,这时已经CACHE到内存的数据可能会重新写回到硬盘去。如果出现这种情况,系统的性能就会下降。
当连接的两个表是用等值连接并且表的数据量比较大时,优化器才可能采用哈希连接。哈希连接是基于CBO的。只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈希边连接。HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE。当使用ORDERED提示时,FROM子句中的第一张表将用于建立哈希表。
6) 索引连接
如果一组已存在的索引包含了查询所需要的所有信息,那么优化器将在索引中有选择地生成一组哈希表。可通过范围或者快速全局扫描访问到每一个索引,而选择何种扫描方式取决于WHERE子句中的可有条件。在一张表有大量的列,而您只想访问有限的列时,这种方法非常有效。WHERE子句约束条件越多,执行速度越快。因为优化器在评估执行查询的优化路径时,将把约束条件作为选项看待。您必须在合适的列(那些满足整个查询的列)上建立索引,这样可以确保优化器将索引连接作为可选项之一。这个任务通常牵涉到在没有索引,或者以前没有建立联合索引的列上增加索引。相对于快速全局扫描,连接索引的优势在于:快速全局扫描只有一个单一索引满足整个查询;索引连接可以有多个索引满足整个查询。
三 几种主要表连接的比较
类别
|
嵌套循环连接
|
排序合并连接
|
哈希连接
|
提示
|
USE_NL
|
USE_MERGE
|
USE_HASH
|
使用的条件
|
任何连接
|
主要用于不等价连接,如<、 <=、 >、 >=;
但是不包括 <>
|
·仅用于等价连接
|
相关资源
|
CPU、磁盘I/O
|
内存、临时空间
|
内存、临时空间
|
特点
|
当有高选择性索引或进行限制性搜索时效率比较高,能够快速返回第一次的搜索结果。
|
当缺乏索引或者索引条件模糊时,排序合并连接比嵌套循环有效。
|
当缺乏索引或者索引条件模糊时,哈希连接连接比嵌套循环有效。通常比排序合并连接快。
在数据仓库环境下,如果表的纪录数多,效率高。
|
缺点
|
当索引丢失或者查询条件限制不够时,效率很低;
当表的纪录数多时,效率低。
|
所有的表都需要排序。它为最优化的吞吐量而设计,并且在结果没有全部找到前不返回数据。
|
为建立哈希表,需要大量内存。第一次的结果返回较慢。
|
分享到:
相关推荐
详解Oracle多种表连接方式,对内连接、外联结均有详细描述。
文档中描述了 ,几种常用的数据库的 表连接方式。
几种常用的表连接方式.doc几种常用的表连接方式.doc几种常用的表连接方式.doc几种常用的表连接方式.doc几种常用的表连接方式.doc几种常用的表连接方式.doc几种常用的表连接方式.doc
Oracle的三种表连接方式 详细讲述• sort merge join(SMJ) • nest loop(NL) • hash join(HJ)
Oracle+表连接方式(内连接-外连接-自连接) 详细介绍的连接的类型及应用实例,一份值得看的数据库资料,强列建义下载
数据仓库技术是目前已知的比较成熟和被广泛采用的解决方案,用于整和电信运营企业内部所有分散...本文着重分析ORACLE SQL优化中对于系统性能影响极大的表连接方式、特点、适用范围,并对如何使用和优化做了详细的探讨。
Oracle+表连接方式(内连接-外连接-自连接)+详解
Oracle表连接方式,思路清晰,可以帮助初学者更好的掌握Oracle的表连接。
Oracle数据库3种主要表连接方式各自优劣对比
本文将主要从以下几个典型的例子来分析Oracle表的几种不同连接方式。
内连接 自然连接 左外连接 右外连接 笛卡尔连接 索引连接 嵌套连接
ORACLE表连接方式的分析与优化_tony.doc
从一个或多个表中检索相关的数据,大体上表与表之间的连接主要可分四种,分别为相等连接,外连接,不等连接和自连接,本文将主要从以下几个典型的例子来分析Oracle表的四种不同连接方式:
概述表与表见的关系,根据条件查详细信息
左连接:以left join左边的表为主表,在满足on条件的基础上,显示主表剩余的数据,从表中的值以null值填充 右连接: 以right join右边的表为主表,在满足on条件的基础上,显示主表剩余的数据,从表中的值以null值填充
多表连接的多种方式.sql
从一个或多个表中检索相关的数据,大体上表与表之间的连接主要可分四种,分别为相等连接,外连接,不等连接和自连接,本文将主要从以下几个典型的例子来分析Oracle表的四种连接方式
oracle表的连接方式,oracle表的连接方式
总结了一份用面向对象,mysqli方式连接MySQL数据库的代码。构造函数连接
Oracle 表的扫描方式及连接方法,全表扫描,索引扫描,索引范围扫描,索引唯一扫描