各类连接限制场景
explain plan for
select /*+leadind(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,HASH都不支持。
explain plan for
select /*+leadind(t1) use_merge(t2)*/ *
from t1, t2
where t1.id <> t2.t1_id and t1.n = 19;
select * from table(dbms_xplan.display);
排序合并连接不支持<>和Like,但是支持>、<
嵌套循环无限制
表连接与索引
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'));
如果不使用HINT提示
select * from t1, t2 where t1.id = t2.t1_id;
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
会显示走的是HASH连接,因为ORACLE认为HASH连接更合理,但是此时逻辑读,COST值都差不多
create index t1_n on t1(n);
再执行上述语句,看看扫描t1表时BUFFER的差别
create index t2_t1_id on t2(t1_id);
再执行上述语句,看看扫描t2表时BUFFER的差别
最适合NL连接的场景:
1. 两表关联返回的记录不多,最佳情况是驱动结果集仅返回1条或少量几条记录,而被驱动表仅匹配到1条或少量几条记录,这种情况,即便t1和t2表的记录奇大无比,也是非常迅速的。
2. 遇到一些不等值查询导致哈希和排序合并连接被限制使用,不得不使用NL连接。
3.最佳的索引建立位置:驱动表的限制条件所在列有索引,被驱动表的连接条件所在列有索引。原因在于,驱动表的限制条件建立索引是为了缩小扫描驱动表的时间,如果在驱动表的连接条件建立索引就没有任何意义了,所有列关联到另一个表的所有列,等同于每一条记录都要关联。而驱动表的限制条件建立了索引,只快速 返回1条或几条,然后再等传递给t2表的t1_id列,一般情况下,t2表对应t1表返回的记录并不多,所以t2表的t1_id列建索引是有意义的。