Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Sql Coding Cost Too Much -- Help

Sql Coding Cost Too Much -- Help

From: Steve <schen_at_prodigy.net>
Date: Thu, 17 Jan 2002 22:59:14 GMT
Message-ID: <6HI18.19523$IS6.1743089859@newssvr15.news.prodigy.com>


Hi Guru,

It would be very appreciated if someone give comment to the following coding that cost a lot to execute.



select
       aq.PR_num,
       aq.appl_id,
       aq.PROT_ID,
       aq.QUES_ID,
       aq.QUES_TXT,
       aq.QUES_CD,
       aq.ANS_IND,
       aq.DISP_num

  FROM
                                                    applicant  aa

 WHERE aa.pr_num (+) = aq.pr_num
   AND aa.appl_id (+) = aq.appl_id

   AND aa.ques_id (+) = NVL(aq.val_ques_id,999999)    AND aa.ans_txt (+) = NVL(aq.ans_txt,999999)  GROUP BY aq.PR_Num,
       aq.appl_id,
       aq.PROT_ID,
       aq.QUES_ID,
       aq.QUES_TXT,
       aq.QUES_CD,
       aq.ANS_IND,
       aq.DISP_Num

HAVING COUNT(aa.ans_txt) = 0
/

Query Plan



SELECT STATEMENT Cost =30477160
  FILTER
    SORT GROUP BY
      MERGE JOIN OUTER
        SORT JOIN
          VIEW
            HASH JOIN
              HASH JOIN OUTER
                HASH JOIN
                  TABLE ACCESS FULL PROT
                  TABLE ACCESS FULL QUES
                TABLE ACCESS FULL LOGIC
              INDEX FAST FULL SCAN PR_A_PK
        SORT JOIN
          TABLE ACCESS FULL APPLICANT


------------------------------------------------------------

Note: The applicant table consists of 11 millions rows. This execution cost = 30 million in full table scan.
I added index hint on first selection of the coding, but the cost is about the same, though "index full scan index_name on applicant table" is shown.

I suspects that the problem is on the clause of "having count(aa.ans_txt) = 0 ", with which aa (applicant table) has been accessed twice.

Then what would be way to optimize the query?

All help will be very appreciated.

Steve Received on Thu Jan 17 2002 - 16:59:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US