Oracle中表的连接类型
Oracle中所谓表的连接,有两种含义。一种是根据Oracle内部的连接判断方式区分的内部连接类型,这会影响到系统优化、资源占用等问题。另一种是基于查询的方式,对两张表之间建立联系,会影响到查询的结果,这里都写一下。
一、Oracle
中的表内部连接有
3类
:
1、
嵌套循环连接(
Nested Loops
)
原理:扫描一个表,每读取驱动表的一条记录,就根据索引去另一个表里面查找,所有匹配记录放在结果集中,然后再读取驱动表的下一行。没有索引一般就不会是
nested loops
。
条件:驱动表结果集不大,被驱动表连接字段要有索引。
特点:使用嵌套循环连接是从连接结果中提取第一批记录的最快速方法。
使用:
USE_NL(t1 t2)
提示来强制执行
Nested Loops
。
2、
哈希连接(
Hash Join
)
原理:优化器先扫描小表,根据连接键在内存中建立
hash
表,然后扫描大表,每得到一条记录就探测
hash
表一次,找出匹配行。
条件:两个巨大表之间的连接,或一个巨大的表一个小表之间的连接。且连接键无索引。
特点:需要较大的内存,如表太大则需要进行分区,并暂时存储至磁盘的临时段。扫描成本
=
全表扫描大表
+
分区数
*
表全表扫描小表;还需要注意的是:必须将
HASH_JOIN_ENABLED
设为
True,
并且为参数
PGA_AGGREGATE_TARGET
设置了一个足够大的值后,才可以执行
Hash Join
。
使用:
USE_HASH(t1 t2)
提示来强制执行
Hash Join
3、
排序合并连接(
Sort Merge Join
)
原理:将两个表分别进行排序,然后将两个表合并,查找出匹配的记录。
条件:行源已经被排过序的情况下使用。
特点:主要花费在两个表的全表扫描和各自的排序上。
使用:
USE_MERGE(t1 t2)
提示来强制执行
Sort Merge Join
。
总结
:当缺少有用的索引时,哈希连接比嵌套循环连接更加有效。哈希连接可能比排序合并连接更快,因为在这种情况下只有一张源表需要排序。哈希连接也可能比嵌套循环连接更快,因为处理内存中的哈希表比检索
B_Tree
索引更加迅速。
二、Oracle外部连接方式也分3类:
SQL> select * from t11;
C1 C2
---------- ----------
5 5
1 1
2 2
SQL> select * from t22;
D1 D2
---------- ----------
1 1
2 2
3 3
4 4
1、内连接(inner join)
SQL> select c1,c2,d1,d2 from t11 inner join t22 on t11.c1=t22.d1;
等效于:(可简写为join)
SQL> select c1,c2,d1,d2 from t11,t22 where t11.c1=t22.d1;
效果:
C1 C2 D1 D2
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2
2、外连接(outer join)
①左外连接
SQL> select c1,c2,d1,d2 from t11 left outer join t22 on t11.c1=t22.d1;
等效于:(可简写为left join)
SQL> select c1,c2,d1,d2 from t11,t22 where t11.c1=t22.d1(+);
效果:
C1 C2 D1 D2
---------- ---------- ---------- ----------
5 5
1 1 1 1
2 2 2 2
②右外连接
SQL> select c1,c2,d1,d2 from t11 right outer join t22 on t11.c1=t22.d1;
等效于:(可简写为right join)
SQL> select c1,c2,d1,d2 from t11,t22 where t11.c1(+)=t22.d1;
效果:
C1 C2 D1 D2
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2
4 4
3 3
③全外连接
SQL> select c1,c2,d1,d2 from t11 full outer join t22 on t11.c1=t22.d1;
效果:
C1 C2 D1 D2
---------- ---------- ---------- ----------
5 5
1 1 1 1
2 2 2 2
4 4
3 3
等效于:(可简写为full join)
SQL> select c1,c2,d1,d2 from t11,t22 where t11.c1=t22.d1(+)
2 union
3 select c1,c2,d1,d2 from t11,t22 where t11.c1(+)=t22.d1;
3、交叉连接(cross join)
SQL> select c1,c2,d1,d2 from t11 cross join t22;
等效于:(其他join必须有on子句)
SQL> select c1,c2,d1,d2 from t11,t22;
效果:
C1 C2 D1 D2
---------- ---------- ---------- ----------
5 5 1 1
5 5 2 2
5 5 3 3
5 5 4 4
1 1 1 1
1 1 2 2
1 1 3 3
1 1 4 4
2 2 1 1
2 2 2 2
2 2 3 3
2 2 4 4
12 rows selected.