Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: Query performance issue - please help

    hello
    please help me rewrite this query as soon as you can, causing lot of performance issues. thanks,

    select A.xpr_num xpr_num, D.bill_cd bill_cd, B.abbr_cd abbr_cd,
    B.ctg_num ctg_num, B.hth_cd hth_cd, C.due_key_id due_key_id,
    sum(C.actn_ct) actn_ct from PR_AG A, PRM_CV B, PRM_FAC C, BILL_FM D
    where A.PR_AG_KEYID = C.PR_AG_KEYID
    and B.PRM_CV_KEYID = C.PRM_CV_KEYID
    and D.bill_cd_keyid = C.bill_cd_keyid
    AND
    A.xpr_num = '01222' and due_key_id = (SELECT MAX(due_key_id) from PR_AG AA, PRM_CV BB, PRM_FAC CC, BILL_FM DD
    where AA.PR_AG_KEYID = CC.PR_AG_KEYID
    AND BB.PRM_CV_KEYID =CC.PRM_CV_KEYID
    AND DD.bill_cd_keyid = CC.bill_cd_keyid
    AND A.xpr_num = AA.xpr_num and D.bill_cd=DD.bill_cd
    AND B.abbr_cd=BB.abbr_cd) group by
    A.xpr_num, bill_cd, abbr_cd, ctg_num, hth_cd, due_key_id;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    which table has the "due_key_id" field?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Posts
    2

    Re: Query performance issue

    The table PRM_FAC has the due_key_id field.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The only sure way to learn where any query is spinning it wheels is to enable SQL_TRACE & run the results thru TKPROF.
    You might want to try re-writing the sub-query similar as below...
    SELECT MAX(CC.due_key_id)
    from PRM_FAC CC
    where exists (select '1'
    from PR_AG AA
    where AA.PR_AG_KEYID = CC.PR_AG_KEYID
    AND A.xpr_num = AA.xpr_num
    )
    AND EXISTS (select '1'
    from PRM_CV BB
    where BB.PRM_CV_KEYID = CC.PRM_CV_KEYID
    AND B.abbr_cd = BB.abbr_cd
    )
    AND exists (select '1'
    from BILL_FM DD
    where DD.bill_cd_keyid = CC.bill_cd_keyid
    and D.bill_cd = DD.bill_cd
    )
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Please post an autotrace/explain plan and also a tkprof output.
    thanks.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Besides the explain plan to be know, you have to make sure you user CBO and the statistics for the tables/indexes involved in the query are available for the optimizer to make the right decision.

    W/o the explian plan you will be going blindly and may be in a wrong direction. Post the plan please !!!

    HTH,

    clio_usa - OCP 8/8i/9i DBA

Posting Permissions

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