oracle表连接（三）

各类连接限制场景

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列建索引是有意义的。