几种表连接方式的使用场景

使用以下两张表，在不使用hint的情况下，构造5个SQL, 在不使用hint的前提下，使执行计划分别走nested loop, hash join, sort merge join, merge join cartesian和外连接.  列出执行计划，执行计划里需要包括实际每一步的返回行数。（有必要的情况下可以创建索引）

测试数据如下

drop table test_tab1 ;
 create table test_tab1 as select  /*+ordered*/ * from all_objects,(select * from dual connect by level < 5)   ;
 
drop table test_tab2;
create table test_tab2 as select * from test_tab1 where rownum < 1e4;
 
 
analyze table test_tab1 compute statistics for table for all indexes for all columns;
analyze table test_tab2 compute statistics for table for all indexes for all columns;

1）nested loop

nested loop，指的是两个表连接时, 通过两层嵌套循环来进行依次的匹配, 最后得到返回结果集的表连接方法.

select t1.owner,t1.object_name,t2.OBJECT_ID
from test_tab1 t1,
     test_tab2 t2
where t1.OBJECT_ID = t2.OBJECT_ID
  and ROWNUM < 10;

select *
from test_tab1 t1,
     test_tab2 t2
where t1.OBJECT_ID > t2.OBJECT_ID
and  t1.OBJECT_ID < 8000;

表t1 作为驱动表，先对表t2 筛选出<8000的数据，再以匹配满足，t1表的> t2.object_id 条件，依次执行。

关于嵌套循环，首先，要确保结果集小的表为驱动表，结果集多的表为被驱动表。这不意味着记录多的表不能作为驱动表, 只要通过谓词条件过滤后得到的结果集比较小，也可以作为驱动表。

其次，在驱动表的谓词条件列以及被驱动表的连接列上加上索引，能够显著的提高执行性能。

最后，如果要查询的列都在索引中，避免回表查询列信息时，又将进一步提高执行性能。

2）hash join

select *
from test_tab1,
     test_tab2
where test_tab1.OBJECT_ID = test_tab2.OBJECT_ID
  and test_tab1.OBJECT_NAME = 'T_TEST1'

3）sort merge join

Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配。

例如，下面的两表，满足筛选条件后，进行排序，特别是对于数据已经是排序的情况下。

select *
  from test_tab1 t1,
     test_tab2 t2
where  t1.OBJECT_ID>t2.OBJECT_ID+10 and t1.OBJECT_ID<300;
/

可以看出对t1和t2表都做了全表扫描，对数据进行了排序，然后对t1 object_id结果集进行了匹配和关联，最后把结果输出。

4）merge join cartesian 

select t1.owner,t2.object_name
from test_tab1 t1,
     test_tab2 t2
where t1.OBJECT_ID < 1000 and t2.OBJECT_ID < 1000

上面对t1 表的object_id进行过滤处理后，形成结果集1，这样的数据可能是排序的，也有可能是不排序的。此后，在对t2表的object_id进行过滤，t1表进行排序，最后吧所有的结果集都进行合并。

关于笛卡尔的参数：

alter system set "_optimizer_mjc_enabled" = false;
or
alter session set "_optimizer_mjc_enabled" = false;

5）外连接.

select t1.owner,t1.object_name,t2.OBJECT_ID
from test_tab1 t1,
     test_tab2 t2
where t1.OBJECT_ID = t2.OBJECT_ID(+)
  and ROWNUM < 10;

不加（+），就变成hash join，这是由于t1 表不需要满足外链接查询条件，及完成两表扫描后，不需要外链接查询条件，直接返回到结果集。

可以看到先对表t2 进行了<1000的过滤，在对表t1 <1000，也即是t1 为主表，即使有些记录关联不上，主表的信息都能够查询出来。

这个可以通过访问路径access可以看出"T1"."OBJECT_ID"(+)="T2"."OBJECT_ID"