ORACLE数据库SQL优化表连接类型.docx

1、ORACLE数据库SQL优化表连接类型一，当优化器解析含表连接的目标SQL时，它除了会根据目标SQL的SQL文本的写法来决定表连接的类型之外，还必须决定如下三件事情才能得到最终的执行计划。1，表连接顺序不管目标SQL中有多少个表做表连接，ORACLE在实际执行该SQL时都只能先两两做表连接，再依次执行这样的两两表连接过程，直到目标SQL中所有的表都已连接完毕。所以这里的表连接顺序包含两层含义：一层含义是当两个表做连接时，优化器需要决定这两个表中谁是驱动表(outer table),谁是被驱动表（inner table）;另一层含义是当多个表(超过2个表)做表连接时，优化器需要决定这些表中谁和谁

2、先做连接，然后决定这个表连接结果所在的结果集再和剩余表中哪一个再做表连接，这个两两做表连接的过程会一直持续下去，直到目标SQL中所有的表都已连接完为止。2，表连接方法在ORACLE中，两个表之间的连接方法有排序合并连接(Sort Merge)，嵌套循环连接(Nested Loops)，哈希连接(Hash Join)，笛卡尔连接(Cross Join)这四种。3，访问单表的方法对于优化器来说，仅决定表的连接顺序和表的连接方法是不够的，这不足以得到目标SQL的最终执行计划，因为优化器在对目标SQL中的各个表两两连接时，还必须决定如何去获取存储在这些表里的不同维度的数据，即优化器还要决定访问单表的方

3、法。比如在访问单表的时候，是采用全表扫描还是走索引，如果是走索引，应该是走什么样的索引（Index unique scan, index range scan, index full scan,index fast full scan, index skip scan）。二，表连接类型1，内连接(Inner Join):是指表连接的连接结果只包含哪些完全满足条件的记录。下面来看一个内连接的例子：create table t1(col1 number,col2 varchar2(10);create table t2(col2 varchar2(10),col3 varchar2(10);ins

4、ert into t1 values(1,A);insert into t1 values(2,B);insert into t1 values(3,C);insert into t2 values(A,A2);insert into t2 values(B,B2);insert into t2 values(D,D2);两个表里的数据如下：SQL select * from t1;COL1 COL2- -1 A2 B3 CSQL select * from t2;COL2 COL3- -A A2B B2D D2我们来看下如下的内连接：SQL select t1.col1,t1.col2,t2

5、.col3 from t1,t2 where t1.col2=t2.col2;COL1 COL2 COL3- - -1 A A22 B B2从上面的内连接的结果可以看出，内连接的结果确实只包含了哪些完全满足连接条件的记录。上面的写法是ORACLE自己的写法。这个和标准的SQL中的内连接写法不同，下面我们来看下标准的SQL的内连接的写法如下：SQL select t1.col1,t1.col2,t2.col3 from t1 join t2 on (t1.col2=t2.col2);COL1 COL2 COL3- - -1 A A22 B B2从上面可以看出查询出来的结果是一样的。2，外连接（O

6、uter Join）是对内连接的一种扩展，它是指表连接结果除了包含那些完全满足连接条件的记录之外还会包含驱动表(outer table)中所有不满足该连接条件的记录。标准的SQL外连接分为左连接(left outer join)，右连接(right outer join)和全连接(full outer join)这三种。他们在标准的SQL中所对应的关键字分别是left out join,right out join,full outer join。这里的Outer table就是驱动表。当做外连接的时候，驱动表中所有不满足该连接条件的记录所对应的被驱动表中的查询列均会为NULL值来填充。可以简

7、单得把全连接理解为先做左连接，再做右连接，最后对左连接和右连接的连接结果做一个UNION操作。（注意，虽然可以这么理解，但ORACLE在实际执行全连接时不会这么做）。下面来看几个外连接的例子：如下是左连接的例子：SQL select t1.col1,t1.col2,t2.col3 from t1left joint2 on (t1.col2=t2.col2);COL1 COL2 COL3- - -1 A A22 B B23 CSQL select t1.col1,t1.col2,t2.col3 from t1left outer joint2 on (t1.col2=t2.col2);COL1

8、 COL2 COL3- - -1 A A22 B B23 C注意：left outer join 可以简写成left join.从上面可以看出，做连接的执行结果确实是除了包含所有满足条件连接条件的记录外，还包含驱动表(T1)中所有不满足该连接条件的记录。同时，驱动表中所有不满足该连接条件的记录所对应的被驱动表中的查询列均以NULL值来填充。如下是右连接的例子：SQL select t1.col1,t1.col2,t2.col3 from t1 right join t2 on (t1.col2=t2.col2);COL1 COL2 COL3- - -1 A A22 B B2D2SQL sele

9、ct t1.col1,t1.col2,t2.col3 from t1 right outer join t2 on (t1.col2=t2.col2);COL1 COL2 COL3- - -1 A A22 B B2D2注意：right outer join 可以简写成right join.从上面可以看出，做连接的执行结果确实是除了包含所有满足条件连接条件的记录外，还包含驱动表(T2)中所有不满足该连接条件的记录。同时，驱动表中所有不满足该连接条件的记录所对应的被驱动表中的查询列均以NULL值来填充。如下是全连接的例子：SQL select t1.col1,t1.col2,t2.col3 fro

10、m t1 full outer join t2 on (t1.col2=t2.col2);COL1 COL2 COL3- - -1 A A22 B B23 CD2SQL select t1.col1,t1.col2,t2.col3 from t1 full join t2 on (t1.col2=t2.col2);COL1 COL2 COL3- - -1 A A22 B B23 CD2从上面可以看出，全连接的结果确实是除了包含目标表T1和目标表T2中所有满足条件的记录外，还包含了目标表T2和目标表T2中所有不满足该连接条件的记录，同时所有不满足条件的以NULL填充。3，除了带连接条件，还带上其

11、他限制条件上面的例子都是除了连接条件，没有带其他的限制条件，如果目标SQL中除了表连接条件之外还带有其他的限制条件，则目标SQL中表连接烈性和该额外限制条件在目标SQL中的SQL文本中出现的位置都可能会对最终执行结果产生影响。看如下的例子：SQL例子1SQL select t1.col1,t1.col2,t2.col3 from t1 join t2 on (t1.col2=t2.col2 and t1.col1=1);COL1 COL2 COL3- - -1 A A2SQL例子2SQL select t1.col1,t1.col2,t2.col3 from t1 join t2 on (t1

12、.col2=t2.col2) where t1.col1=1;COL1 COL2 COL3- - -1 A A2从上面例子1和例子2可以看出除了表连接条件”t1.col2=t2.col2 ”之外,还多了一个额外的限制条件t1.col1=1,对于该限制条件，一个在join on所对应的括号内，而一个在JOIN ON 所对应的括号外，虽然该限制条件在SQL文本中所处的位置不同，但因为都是内连接，所以该限制条件的位置并不会影响实际的表的连接结果。下面在看一下外连接有额外限制条件的SQL语句：SQL例子3SQL select t1.col1,t1.col2,t2.col3 from t1 left j

13、oin t2 on (t1.col2=t2.col2 and t1.col1=1);COL1 COL2 COL3- - -1 A A22 B3 CSQL例子4SQL select t1.col1,t1.col2,t2.col3 from t1 left join t2 on (t1.col2=t2.col2) where t1.col1=1;COL1 COL2 COL3- - -1 A A2由于此时是外连接，从上面的SQL例子3和SQL例子4可以看出此时的结果是不一样的了。具体来说是这样的：对于该限制条件(t1.col1=1),它在例子3的SQL文本位于left join所对应的括号内，这表示

14、该限制条件会在表T1和表T2做左连接之前就被应用在T1上。而在例子4中，限制条件(t1.col1=1)是在左连接的所对应的括号外，这表示该限制条件在表T1和表T2做完左连接后，才会被应用在表T1和表T2的连接结果集上，从上面的结果我们可以得出如下的结论：对于外连接而言，除了表连接条件之外的额外的限制条件在目标SQL的SQL文本中的所处的位置确实可能会影响该SQL的实际执行结果。对于ORACLE用自定义的关键字”(+)“来表示外连接。关键字“(+)”的位置在目标SQL连接条件中某一个表的连接列的后面，其含义是关键字“(+)”出现在那个表的连接列后面，就表明哪个表会以NULL来填充那些不满足连接条

15、件并位于该表中的查询列，此时应该以关键字“(+)”对面的表来作为外连接的驱动表，这里的关键是决定哪个表是驱动表。看如下的ORACLE的写法的例子：t2是驱动表SQL select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2(+)=t2.col2;COL1 COL2 COL3- - -1 A A22 B B2D2t1是驱动表SQL select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2(+);COL1 COL2 COL3- - -1 A A22 B B23 C下面两种写法（例子5和例子6）是一致的：SQL例子5SQL select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2(+) and t1.col1=1;COL1 COL2 COL3- - -1 A A2SQL例子6SQL select t1.col1,t1.col2,t2.col3 from t1 left join t2 on (t1.col2=t2.col2) where t1.col1=1;COL1 COL2 COL3- - -1 A A2