对表的连接的总结

fteair

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连接。

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

fteair

张兴康

fteair