表链接方式

来源:互联网 发布:显示器自动校准软件 编辑:程序博客网 时间:2024/03/29 03:56

表连接方式经常和表连接查询搞混,今天我来给大家理理清楚。


一、连接查询

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> select a.empno,a.ename from emp a, dept b where a.deptno =b.deptno and b.deptno=10;Execution Plan----------------------------------------------------------Plan hash value: 130586116------------------------------------------------------------------------------| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |         |     3 |   138 |     4   (0)| 00:00:01 ||   1 |  NESTED LOOPS      |         |     3 |   138 |     4   (0)| 00:00:01 ||*  2 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |    13 |     1   (0)| 00:00:01 | --上面的表为外部表,即驱动表|*  3 |   TABLE ACCESS FULL| EMP     |     3 |    99 |     3   (0)| 00:00:01 | --下面的表为内部表------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("B"."DEPTNO"=10)   3 - filter("A"."DEPTNO"=10)Note-----   - dynamic sampling used for this statementStatistics----------------------------------------------------------         48  recursive calls          0  db block gets         21  consistent gets          0  physical reads          0  redo size        659  bytes sent via SQL*Net to client        492  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          3  rows processed 

        使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。在驱动行源表(就是正在查找的记录,外部表)较小、或者内部行源表已连接的列有惟一的索引高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两个行记录源都可以使用嵌套循环连接,所以嵌套循环连接是非常灵活的。

        然而,  如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时,嵌套循环连接效率是很低的。如果驱动表的记录非常庞大时,其他的连接方法可能更加有效。 可以通过在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> select a.empno,a.ename,b.dname, a.deptno,b.deptno from emp a,dept b where a.deptno>b.deptno;17 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1407029907----------------------------------------------------------------------------| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0 | SELECT STATEMENT    |      |     3 |   165 |     8  (25)| 00:00:01 ||   1 |  MERGE JOIN         |      |     3 |   165 |     8  (25)| 00:00:01 ||   2 |   SORT JOIN         |      |     4 |    88 |     4  (25)| 00:00:01 ||   3 |    TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 ||*  4 |   SORT JOIN         |      |    14 |   462 |     4  (25)| 00:00:01 ||   5 |    TABLE ACCESS FULL| EMP  |    14 |   462 |     3   (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - access("A"."DEPTNO">"B"."DEPTNO")       filter("A"."DEPTNO">"B"."DEPTNO")Note-----   - dynamic sampling used for this statementStatistics----------------------------------------------------------          7  recursive calls          0  db block gets         31  consistent gets          0  physical reads          0  redo size       1364  bytes sent via SQL*Net to client        503  bytes received via SQL*Net from client          3  SQL*Net roundtrips to/from client          4  sorts (memory)          0  sorts (disk)         17  rows processed


可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生排序合并连接的执行计划。

SQL> select /*+ use_merge(a b) */ a.empno,a.ename,b.dname, a.deptno,b.deptno from emp a,dept b where a.deptno>b.deptno;17 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1407029907----------------------------------------------------------------------------| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0 | SELECT STATEMENT    |      |     3 |   165 |     8  (25)| 00:00:01 ||   1 |  MERGE JOIN         |      |     3 |   165 |     8  (25)| 00:00:01 ||   2 |   SORT JOIN         |      |     4 |    88 |     4  (25)| 00:00:01 ||   3 |    TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 ||*  4 |   SORT JOIN         |      |    14 |   462 |     4  (25)| 00:00:01 ||   5 |    TABLE ACCESS FULL| EMP  |    14 |   462 |     3   (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - access("A"."DEPTNO">"B"."DEPTNO")       filter("A"."DEPTNO">"B"."DEPTNO")Note-----   - dynamic sampling used for this statementStatistics----------------------------------------------------------          7  recursive calls          0  db block gets         31  consistent gets          0  physical reads          0  redo size       1364  bytes sent via SQL*Net to client        503  bytes received via SQL*Net from client          3  SQL*Net roundtrips to/from client          4  sorts (memory)          0  sorts (disk)         17  rows processed
 排序合并连接是基于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子句中的第一张表将用于建立哈希表。

SQL> select /*+ use_hash(a b)*/ a.empno,a.ename,b.dname, a.deptno,b.deptno from emp a,dept b where a.deptno=b.deptno; --这里是 /*+ use_hash(a b)*/强制hashjoin14 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 615168685---------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |    14 |   770 |     7  (15)| 00:00:01 ||*  1 |  HASH JOIN         |      |    14 |   770 |     7  (15)| 00:00:01 | --看到了吗,这里是hashjoin了哦|   2 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 ||   3 |   TABLE ACCESS FULL| EMP  |    14 |   462 |     3   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("A"."DEPTNO"="B"."DEPTNO")Note-----   - dynamic sampling used for this statementStatistics----------------------------------------------------------          7  recursive calls          0  db block gets         32  consistent gets          0  physical reads          0  redo size       1200  bytes sent via SQL*Net to client        492  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          2  sorts (memory)          0  sorts (disk)         14  rows processed



6) 索引连接

       如果一组已存在的索引包含了查询所需要的所有信息,那么优化器将在索引中有选择地生成一组哈希表。可通过范围或者快速全局扫描访问到每一个索引,而选择何种扫描方式取决于WHERE子句中的可有条件。在一张表有大量的列,而您只想访问有限的列时,这种方法非常有效。WHERE子句约束条件越多,执行速度越快。因为优化器在评估执行查询的优化路径时,将把约束条件作为选项看待。您必须在合适的列(那些满足整个查询的列)上建立索引,这样可以确保优化器将索引连接作为可选项之一。这个任务通常牵涉到在没有索引,或者以前没有建立联合索引的列上增加索引。相对于快速全局扫描,连接索引的优势在于:快速全局扫描只有一个单一索引满足整个查询;索引连接可以有多个索引满足整个查询。

SQL> select a.empno,b.deptno from emp a,dept b where a.empno=7369 and b.deptno=30;Execution Plan----------------------------------------------------------Plan hash value: 982393369------------------------------------------------------------------------------| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |         |     1 |    26 |     1   (0)| 00:00:01 ||   1 |  NESTED LOOPS      |         |     1 |    26 |     1   (0)| 00:00:01 ||*  2 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |    13 |     1   (0)| 00:00:01 ||*  3 |   INDEX UNIQUE SCAN| PK_EMP  |     1 |    13 |     0   (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("B"."DEPTNO"=30)   3 - access("A"."EMPNO"=7369)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          2  consistent gets          0  physical reads          0  redo size        580  bytes sent via SQL*Net to client        492  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed


三 几种主要表连接的比较 

类别

嵌套循环连接

排序合并连接

哈希连接

提示

USE_NL

USE_MERGE

USE_HASH

使用的条件

任何连接

主要用于不等价连接,如<、 <=、 >、 >=;

但是不包括 <>

·仅用于等价连接

相关资源

CPU、磁盘I/O

内存、临时空间

内存、临时空间

特点

当有高选择性索引或进行限制性搜索时效率比较高,能够快速返回第一次的搜索结果。

当缺乏索引或者索引条件模糊时,排序合并连接比嵌套循环有效。

当缺乏索引或者索引条件模糊时,哈希连接连接比嵌套循环有效。通常比排序合并连接快。

在数据仓库环境下,如果表的纪录数多,效率高。

 

缺点

当索引丢失或者查询条件限制不够时,效率很低;

当表的纪录数多时,效率低。

所有的表都需要排序。它为最优化的吞吐量而设计,并且在结果没有全部找到前不返回数据。

为建立哈希表,需要大量内存。第一次的结果返回较慢。




原创粉丝点击