三大类型连接
1）嵌套循环连接（Nested loops join）
2）哈希连接（hash join）
3) 排序合并连接（merge sort join）
1：各类连接访问次数
1）嵌套循环连接
create table join_01_objects as select * from dba_objects where rownum < 1000 ;
create table join_05_objects as select * from dba_objects where rownum < 1000 ;
insert into join_05_objects select * from dba_objects where rownum < 1000;
insert into join_05_objects select * from dba_objects where rownum < 1000;
insert into join_05_objects select * from dba_objects where rownum < 1000;
insert into join_05_objects select * from dba_objects where rownum < 1000;
alter session set statistics_level=all;
select /*+ use_nl(t1,t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id ;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ use_nl(t1,t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id in (1050,1060);
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ use_nl(t1,t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ use_nl(t1,t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=99999 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select count(1) from join_01_objects t1;
select count(1) from join_01_objects t1 where t1.object_id in (1050,1060);
select count(1) from join_01_objects t1 where t1.object_id=1050 ;
select count(1) from join_01_objects t1 where t1.object_id=99999;
嵌套循环连接中，驱动表返回多少条记录，被驱动表就被访问多少次。
2) 哈希连接表访问次数
select /*+ use_hash(t1,t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ use_hash(t1,t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id in (1050,1060);
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ use_hash(t1,t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ use_hash(t1,t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=99999 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
在HASH连接中，驱动表和被驱动表都只会访问0次/1次
被驱动表访问0次好理解，驱动表什么情况下访问0次
select /*+ use_hash(t1,t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=99999 and 1=2 ;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
3) 排序合并的表访问次数
select /*+ use_merge(t1,t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id in (1040,1050,1060)
and 1=2 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
在排序合并连接中，表都只会访问0次/1次
排序合并连接没有驱动表和被驱动表的概念，而嵌套循循环和哈希连接需要考虑驱动表和被驱动表的情况
2： 各类连接驱动顺序区别
1） 嵌套循环连接驱动表的顺序
select /*+ leading(t1)use_nl(t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ leading(t2)use_nl(t1)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
嵌套循环连接要特别注意驱动表的顺序，小的结果集先访问，大的结果结后访问
2）哈希连接的表驱动顺序
select /*+ leading(t1)use_hash(t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ leading(t2)use_hash(t1)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
哈希连接要特别注意驱动表的顺序，小的结果集先访问，大的结果结后访问
3）排序合并连接
select /*+ leading(t1)use_merge(t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ leading(t2)use_merge(t1)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
排序合并连接没有驱动表的概念，无论哪张表在前面都无所谓。
3：各类连接排序情况
1）除嵌套循环连接都需要排序
2）排序只取部分字段
哈希连接并不排序，消耗的内存是用于建立哈希表
哈希连接、排序合并连接的简单优化思想：不要取多余的字段参与排序
select /*+ leading(t1)use_hash(t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ leading(t1)use_merge(t2)*/ t1.object_id,t1.STATUS,t2.object_id,t2.status
from join_01_objects t1,join_05_objects t2
where t1.object_id=t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
排序合并连接自行对比
4： 各类连接限制场景对比
1）哈希连接的限制
select /*+ leading(t1)use_hash(t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id <> t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ leading(t1)use_hash(t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id like t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ leading(t1)use_hash(t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id < t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
哈希连接不支持不等值连接<>,不支持>和<的连接方式，也不支持LIKE的连接方式
2)排序合并的限制
select /*+ leading(t1)use_merge(t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id <> t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ leading(t1)use_merge(t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id like t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ leading(t1)use_merge(t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id < t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
排序合并连接不支持不等值连接<>,也不支持LIKE的连接方式
3）循环嵌套无限制
5： 表连接综合运用
1）嵌套循环连接与索引
select /*+ leading(t1) use_nl(t2) */ *
from join_01_objects t1,join_05_objects t2
where t1.object_id = t2.object_id
and t1.OBJECT_NAME='JIJOIN$' ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
t1.object_id < t2.object_id 是连接条件，
t1.OBJECT_NAME='JIJOIN$' ; 是限制条件
create index join_01_index on join_01_objects(OBJECT_NAME);
create index join_05_index on join_05_objects(object_id);
驱动表的限制条件所在列有索引
被驱动表的连接条件所在列有索引
嵌套循环连接使用总结：
1：两表关联返回的记录不多，驱动表结果集合返回少量条数
2：遇到不等值查询，导致哈希和排序合并不能使用
3：索引使用思想
驱动表的限制条件列有索引
被驱动表的连接条件所在列有索引
2）哈希连接与索引
起不到传递作用，与单表索引类似
HASH_AREA_SIZE
3）排序合并连接与索引
drop index join_01_index;
drop index join_05_index;
select /*+ ordered use_merge(t2)*/ *
from join_01_objects t1,join_05_objects t2
where t1.object_id = t2.object_id
and t1.object_id=1050 ;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ ordered use_merge(t2)*/ t1.object_id,t2.object_id
from join_01_objects t1,join_05_objects t2 ;
where t1.object_id = t2.object_id order by 1;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
create index join_01_index on join_01_objects(object_id);
create index join_05_index on join_05_objects(object_id);
select /*+ ordered use_merge(t2)*/ t1.object_id,t2.object_id
from join_01_objects t1,join_05_objects t2 ;
where t1.object_id = t2.object_id order by 1;
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
连接条件建索引后，merge sort join 的排序减少一次
网友评论