半导体、半决赛以及半联结 exists

看似一个简单的查询，但是执行时间很长，想看看到底在干啥，可以通过执行计划来了解。

先用sql_monitor试试吧，比较准确和清晰。

执行了14313秒（4个小时）还没结束，已经读取了111GB数据，主要耗是在IO等待上。



看到第1行是一个 MERGE JOIN SEMI 的操作，合并半联结？什么是半联结？

半联结是两个表之间的联结，第一个表中的数据是否返回会根据另一个表中是否出现相匹配的数据来确定。

执行逻辑
Q1代表外层查询，Q2代表子查询

while Q1 still has records
  fetch record from Q1
  result = false
  while Q2 strill has records    <-- 循环取子查询数据
    fetch record from Q2
    if Q1.record matches Q2.record then 
      result = true
      exit loop    <-- 如果匹配就结束循环
    end if
  end loop
  if (result = true) return Q1 record   <--如果匹配Q2，这里就返回Q1的记录了
end loop

控制半联结执行计划
1 使用提示 NO_SEMMIJOIN，例如
  SELECT name from a where exists (select /*+no_semijoin */ null from b where a.id=b.id);
2 隐含参数进行会话或实例级控制
  alter session set "_always_semi_join"=NESTED_LOOPS;
3 在11g中通过 or 条件改写（其他版本最好测试一下）
   SELECT name from a where 1=2 or exists (select /*+no_semijoin */ null from b where a.id=b.id);

当以下条件满足时会导致半联结
1 语句使用了EXISTS 或 IN 或 =ANY
2 在EXISTS或IN 子句中有子查询
3 如果语句使用EXISTS语法，则必须使用相关子查询得到结果（得有关联条件）
4 EXISTS或IN不能包含在OR分支中（参考上面控制半联结的第3种方法）
 
回到我们这个问题，语句执行慢就是因为采用半联结方式执行，恰恰外层查询很大，有217M条记录（2.17亿条），虽然通过rowid（主键）但是效率还是不高。子查询结果集比较小，此案例就1条，那么直接关联不就省事了？


我们的方案是

改写



效果有待验证。