SQL tunning advise
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL tunning advise

  1. #1
    Join Date
    Aug 2003
    Posts
    54

    SQL tunning advise

    anyone has any suggestion on the below query:

    select * from
    (select distinct
    ps.ctr_no
    ,NVL(table_dec,'XXXX') DEPT_ID
    ,NVL(b.ba_no,'XXXX') ba_no
    ,NVL(actpay_cd,'') actpay_cd
    ,NVL(actrec_cd,'') actrec_cd
    ,NVL(affil_cd,'') affil_cd
    ,NVL(ap_adr_seq,'') ap_adr_seq
    ,NVL(ar_adr_seq,'') ar_adr_seq
    ,ps.net_payable
    ,ps.net_receivable
    ,ps.net_adjustment
    ,ps.ref_invc_no
    ,c.clsf
    ,max(decode(naid.ba_use, 'W','W','C')) ba_use
    from party_settle ps, paystatn p, codetabs ct, ba_hdr b, contract c, naid
    where ps.ps_no = 'LAM'
    and ps.busn_dt = TO_DATE('01-JUL-2003','DD-MON-YYYY')
    and p.ps_no = 'LAM'
    and p.busn_dt = TO_DATE('01-JUL-2003','DD-MON-YYYY')
    and ps.ctr_no = p.mstr_ctr_no
    and c.ps_no = 'LAM'
    and ps.ctr_no = c.ctr_no
    and c.edt_to >= TO_DATE('01-JUL-2003','DD-MON-YYYY')
    and c.edt_from<= TO_DATE('01-JUL-2003','DD-MON-YYYY')
    and '177' = ct.table_id(+)
    and p.loc_code = ct.table_key(+)
    and substr(c.ctr_pty_no,1,6) = b.ba_no(+)
    and rtrim(c.ctr_pty_no) = rtrim(naid.na_id)
    and nvl(c.doi_disb_fl,'N') <> 'Y'
    group by ps.ctr_no, table_dec, b.ba_no, actpay_cd, actrec_cd, affil_cd,
    ap_adr_seq, ar_adr_seq, ps.net_payable, ps.net_receivable, ps.net_adjustment
    , ps.ref_invc_no, c.clsf
    union
    select distinct
    ps.ctr_no
    ,NVL(table_dec,'XXXX') DEPT_ID
    ,NVL(b.ba_no,'XXXX') ba_no
    ,NVL(actpay_cd,'') actpay_cd
    ,NVL(actrec_cd,'') actrec_cd
    ,NVL(affil_cd,'') affil_cd
    ,NVL(ap_adr_seq,'') ap_adr_seq
    ,NVL(ar_adr_seq,'') ar_adr_seq
    ,ps.net_payable
    ,ps.net_receivable
    ,ps.net_adjustment
    ,ps.ref_invc_no
    ,c.clsf
    ,max(decode(naid.ba_use, 'W','W','C')) ba_use
    from party_settle ps, ctr_man_adjust cma, codetabs ct, ba_hdr b, contract c,
    naid
    where ps.ps_no = 'LAM'
    and ps.busn_dt = TO_DATE('01-JUL-2003','DD-MON-YYYY')
    and cma.ps_no = 'LAM'
    and cma.busn_dt= TO_DATE('01-JUL-2003','DD-MON-YYYY')
    and ps.ctr_no = cma.ctr_no
    and c.ps_no = 'LAM'
    and ps.ctr_no = c.ctr_no
    and c.edt_to >= TO_DATE('01-JUL-2003','DD-MON-YYYY')
    and c.edt_from<= TO_DATE('01-JUL-2003','DD-MON-YYYY')
    and '177' = ct.table_id(+)
    and cma.loc_code = ct.table_key(+)
    and substr(c.ctr_pty_no,1,6) = b.ba_no(+)
    and rtrim(c.ctr_pty_no) = rtrim(naid.na_id)
    and nvl(c.doi_disb_fl,'N') <> 'Y'
    group by ps.ctr_no, table_dec, b.ba_no, actpay_cd, actrec_cd, affil_cd,
    ap_adr_seq, ar_adr_seq, ps.net_payable, ps.net_receivable, ps.net_adjustment
    , ps.ref_invc_no, c.clsf
    )
    order by 1,2

  2. #2
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Can you post the "expain plan"?

    On first glance...
    Why do you need a distinct if you're grouping (via max())?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #3
    Join Date
    Jan 2001
    Posts
    515

    Use bind variables

    Make sure you are passing bind variables.

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    max(decode(naid.ba_use, 'W','W','C')) ba_use

    wht does MAX() do in this DECODE
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width