oracle join on 數(shù)據(jù)過濾問題

更新時間：2009年07月13日 21:36:17 作者：

因為在FOR .. IN () LOOP 游標中使用所以不能采用下面的查詢語句做游標

select a.f_username 
from 
( 
SELECT /*+parallel(gu,4)*/distinct gu.f_username 
FROM t_base_succprouser gu 
where gu.f_expectenddate > (select trunc(sysdate,'Y')FROM DUAL) 
and gu.f_lotid=1 
and gu.f_playid=4 
and gu.f_paymoney>=1500 
) A 
left join 
( 
select 
from t_base_vip_customes 
and ((vu.f_passeddate is null ) or (vu.f_passeddate > trunc(sysdate,'Y') )) 
and ((vu.f_lotid is null ) or (vu.f_lotid=1)) 
and ((vu.f_playid is null ) or (vu.f_playid=4)) 
and ((vu.f_condtionid is null ) or (vu.f_condtionid=3)) 
)B 
on A.f_username=B.f_usernam 
where b.f_username is null 

采用下面的語句只能查出部分用戶

復制代碼代碼如下:

SELECT /*+parallel(gu,4)*/distinct gu.f_username 
FROM t_base_succprouser gu 
left join t_base_vip_customes VU on gu.f_username=vu.f_username 
gu.f_expectenddate > (select trunc(sysdate,'Y')FROM DUAL) 
and gu.f_lotid=rec_viplotplay.f_lotid 
and gu.f_playid=rec_viplotPlay.f_Playid 
and gu.f_paymoney>=rec_viplotPlay.F_Conditon_ValuesA 
and ((vu.f_passeddate is null ) or (vu.f_passeddate > trunc(sysdate,'Y') )) 
and ((vu.f_lotid is null ) or (vu.f_lotid=rec_viplotplay.f_lotid)) 
and ((vu.f_playid is null ) or (vu.f_playid=rec_viplotPlay.f_Playid)) 
and ((vu.f_condtionid is null ) or (vu.f_condtionid=rec_viplotPlay.F_CondtionID)) 
and vu.f_username is null 

執(zhí)行計劃：

復制代碼代碼如下:

SELECT STATEMENT, GOAL = ALL_ROWS 
HASH UNIQUE 
NESTED LOOPS OUTER 
PARTITION RANGE ALL 
TABLE ACCESS FULL Object name=T_BASE_SUCCPROUSER 
VIEW 
FILTER 
TABLE ACCESS FULL Object name=T_BASE_VIP_CUSTOMES 
FAST DUAL 

后來改成了下面就能全部查出來了

復制代碼代碼如下:

SELECT /*+parallel(gu,4)*/distinct gu.f_username 
FROM t_base_succprouser gu 
left join t_base_vip_customes VU on gu.f_username=vu.f_username 
and ((vu.f_passeddate is null ) or (vu.f_passeddate > trunc(sysdate,'Y') )) 
and ((vu.f_lotid is null ) or (vu.f_lotid=rec_viplotplay.f_lotid)) 
and ((vu.f_playid is null ) or (vu.f_playid=rec_viplotPlay.f_Playid)) 
and ((vu.f_condtionid is null ) or (vu.f_condtionid=rec_viplotPlay.F_CondtionID)) 

where gu.f_expectenddate > (select trunc(sysdate,'Y')FROM DUAL) 
and gu.f_lotid=rec_viplotplay.f_lotid 
and gu.f_playid=rec_viplotPlay.f_Playid 
and gu.f_paymoney>=rec_viplotPlay.F_Conditon_ValuesA 
and vu.f_username is null 
執(zhí)行計劃： 
SELECT STATEMENT, GOAL = ALL_ROWS 
HASH UNIQUE 
FILTER 
NESTED LOOPS OUTER 
TABLE ACCESS BY GLOBAL INDEX ROWID Object name=T_BASE_SUCCPROUSER 
INDEX RANGE SCAN Object name=IX_BASE_PROUSER_LOWEX 
FAST DUAL 
VIEW 
TABLE ACCESS FULL Object name=T_BASE_VIP_CUSTOMES 