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 Wed Jan 22 2003 - 10:53:41 CST
