oracle 连接两表,oracle 表连接

施阳曜
2023-12-01

一、表连接就是在多个表之间用连接条件连接在一起,当优化器在解析带有表连接的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)之间的连接,基本上事实表的外键列对应各维度表的主键列,事实表是张大表,后面章节会详细描述这个连接

 类似资料: