oracle 连接两表,oracle 表连接
一、表连接就是在多个表之间用连接条件连接在一起，当优化器在解析带有表连接的sql时，除了会根据sql的写法来决定表连接的类型，还要确定下面三种情况才能指定出执行计划
1、表连接顺序，不管几个表的连接，sql在执行时，只能进行两表的连接，然后根据连接结果再进行下一个两表的连接，直至所有表都连接完成，连接的顺序有两层含义，一是两表连接时决定好谁是outer table (驱动表) 谁是inner table(被驱动表)，二是在多表连接的情况下，先连接哪两张表
2、表连接方法，有四种，排序合并连接，嵌套循环连接，哈希连接，笛卡尔连接，优化器在解析sql时要决定采用哪种连接
3、访问单表的方法 优化器在表表连接时，还要决定如何去读取单表中的数据，比如用全表扫还是走索引，走索引的话该如何走索引，等等
二、表连接的类型
类型分为内链接和外连接，类型决定了表连接的结果，sql的写法直接决定了类型
1、内链接:连接结果只包含那些完全满足连接条件的记录，只要sql里没写外连接的关键字，那就是内链接,内链接的三种写法，其中一是oracle专用的
SELECT T1.COL1,T1.COL2,T2.COL3 FROM T1,T2 WHERE T1.COL2=T2.COL2;
SELECT T1.COL1,T1.COL2,T2.COL3 FROM T1 JOIN T2 ON(T1.COL2=T2.COL2);
SELECT T1.COL1,
COL2,T2.COL3 FROM T1 JOIN T2 USING(COL2);
标准sql中还有一种natural join ,表示只用两个表所有的同名列一起作为连接列，不推荐使用，虽然会省去写连接列，但是会增加sql报错的风险
2、外连接，
连接结果除了包含那些完全满足连接条件的记录，还包含所有驱动表中不满足连接条件的记录，外连接分三种，左连接(left outer join),右连接(right outer join)，全连接(full outer join)
左连接，左边为驱动表
SELECT T1.COL1,t1.COL2,T2.COL3 FROM T1,t2 where t1.col2=t2.col2(+);
SELECT T1.COL1,t1.COL2,T2.COL3 FROM T1 left outer join T2 on (t1.col2=t2.col2);
SELECT T1.COL1, COL2,T2.COL3 FROM T1 left outer join T2 USING(COL2);
右连接，右边为驱动表
SELECT T1.COL1,t2.COL2,T2.COL3 FROM T1,t2 where t1.col2(+)=t2.col2;
SELECT T1.COL1, COL2,T2.COL3 FROM T1 right outer join T2 USING(COL2);
SELECT T1.COL1,t2.COL2,T2.COL3 from t1 right join t2 on(t1.col2=t2.col2);
全连接，相当于左连接 union 右连接
SELECT T1.COL1,t1.COL2,T2.COL3 from t1 full join t2 on(t1.col2=t2.col2);
3、当连接除了连接条件外，还有其他的条件时
内链接下面俩个语句结果相同
SELECT T1.COL1,t1.COL2,T2.COL3 from t1 join t2 on(t1.col2=t2.col2 and t1.col1=1);
SELECT T1.COL1,t1.COL2,T2.COL3 from t1 join t2 on(t1.col2=t2.col2) where t1.col1=1;
外连接就会发生不同，所以在外连接中除了连接限制条件外，其他的限制条件所处的文本位置 ，会影响最后的结果
SELECT T1.COL1,t1.COL2,T2.COL3 from t1 left outer join t2 on(t1.col2=t2.col2 and t1.col1=1);
SELECT T1.COL1,t1.COL2,T2.COL3 from t1 left join t2 on(t1.col2=t2.col2) where t1.col1=1;
上面两条标准sql 语句在oracle中用(+)来表示时，分别可以写成
SELECT T1.COL1,t1.COL2,T2.COL3 FROM T1,t2 where t1.col2=t2.col2(+) and
t1.col1(+)=1;
SELECT T1.COL1,t1.COL2,T2.COL3 FROM T1,t2 where t1.col2=t2.col2(+) and
t1.col1=1;
t1.col2(+)=1即可表示这个条件也在连接限制条件内。
上述第一条语句的执行计划会使用哈希外连接(hash join outer),而第二条语句会使用哈希连接(hash join),即它实际上时用等价的内链接来执行的
内链接适用的natural join 同样可以用于外连接
三、表连接的方法
1、排序合并连接(sort merge join)
两个表各自排序后合并得到结果集
执行效率不如哈希连接，但是比哈希连接适用更大的范围，因为哈希连接通常只用于等值连接条件，排序合并连接可以使用不同的连接条件，比如 < <= > => 等等。
并不适合在OLAP，当然如果可以避免排序，也可以用在OLAP，比如在两个表各自的连接列上存在索引
严格意义上排序合并连接并不存在驱动表的概念
2、嵌套循环连接(nested loops join)
两个表连接时依靠两层嵌套循环(外层循环和内层循环)来得到结果集
步骤
a、优化器根据规则决定驱动表和被驱动表，驱动表用于外层，被驱动表用于内层
b、根据谓语条件访问驱动表，得到结果集1
c、遍历结果集1同时遍历被驱动表，即先取出结果集1中的一条记录，然后根据这条记录遍历被驱动表，找出相匹配的记录，然后再取出结果集1中的第二条记录，继续遍历被驱动表，直到结果集1所有记录取完，即结果集1中有多少条记录，就会遍历循环多少次被驱动表，就会有多少次内层循环
嵌套循环连接的关键点在于结果集1的数据量，数据量少效率就会高，同时这种连接有一个其他连接没有的优点：可以实现快速响应。即可以快速返回已经连接过，且满足连接条件的记录，而不用等所有的连接全部完成
oracle 11g中加入了向量I/O(vector I/O)提高嵌套循环的性能
3、哈希连接(hash join)
两个表连接时通过哈希运算来得到结果集
_hash_join_enabled 参数 默认为true ，启用hash_join
alter session set "_hash_join_enabled"=true;
alter session set "_hash_join_enabled"=false;
use_hash hint 优先级比这个参数高
Si & Bj Sn&Bn(代表磁盘中的hash partition)
hash的优缺点和适用场景
a、hash不一定排序，或者说大部分都不会排序
b、驱动表的连接列可选择性(selectivity)尽可能好,因为这会影响hash bucket中的记录数
c、只适用于CBO，且只适用于等值连接
d、适合小表和大表做连接且结果集较大的情况，小表连接列可选择性非常好的情况下，哈希连接的执行时间相当于大表做全表扫描的时间
e、两个表连接时，做完hash后，hash table能完全在内存(PGA)中的话，哈希连接的执行效率会非常高
4、笛卡尔连接(cross join)
两个表在做连接时没有任何连接条件的表连接方法，实际上是一种特殊的合并连接，并不排序(MERGE JOIN CARTESIAN)，T1结果集为m,T2结果集为n,则笛卡尔连接后的记录数为M*N
select t1.col1,t2.col3 from t1,t2;
标准sql :
select t1.col1,t2.col3 from t1 cross join t2;
笛卡尔连接一般是不好的，往往是因为漏写了连接条件或者用了ordered hint，而sql文本上相邻的俩个表有没有直接的关联条件造成的，还有可能是统计信息不准确造成。除非是刻意的，比如为了避免多次的大表全表扫描才会使用这种连接。
四、反连接(anti join)
结果集1，结果集2，满足t1.col2=t2.col2的都会被去除，只返回不满足连接条件的值
select * from t1 where col2 not in( select col2 from t2);
select * from t1 where col2 <> all(select col2 from t2);
select * from t1 where not exists(select 1 from t2 where col2=t1.col2);
当t1,t2没有null值时 ，上面三条语句结果相同
当有null时，结果会不一样
not in 和
<> all 对null敏感，当他们后面的子查询或常量集合有null，则整个sql的执行结果就是null
not exists
对null不敏感，不影响执行结果
五、半连接(semi join)
t1,t2连接时，驱动表t1,被驱动表t2,即使t2中满足连接条件t1.col2=t2.col2有多条记录，也只会返回第一条记录，即半连接时特殊的内连接，实际具有去重的作用。当子查询展开时，oracle 通常会把where 后的条件 =any,exist,in等查询转换为对应的半连接。
select * from t1 where col2 in(select col2 from t2);
select * from t1 where col2= any(select col2 from t2);
select * from t1 where exists(select col2 from t2 where col2=t1.col2);
六、星形连接(star join)
通常用于数据仓库，既不是连接类型，也不是连接方法，他是一个事实表(fact table)与多个维度表(dimension table)之间的连接，基本上事实表的外键列对应各维度表的主键列，事实表是张大表，后面章节会详细描述这个连接