各类连接驱动顺序区别
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代表的内存消耗差别很大