Re: Oracle performance tuning question

I hope this isn't too terse, but I feeling a bit lazy.

Instead of writing it like

select ...
from ...
where in (select ... from ... union select ... from ...)

you may want to write it like

select ...
from ...
where exists(select ... from ...)
or exists(select ... from ...)

"Frank Y" <dmx_frank_at_yahoo.com> wrote in message news:9d599275.0301220853.79842d99_at_posting.google.com...
> Hi all,
>
> I found the execute of following query in my DB is very slow, any one
> has suggestion to make it fast. Thanks very much.
> SELECT WT_SVR_LVL,
> USR_NTF_TY,
> WT_TY,
> WT_NO,
> WT_VER_NO,
> COMMO_DESC_100,
> IMPR_EXPR_NAME,
> IMPR_EXPR_CNAME,
> SHPR_CNGE_NAME,
> SHPR_CNGE_CNAME,
> IMPR_EXPR_ADDR_1||' '||IMPR_EXPR_ADDR_2||' '||
> IMPR_EXPR_ADDR_3||' '||IMPR_EXPR_ADDR_4||' '||
> IMPR_EXPR_ADDR_5,
> SHPR_CNGE_ADDR_1||' '||SHPR_CNGE_ADDR_2||' '||
> SHPR_CNGE_ADDR_3||' '||SHPR_CNGE_ADDR_4||' '||
> SHPR_CNGE_ADDR_5,
> IMP_CNSTR_CD,
> TSHPT_CNSTR_CD,
> EXP_CNSTR_CD,
> IMP_CNTY_CD,
> TSHPT_CNTY_CD_ORIG,
> TSHPT_CNTY_CD_DEST,
> EXP_CNTY_CD
> FROM VHTGTLT1
> WHERE ( WT_TY, WT_NO, WT_VER_NO ) IN
> (SELECT WT_TY, WT_NO, WT_VER_NO
> FROM VMTLNAME1
> WHERE (((SSA_NAME between :l_ie_srch_range.from[0] AND
> :l_ie_srch_range.to[0])
> AND NAME_TY=:l_ie_name_ty)
> OR ((SSA_NAME between :l_sc_srch_range.from[0] AND
> :l_sc_srch_range.to[0])
> AND NAME_TY=:l_sc_name_ty))
> AND (SHPT_MOD=:p_pri_shpt_mod OR
> SHPT_MOD=:p_sec_shpt_mod)
> AND EFF_STR_DT <= to_date(:gl_sysdate,'YYYYMMDD
> HH24:MI:SS')
> AND EFF_END_DT >= to_date(:gl_sysdate,'YYYYMMDD
> HH24:MI:SS')
> AND (RISK_GP in (:p_risk_gp, '#')
> OR RISK_GP_2 = :p_risk_gp
> OR RISK_GP_3 = :p_risk_gp
> OR RISK_GP_4 = :p_risk_gp
> OR RISK_GP_5 = :p_risk_gp)
> AND DEST in (:p_dest, '#')
> AND (PT_ORIG_1 in (:p_pt_orig, '#')
> OR PT_ORIG_2 = :p_pt_orig
> OR PT_ORIG_3 = :p_pt_orig)
> UNION
> SELECT WT_TY, WT_NO, WT_VER_NO
> FROM VMTLNAME1
> WHERE SSA_NAME='#'
> AND (SHPT_MOD=:p_pri_shpt_mod OR
> SHPT_MOD=:p_sec_shpt_mod)
> AND (NAME_TY=:l_ie_name_ty OR NAME_TY=:l_sc_name_ty)
> AND EFF_STR_DT <= to_date(:gl_sysdate,'YYYYMMDD
> HH24:MI:SS')
> AND EFF_END_DT >= to_date(:gl_sysdate,'YYYYMMDD
> HH24:MI:SS')
> AND (RISK_GP in (:p_risk_gp, '#')
> OR RISK_GP_2 = :p_risk_gp
> OR RISK_GP_3 = :p_risk_gp
> OR RISK_GP_4 = :p_risk_gp
> OR RISK_GP_5 = :p_risk_gp)
> AND DEST in (:p_dest, '#')
> AND (PT_ORIG_1 in (:p_pt_orig, '#')
> OR PT_ORIG_2 = :p_pt_orig
> OR PT_ORIG_3 = :p_pt_orig)
> GROUP BY WT_TY, WT_NO, WT_VER_NO ) //end of union
> AND (DEL_IND != 'Y' OR DEL_IND IS NULL )
> ORDER BY WT_TY, WT_NO, WT_VER_NO ;
Received on Thu Jan 23 2003 - 22:38:48 CST