经典，表的连接

王硕

第六章， 表的连接学以致用

6.1表的连接

嵌套连接（Nested loops join)    占70%

哈希连接（Hash join）

排序合并（Merge sort join）

各类连接的差异：

访问次数：

嵌套循环：驱动表返回几条数值，被驱动表访问多少次

哈希连接：驱动表和被驱动表都是最多被访问1次

排序合并：驱动表和被驱动表都是最多被访问1次

驱动顺序：

嵌套循环：有

哈希连接：有

排序合并：无

排序差别：

嵌套循环： 无须排序

哈希连接：无须排序（hash_area_size）但是会消耗内存用于建hash表

排序合并：需要排序（sort_area_size）

限制场景：

嵌套循环：无任何限制

哈希连接：连接条件为<> > < like无法使用

排序合并：链接条件为<> like 无法使用

索引与各连接的经典优化：

嵌套循环：驱动表限制条件有索引，被驱动表连接条件有索引

哈希连接：索引列在表连接中午特殊要求，与单表情况无异

排序合并：索引消除排序

王硕

    6.2.1形像了解三种连接

假如一个特别的舞会，男生在一个房间，女生在另外一个房间，中间是舞厅。

第一种情况：先从男孩子中挑选一个到女生房间找一个跟他高度合适的女孩子去跳舞。

第二对舞伴也类似产生。      我们成为嵌套循环连接

第二种情况：男女生按身高排序分成两队，再配对。      我们成为排序合并连接或者是哈希连接

分析，如果男女生人数相差不大，采取排序合并好，

如果男女生相差大，则采用嵌套循环。

王硕

6.2.2各类连接访问次数的差异

6.2.2.1嵌套循环的表访问次数

drop table t1 cascade constraints purge;

drop table t2 cascade constraints purge;

create table t1 (

id number not null,

n number,

contents varchar2(4000)

);

create table t2 (

id number not null,

n number,

contents varchar2(4000)

);

execute dbms_random.seed(0);

insert into t1

      select rownum, rownum, dbms_random.string('a',50)

from dual

connect by level<=100

order by dbms_random.random;

      insert into t2

      select rownum, rownum, dbms_random.string('b',50)

from dual

connect by level<=100000

order by dbms_random.random;

commit;

select count(*) from t1;

select count(*) from t2;

1.T2表为哈被访问100次？

测试表连接语法：

select /*+leading(t1) use_nl(t2)*/ *

from t1,t2

where t1.id=t2.t1_id;             

表访问次数跟踪语法：

alter session set statistics_level=all;

set linesize 1000

select /*+leading(t1) use_nl(t2)*/ *

from t1,t2

where t1.id=t2.t1_id; 

查看执行计划：

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

其中 starts列表示表访问的次数，T1表1次，T2表100次。

2.100次又变成2次

为t1增加一个条件：

select /*+leading(t1) use_nl(t2) */ *

from t1,t2

where t1.id=t2.t1_id

  and t1.n in (17,19)；

查看执行计划：

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

其中 T1表访问1次，T2表访问2次。

3.2次又变成1次

select /*+leading(t1) use_nl(t2) */ *

from t1,t2

where t1.id=t2.t1_id

  and t1.n=19；

查看执行计划:

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

其中T1表访问1次，T2表访问1次

4.1次又变成0次

select /*+leading(t1) use_nl(t2) */ *

from t1,t2

where t1.id=t2.t1_id

  and t1.n=999999；

查看执行计划:

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

其中T1表访问1次，T2表访问0次

5.嵌套循环连接结论

我们在t1表中只有100个数据。

第一次查询全部100个数据，要返回100，读t2表100次。

第四次查询t1表没有符合项，不访问t2表。

6.2.2.2哈希连接的表访问次数

1.测试t2表仅访问1次

测试脚本：

select /*+leading(t1) use_hash(t2)*/ *

from t1,t2

where t1.id=t2.t1_id;

查看执行计划:

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

2.HASH连接结论

T1表返回10条记录，T2表返回1次，，说明哈希连接中被驱动表的访问次数和驱动表的返回记录无关。驱动表T1和被驱动表T2都只会访问1次。

也就是说，在HASH连接中，驱动表和被驱动表都只会访问0次，或者1次。

select /*+leading(t1) use_hash(t2)*/ * 

from t1 ,t2

where t1.id=t2.t1_id

and t1.n=99999999;

查看执行计划:

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

结果表明：

哈希连接中T2被访问了0次；

查看t1表访问0次的情况：  （1=2表示语句一定不成立，所以返回0）

select /*+leading(t1) use_hash(t2)*/ * 

from t1 ,t2

where t1.id=t2.t1_id

and t1.n=99999999;

查看执行计划:

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

结果表明：

T1，T2都只被访问0次

6.2.2.3排序合并的表访问次数

测试语句：

select /*+leading(t1) use_merge(t2)*/ * 

from t1 ,t2

where t1.id=t2.t1_id

and t1.id=t2.t1_id;

查看执行计划:

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

结论：

排序合并连接和Hash连接一样，T1和T2表都只会访问0次或1次。 排序合并连接并没有驱动与被驱动的概念。

王硕

6.2.3各连接驱动顺序区别

6.2.3.1嵌套循环的表驱动顺序

开启监控：

alter session set statistics_level=all;

执行观察：

select /*+leading(t1) use_nl(t2)*/ *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

select /*+leading(t2) use_nl(t1)*/ *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

嵌套连接t1表先访问的情况：

执行观察：

select /*+leading(t1) use_nl(t2)*/ *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

嵌套连接t2表先访问的情况：

执行观察：

select /*+leading(t2) use_nl(t1)*/ *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

结论：

T1表先被访问的buffer是1014，而T2表先被访问的buffer是702k。说明嵌套驱动前后非常重要。

6.2.3.2哈希连接的表驱动顺序

T1表先被访问：

select /*+leading(t1) use_hash(t2)*/ *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

T2表先被访问：

select /*+leading(t2) use_hash(t1)*/ *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

结论：

T1表先被访问下buffer是1013，而T2表先被访问的情况下buffer是1013，但是used-mem差异明显，前者是286kb，后者是11mb。时间前者是0.04秒，后者是0.1秒。

T1，T2表前后访问差别也明显。

6.2.3.3排序合并的表驱动顺序

T1表先被访问：

select /*+leading(t1) use_merge(t2)*/ *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

T2表先被访问：

select /*+leading(t2) use_merge(t1)*/ *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

结论：

效率一样。

嵌套循环和哈希连接有驱动顺序，排序连接没有驱动概念。

王硕

6.2.4各类连接排序情况分析

6.2.4.1除嵌套循环都需要排序

在嵌套循环连接的 计划中没有Uesd-Mem相关关键字，而哈希连接和合并排序连接随处可见Uesd-Mem相关关键字。

6.2.4.2排序只需去部分字段

哈希连接并不排序，消耗内存是用于建立HASH表，关于哈希与合并这两钟排序方式，不要取多余的字段参与排序。

开启监控：

alter session set statistics_level=all;

select /*+leading(t1) use_merge(t2)*/ *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

select /*+leading(t1) use_merge(t2)*/ t1.id

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

取部分字段排序的尺寸是1638k+2048，显然小于取全部字段的8236k+2048，所以取部分字段更高效。

王硕

6.2.5各类连接的限制场景

HINT提示：

HINT提示是oracle提供用来强制走某执行计划的工具。使用full()强制走索引，或者全表。但如果该执行计划不能强制进行某个操作，会报错。

6.2.5.1哈希连接的限制

非等值连接条件，oracle将无法支持HASH连接的算法

select /*+leading(t1) use_hash(t2)*/ *

from t1,t2

where t1.id<>t2.t1_id

and t1.n=19;

先解释：

explain plan for

select /*+leading(t1) use_hash(t2)*/ *

from t1,t2

where t1.id<>t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display);

HINT走的不是HASH而是走的NL连接。同理哈希连接也不支持大于小于及like算法。更改条件> < like关键词实验。

6.2.5.2排序合并的限制

不支持<>不等值写法：

explain plan for

select /*+leading(t1) use_merge(t2)*/ *

from t1,t2

where t1.id<>t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display);

合并连接支持大于或小于写法：

explain plan for

select /*+leading(t1) use_merge(t2)*/ *

from t1,t2

where t1.id<t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display);

合并连接不支持like写法：

explain plan for

select /*+leading(t1) use_merge(t2)*/ *

from t1,t2

where t1.id like t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display);

6.2.5.3嵌套循环无限制

王硕

6.3自己动手的表连接

6.3.1嵌套循环与索引

如果T1中选取量少，则使用嵌套连接更好，但是T1表信息量过大，找到对应的数据不够迅速，则需要建立一个索引，使oracle自动默认认为嵌套连接更高效。一般两表无索引下不用hint会走哈希连接。

嵌套两表无索引实验：

select /*+leading(t1) use_nl(t2)*/ *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

去掉HINT提示，两表无索引，走哈希连接：

select *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

对T1表限制条件加索引,以下强制嵌套连接查看优化变化效率：

create index t1_n on t1(n);

select /*+leading(t1) use_nl(t2)*/ *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

    结论：

buffer 从1013减少到1009，T1表从全表扫描变成索引读。由于T1表只有100条记录，索引除了只能单块读外，table access by index rowid的回表负担，索引buffer为3，胜于前面全表扫描的buffer7。正好7-3=1013-1009。

对T1表的连接条件加索引：

create  index t2_t1_id on t2(t1_id);

select /*+leading(t1) use_nl(t2)*/ *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

结论：

1009变为7。此时不适用HINT，oralce则自己选择嵌套连接：

select *

from t1,t2

where t1.id=t2.t1_id

and t1.n=19;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

结论：

最适合嵌套连接的场景

1.两表关联返回的记录不多，最佳情况是驱动表返回1，或少量记录，而被驱动表仅匹配到1条或少量记录。

2.不等值查询场景

3.驱动表的限制条件所在的列有索引， 被驱动表的连接条件所在的列有索引。

T1为驱动表，限制条件是t1.n=19，所在的列为n列。

   连接条件是t1.id=t2.t1_id。

6.3.2哈希连接与索引

在两表等值条件，无索引下用哈希连接。其需在HASH_AREA_SIZE完成，增大pga大小，可提高效率。

6.3.3排序合并与索引

开启监控：

alter session set statistics_level=all;

select /*+leading(t1) use_merge(t2)*/ * 

from t1 ,t2

where t1.id=t2.t1_id

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

建立索引：

create index idx_t1_id on t1(id);

select /*+leading(t1) use_merge(t2)*/ * 

from t1 ,t2

where t1.id=t2.t1_id

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

结论：

未建索引，排序不可避免。

建立索引后，排序减少一次。

增大内存排序去，避免排序尺寸过大在磁盘中排序。