oracle表连接（二）

各类连接驱动顺序区别

1.嵌套循环连接

select /*+leading(t1) use_nl(t2)*/ * from t1, t2 where t1.id = t2.t1_id and t1.n = 19;

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只访问1次

t2表先访问：BUFFER 701K，t1被访问100000次

所以，嵌套循环连接要特别注意驱动表的顺序，小的结果集先访问，大的结果集后访问

2. 哈希连接

select /*+leading(t1) use_hash(t2)*/ * from t1, t2 where t1.id = t2.t1_id and t1.n = 19;

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，Used_Mem 286K，时间0.04秒

t2表先访问：BUFFER 1013，Used_Mem 11MB，时间0.01秒

在哈希连接中，驱动表顺序也很重要

 

3.排序合并连接

select /*+leading(t1) use_merge(t2)*/ * from t1, t2 where t1.id = t2.t1_id and t1.n = 19;

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'));

无论先访问t1还是先访问t2，效率都一样，执行时间，BUFFER,USED_MEM都一样，这表明，排序合并连接没有驱动表概念。

 

综上所述：嵌套循环连接和哈希连接有驱动顺序，驱动表的顺序不同将影响表连接的性能；而排序合并连接没有驱动的概念，无论哪张表在前都无妨。

嵌套循环连接不需要排序；哈希连接并不排序，消耗内存是用于建议HASH表；排序合并连接需要排序。关于哈希连接和排序合并连接，不要取多余的字段参与排序，因为选择的字段越少，消耗内存的尺寸就越小。

select /*+leading(t2) use_merge(t1)*/ * from t1, t2 where t1.id = t2.t1_id and t1.n = 19;

select /*+leading(t2) use_merge(t1)*/ 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'));

可以看出Used_Mem代表的内存消耗差别很大