3

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

陈龙 2019-03-21
329
摘要:使用以下两张表,在不使用hint的情况下,构造5个SQL,在不使用hint的前提下,使执行计划分别走nestedloop,hashjoin...

问题描述

使用以下两张表,在不使用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;

image.png

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

image.png

表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'

image.png


3)sort merge join

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

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

image.png

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

image.png

可以看出对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

image.png

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

image.png

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

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

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

「喜欢文章,快来给作者赞赏墨值吧」

评论

1
3
最新发布
暂无内容,敬请期待...
数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部
暂无课程,敬请期待...