oracle的表几种连接比较,几种表连接方式的使用场景

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 

select *

from test_tab1 t1,

test_tab2 t2

where t1.OBJECT_ID > t2.OBJECT_ID

and  t1.OBJECT_ID 

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

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

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

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

2)hash joinselect *

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 cartesianselect t1.owner,t2.object_name

from test_tab1 t1,

test_tab2 t2

where t1.OBJECT_ID 

上面对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 

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

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

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