Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Sql Coding Cost Too Much -- Help
Hi Guru,
It would be very appreciated if someone give comment to the following coding that cost a lot to execute.
aq.PR_num, aq.appl_id, aq.PROT_ID, aq.QUES_ID, aq.QUES_TXT, aq.QUES_CD, aq.ANS_IND, aq.DISP_num
applicant aa WHERE aa.pr_num (+) = aq.pr_num AND aa.appl_id (+) = aq.appl_id
aq.appl_id, aq.PROT_ID, aq.QUES_ID, aq.QUES_TXT, aq.QUES_CD, aq.ANS_IND, aq.DISP_Num
Query Plan
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