半外连接优化一列

据说

select  a.*,b.*
from a, b
where a.id=b.id(+)
and a.name like '%%'
and b.name like '%%'

执行计划当中 无法把B的条件优先在连接前执行,从而减少B外连接的数据量.

 

如果才用内嵌视图的话可以当带来其他问题

select a.*, c.*
from  a,
( select b.*  from b where b.name like '%%') c
where a.id=c.id
and a.name like '%%'

最终解决方案:

select a.*,b.*
from a left join b 
on (a.id=b.id and b.name like'%%')
where a.name like '%%'