对表的连接的总结

2018-02-27 08:57:59来源:cnblogs.com作者:路人佳人点击

分享

1.嵌套循环访问的次数
SYS@ PROD>create table t1(
id number not null,
n number,
contents varchar2(4000)
);
SYS@ PROD>create table t2(
id number not null,
t1_id number not null,
n number,
contents varchar2(4000)
);
SYS@ PROD>execute dbms_random.seed(0);

SYS@ PROD>insert into t1
select rownum,rownum,dbms_random.string('a',50)
from dual
connect by level<=100
order by dbms_random.random;
SYS@ PROD>insert into t2 select rownum,rownum,rownum,dbms_random.string('b',50) from dualconnect by level<=100000 order by dbms_random.random;
SYS@ PROD>commit;
SYS@ PROD>select count(*) from t1;100
SYS@ PROD>select count(*) from t2;100000
测试:
set linesize 1000
alter session set ststistics_level=all;
select /*+leading(t1) user_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次
1>SYS@ PROD>select /*+leading(t1) use_nl(t2)*/*
from t1,t2
where t1.id=t2.t1_id
and t1.n in(17,19);
SYS@ PROD>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
发现执行计划里面Starts列t1表访问1次,t2表访问2次
2>SYS@ PROD>select /*+leading(t1) use_nl(t2)*/*
from t1,t2
where t1.id=t2.t1_id
and t1.n=19;
发现执行计划里面Starts列t1表访问1次,t2表访问1次
3>SYS@ PROD>select /*+leading(t1) use_nl(t2)*/*
from t1,t2
where t1.id=t2.t1_id
and t1.n=999999999;
发现执行计划里面Starts列t1表访问1次,t2表访问0次
结论:t1表的查询返回多少条记录,t2表就访问多少次。

HINT的含义,其中use_nl表示强制用嵌套循环的方式连接,leading(t1)表示强制先访问t1表,也就是t1表作为驱动表,增加这些HINT
提示的目的只是为了确保我们的SQL语句的执行计划在做嵌套循环连接。在嵌套循环连接中,驱动表返回多少条记录,被驱动表就访问多少次。
2.哈希连接表的访问次数
/*leading(*) use_hash(t2)*/*
结论:在HASH连接中,驱动表和被驱动表都只会访问0次或者1次。
3.排序合并的访问次数
/*leading(*) use_merge(t2)*/*
结论:排序合并连接根本就没有驱动和被驱动的概念,而嵌套循环和哈希连接要考虑驱动和被驱动情况。

三种访问顺序的比较:
1.嵌套循环连接特别要注意驱动表的顺序,小的结果集先访问,大的结果集后访问,才能保证被驱动表的访问次数降到最低,从而提升性能。
2.哈希循环顺序也重要
3.嵌套循环连接和哈希连接有驱动顺序,驱动表的顺序不同将影响连接的性能,而排序合并连接没有驱动的概念,无论哪张表都无妨。
三种访问的排序:
嵌套和哈希不需要排序,而排序合并需要排序。

适合连接条件范围的比较:
1.HASH连接查询条件不支持<>,大于和小于的写法,也不支持like的连接方式,如果加了这些连接条件,会走全表扫描。
2.排序合并连接不支持<>,也不支持like的连接条件,但支持>和<之类的连接条件。遇到
3.嵌套循环支持所有的sql连接条件的写法,没有任何限制。

最适合NL连接的场景
①两表关联返回的记录不多,最佳情况是驱动表结果集仅返回1条,或少量几条记录,而被驱动表仅匹配到1条或少量几条数据
这种情况即便t1表和t2表的记录奇大无比,也是非常迅速的。
②遇到一些不等值查询导致哈希和排序合并连接被限制使用,不得不使用NL连接。

排序合并连接上的连接条件虽然没有检索功能却有消除排序的作用。

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台