Oracle数据库表连接方式及多见用法

一个SQL语句的关联表超过两个，那么连接的顺序如何 呢?Oracle首先连接其中的两个表，产生一个结果集;然后将产生的结果集与下一个表再执行 关联;继续这个流程，直到所有的表都连接完成;最后产生所需的数据，,本文将主要从以下多个典型的例子来分析Oracle表的几种不同连接方式:
　　1. 相等连接
　　通过两个表具有相同意义的列，可以建立相等连接条件。
　　只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中。
　　例 查询员工信息以及对应的员工所在的部门信息;
SELECT　*　FROM　EMP,DEPT;　　　
SELECT 　*　FROM　EMP,DEPT　　
　WHERE　EMP.DEPTNO　=　DEPT.DEPTNO;　　
　　REM 显示工资超过2000的员工信息以及对应的员工的部门名称。
　　2. 外连接
　　对于外连接，Oracle中可以运用 “(+)”来表示，9i可以运用 LEFT/RIGHT/FULL OUTER JOIN，下面将配合实例一一介绍。除了显示匹配相等连接条件的信息之外,还显示不能匹配相等连接条件的某个表的信息。
　　外连接采用(+)来识别。
　　◆左条件(+) = 右条件;
　　代表除了显示匹配相等连接条件的信息之外,还显示右条件所在的表中不能匹配相等连接条件的信息。
　　此时也称为"右外连接".另一种表示要领是:
　　SELECT ... FROM 表1 RIGHT OUTER JOIN 表2 ON 连接条件
　　◆左条件 = 右条件(+);
　　代表除了显示匹配相等连接条件的信息之外,还显示左条件所在的表中不能匹配相等连接条件的信息。
　　此时也称为"左外连接".
　　SELECT ... FROM 表1 LEFT OUTER JOIN 表2 ON 连接条件
　　例 显示员工信息以及所对应的部门信息
　　--不能显示没有部门的员工信息
　　--不能显示没有员工的部门信息

 

--SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
　　--直接做相等连接:
　　SELECT * FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
　　REM 显示员工信息以及所对应的部门信息,显示没有员工的部门信息
--SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;
　　SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
　　REM 显示员工信息以及所对应的部门信息,显示没有部门的员工信息
　　--SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO(+);
　　SELECT * FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
　　3. 不等连接
　　两个表中的有关的两列执行 不等连接,比较符号一般为>,<,...,BETWEEN.. AND..
　　 REM SALGRADE
　　--DESC SALGRADE;
　　--SELECT * FROM SALGRADE;
　　 REM 显示员工的编号,姓名,工资,以及工资所对应的级别。
　　SELECT EMPNO,ENAME,SAL,SALGRADE.* FROM SALGRADE,EMP
　　WHERE EMP.SAL BETWEEN LOSAL AND HISAL;
　　REM 显示雇员的编号,姓名,工资,工资级别,所在部门的名称;
　　SELECT EMPNO,ENAME,SAL,GRADE,DNAME FROM EMP,DEPT,SALGRADE
　　WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.SAL BETWEEN LOSAL AND HISAL;
　　4. 自连接
　　自连接是数据库中经常要用的连接方式，运用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。下面介绍一下自连接的要领 :

将原表复制一份作为另一个表,两表做笛卡儿相等连接。
　　例 显示雇员的编号,名称,以及该雇员的经理名称
　　SELECT WORKER.ENAME,WORKER.MGR,MANAGER.EMPNO,MANAGER.ENAME FROM EMP WORKER,EMP MANAGER
　　WHERE WORKER.MGR = MANAGER.EMPNO;
　　5.哈希连接
　　当内存能够提供足够的空间时,哈希(HASH)连接是Oracle优化器通常的选择。哈希连接中，优化器根据统计信息，首先选择两个表中的小表，在内存中建立这张表的基于连接键的哈希表;优化器再扫描表连接中的大表，将大表中的数据与哈希表执行 比较，如果有有关联的数据，则将数据添加到结果集中。
　　当表连接中的小表能够完全cache到可用内存的时候，哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的成本。
　　但是，如果哈希表过大而不能全部cache到可用内存时，优化器将会把哈希表分成多个分区，再将分区逐一cache到内存中。当表的分区超过了可用内存时，分区的部分数据就会临时地写到硬盘上的临时表空间上。因此，分区的数据写硬盘时，比较大的区间(EXTENT)会提高I/O性能。ORACLE推选的临时表空间的区间是 1MB。临时表空间的区间大小由UNIFORM SIZE指定。
　　当哈希表构建完成后，执行 下面的处理：
　　1) 第二个大表执行 扫描
　　2) 如果大表不能完全cache到可用内存的时候，大表同样会分成很多分区
　　3) 大表的第一个分区cache到内存
　　4) 对大表第一个分区的数据执行 扫描，并与哈希表执行 比较，如果有匹配的纪录，添加到结果集里面
　　5) 与第一个分区一样，其它的分区也类似处理。
　　6) 所有的分区处理完后，ORACLE对产生的结果集执行 归并，汇总，产生最终的结果。
　　当哈希表过大或可用内存有限，哈希表不能完全CACHE到内存。随着满足连接条件的结果集的添加，可用内存会随之下降，这时已经CACHE到内存的数据可能会重新写回到硬盘去。如果出现这种情况，系统的性能就会下降。
　　当连接的两个表是用等值连接并且表的数据量比较大时，优化器才可能采用哈希连接。哈希连接是基于CBO的。只有在数据库原始化参数HASH_JOIN_ENABLED设为True,并且为参数 PGA_AGGREGATE_TARGET配置了一个足够大的值的时候,Oracle才会运用哈希边连接。HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当运用 HASH_AREA_SIZE。当运用 ORDERED提示时,FROM子句中的第一张表将用于建立哈希表。

 

select　a.user_name,b.dev_no　　　
from　user_info　a,　dev_info　b　　
　
where 　a.user_id　=　b.user_id;　　
　
Plan　　
　
---------------------------------------------------------- 　　
　
0　SELECT　STATEMENT　Optimizer=CHOOSE　(Cost=5　Card=82　 Bytes=3936　　
　
)　　
　
1　0　HASH　JOIN　(Cost=5　Card=82　 Bytes=3936)　　
　
2　1　TABLE　Access　(FULL)　OF　'USER_INFO'　 (Cost=2　Card=82　Bytes　　
　
=1968)　　
　
3　1　TABLE　ACCESS　(FULL) 　OF　'DEV_INFO'　(Cost=2　Card=82　Bytes=　　
　
1968)　　
　　可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生哈希连接的执行计划。
select　/*+　use_hash(a　 b)*/　a.user_name,b.dev_no　　　
　
from　user_info　a,　dev_info　b　　
　
where 　a.user_id　=　b.user_id;　
　　当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索B_树索引更加快速。