#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    1
    Rep Power
    0

    Query Optimization (Urgent)


    Can somebody suggest me better way to write this query.
    Thanks in advance.


    SELECT DISTINCT clcla.parn_cl_id cl_id
    FROM v_regs_clcla clcla,
    (
    SELECT a.parn_cl_id , a.cl_id
    FROM v_regs_clh clh,
    (
    SELECT DISTINCT z.parn_cl_id,z.cl_id,z.pr_id , z.cmp_pr_id
    FROM v_regs_prh prh , spc spc,
    (

    SELECT DISTINCT clcla.parn_cl_id,clpsh.cl_id,clpsh.pr_id , NVL(prpra.pr_id,clpsh.pr_id) AS cmp_pr_id
    FROM v_regs_clcla clcla, v_regs_clpsh clpsh, v_regs_prpra prpra
    WHERE clcla_typ_c = 104
    AND clcla_eff_to_dt IS NULL
    AND clcla_stus_c = 'A'
    AND clcla.cl_id = clpsh.cl_id
    AND clps_ackrat_catg_c IN (pc_ASC,pc_ASO,pc_CPL,pc_RFA,pc_RTS)
    AND clps_stus_c = 'A' AND clps_mkt_stus_c = 100
    AND clpsh_wrk_flow_c= 7
    --AND (clps_eff_to_dt >= regs_pkg_maint.STLM_DATE OR clps_eff_to_dt IS NULL)
    AND (clps_cn_end_dt >= regs_pkg_maint.STLM_DATE )
    AND clcla.parn_cl_id = NVL(c_cl_id_in, clcla.parn_cl_id)
    AND clpsh.pr_id = prpra.parn_pr_id (+)
    AND prpra_typ_c (+)= 2000
    AND prpra.prpra_stus_c (+) = 'A'
    --AND (prpra.prpra_eff_to_dt >= regs_pkg_maint.STLM_DATE OR prpra.prpra_eff_to_dt IS NULL)
    ) z

    WHERE z.pr_id = prh.pr_id
    AND prh.copc_id = spc.copc_id
    AND prh.pr_stus_c='A'
    AND spc.stlm_rcd_sta_cd='A'
    AND spc.spc_rt_reqd_in = 'Y'
    AND prh.prh_rec_mtn_ts = ( select max(prh_rec_mtn_ts) from v_regs_prh prh_inner
    where prh_inner.pr_id = prh.pr_id and prh_inner.pr_stus_c='A')
    ) a
    WHERE clh.cl_id = a.parn_cl_id
    AND cl_stus_c = 'A'
    AND cl_acct_typ_c NOT IN (4,6,10)
    AND NVL(cl_prtnr_aff_c,'XXX') <> 'A'
    ) b
    WHERE b.cl_id = clcla.cl_id
    AND clcla_typ_c = 104
    AND clcla_stus_c = 'A'
    AND clcla.parn_cl_id NOT IN ( SELECT DISTINCT CL_ID FROM V_REGS_CLCLA X WHERE X.CL_ID =
    clcla.parn_cl_id AND X.clcla_typ_c IN (101,102,111));
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    67
    Rep Power
    12
    Go to Quest.com, download TOAD and some other SQL tuning program to help you streamline your code. it will show u the improvement in CPU usage, I/O rate... etc.
  4. #3
  5. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    Without knowing your table structure and the desired result it's very hard to help you.
    One thing for shure is to look at the explain plan to check execution.

IMN logo majestic logo threadwatch logo seochat tools logo