目录
连接方式
NEST LOOP（嵌套循环连接）
HASH JOIN（哈希连接）
MERGE JOIN（排序归并连接）
连接方式
创建测试表：
create table tab1(c1 int,c2 int ,c3 int);
create table tab2(c1 int,c2 int ,c3 int);
insert into tab1 select level,level,level from DUAL CONNECT by level <100000;
insert into tab2 select level,level,level from DUAL CONNECT by level <100000;
create index ind_tab1 on tab1(c1);
create index ind_tab2 on tab2(c1);
select * from user_indexes where table_name in ('TAB1','TAB2') ;
NEST LOOP（嵌套循环连接）
优化器会选择一张代价较小的表作为驱动表，另一张表作为被驱动表，驱动表的每条记录与被驱动表进行一次连接操作。
需注意的问题：
- 选择小表作为驱动表。统计信息尽量准确，保证优化器选对驱动表。
- 大量的随机读。如果没有索引，随机读很致命，每次循环只能读一块，不能读多块。使用索引可以解决这个问题。
使用场景：
- 驱动表有很好的过滤条件
- 表连接条件能使用索引
- 结果集比较小
NEST LOOP INNER JOIN2（嵌套循环内连接）
explain select tab1.c1 from tab1 ,tab2 where tab1.c1>tab2.c1;
NEST LOOP LEFT JOIN2（嵌套循环左连接）
explain select tab1.c1 from tab1 left join tab2 on tab1.c2>tab2.c2;
HASH JOIN（哈希连接）
以一张表的连接列为哈希键，构造哈希表，另张表的连接列进行哈希探测，找到满足条件的记录。
HASH JOIN的特点：
- 一般没索引或用不上索引时会使用该连接方式
- 选择小的表（或row source）做hash表
- 只适用等值连接中的情形，非等值一般用NEST LOOP
Hash连接比较消耗内存，如果系统有很多这种连接时，需调整以下3个参数：
HJ_BUF_GLOBAL_SIZE
HJ_BUF_SIZE
HJ_BLK_SIZE
HASH2 INNER JOIN（HASH内连接）
explain select tab1.c1,tab2.c2 from tab1 , tab2 where tab1.c1=tab2.c1;
HASH LEFT JOIN2（HASH左外连接）
explain select tab1.c1 from tab1 left join tab2 on tab1.c1=tab2.c1;
HASH FULL JOIN2（HASH 全外连接）
explain select tab2.c1 from tab1 full outer join tab2 on tab2.c1=tab1.c1 ;
全连接的查询结果是左外连接和右外连接查询结果的并集。
HASH LEFT SEMI MULTIPLE JOIN（多列not in）
explain select * from tab1 where (c1,c2) not in (select c1,c2 from tab2) ;
HASH LEFT SEMI JOIN2(HASH 左半连接)
子查询和非等值连接出现
explain select * from tab1 where c1 not in (select c1 from tab2) and c2 not in (select c2 from tab2);
HASH RIGHT JOIN2(HASH右外连接)
explain select t.c1 ,tab1.c3 from tab1 left join (select * from tab2 where c1=10)t on t.c1=tab1.c2 ;
MERGE JOIN（排序归并连接）
Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配。
MERGE SORT的特点：
- 无驱动表之分，随机读很少
- 两个表都需要按照连接列排序，需要消耗大量的cpu和额外的内存
应用场景：
通常情况下，merge sort join需要消耗大量的cpu和内存，效率都不会太高。如果存在相关索引可以消除sort，那么CBO可能会考虑该连接方式。
explain select tab1.c1 from tab1,tab2 where tab1.c1=tab2.c1;
