Oracle优化一个视图发现的问题。

--原始视图：

select BLNO,CFS_LOCACCEPTID,LOCACCEPTNO,E_BLID,DELIVERTIMES,UPDATETIME,
UPDATEUSER,ACCEPTTIME,DELIVERYMAN,CARGOQTY,REMARK,NVL(T_LIFT,'未收') as T_LIFT,
NVL(T_MANPOWER,'未收') as T_MANPOWER,
NVL(T_REINFORCE,'未收') as T_REINFORCE,
NVL(T_HUG,'未收') as T_HUG,
NVL(T_HLIFT,'未收') as T_HLIFT from (select (Select blno from e_bl where e_blid=t.e_blid) as blno,cfs_locacceptid,locacceptno,
delivertimes,updatetime,updateuser,accepttime,deliveryman,cargoqty,E_BLID,
remark,(select case NVL(Trim(PAYMODE),'--') when 'M' then '月结' when 'L' then '现结' when '--' then '/' else '/' end
from  cfs_chargedetail where LOCACCEPTNO=t.LOCACCEPTNO and CHARGECODE='T_LIFT' and rownum=1) as T_LIFT,
(select case NVL(Trim(PAYMODE),'--') when 'M' then '月结' when 'L' then '现结'  when '--' then '/'  else '/' end
from  cfs_chargedetail where LOCACCEPTNO=t.LOCACCEPTNO and CHARGECODE='T_MANPOWER' and rownum=1) as T_MANPOWER,
(select case NVL(Trim(PAYMODE),'--') when 'M' then '月结' when 'L' then '现结' when '--' then '/'  else '/' end
from  cfs_chargedetail where LOCACCEPTNO=t.LOCACCEPTNO and CHARGECODE='T_REINFORCE' and rownum=1) as T_REINFORCE,
(select case NVL(Trim(PAYMODE),'--') when 'M' then '月结' when 'L' then '现结' when '--' then '/'  else '/' end
from  cfs_chargedetail where LOCACCEPTNO=t.LOCACCEPTNO and CHARGECODE='T_HUG' and rownum=1) as T_HUG,
(select case NVL(Trim(PAYMODE),'--') when 'M' then '月结' when 'L' then '现结' when '--' then '/'  else '/' end
from  cfs_chargedetail where LOCACCEPTNO=t.LOCACCEPTNO and CHARGECODE='T_HLIFT' and rownum=1) as T_HLIFT
from cfs_locaccept t) WHERE NVL(BLNO,'---')<>'---'

速度慢到11秒才会出数据，优化后的视图：

select BLNO,CFS_LOCACCEPTID,LOCACCEPTNO,E_BLID,DELIVERTIMES,UPDATETIME,
UPDATEUSER,ACCEPTTIME,DELIVERYMAN,CARGOQTY,REMARK,NVL(T_LIFT,'未收') as T_LIFT,
NVL(T_MANPOWER,'未收') as T_MANPOWER,
NVL(T_REINFORCE,'未收') as T_REINFORCE,
NVL(T_HUG,'未收') as T_HUG,
NVL(T_HLIFT,'未收') as T_HLIFT from (select e_bl.blno as blno,cfs_locacceptid,locacceptno,
delivertimes,t.updatetime,t.updateuser,accepttime,deliveryman,t.cargoqty,t.E_BLID,
t.remark,
GetPayModeByCFS_ChargeDeatil(t.LocAcceptNo,'T_LIFT') as T_LIFT,
GetPayModeByCFS_ChargeDeatil(t.LocAcceptNo,'T_MANPOWER') as T_MANPOWER,
GetPayModeByCFS_ChargeDeatil(t.LocAcceptNo,'T_REINFORCE') as T_REINFORCE,
GetPayModeByCFS_ChargeDeatil(t.LocAcceptNo,'T_HUG') as T_HUG,
GetPayModeByCFS_ChargeDeatil(t.LocAcceptNo,'T_HLIFT') as T_HLIFT
from cfs_locaccept t left join e_bl on e_bl.e_blid=t.e_blid );

结论：

 执行视图查询:

   SELECT * FROM view_locacceptweb WHERE  blno= '569622668'  

  如果查询中包含子查询，并且又作为视图的查询条件，就会造成全表扫描。速度非常慢。  尽量使用联查代替子查询。