Re: Sql Coding Cost Too Much -- Help

Steve wrote:

> 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
>
> -- inside the following bracket is used to be a view named "aq". I break it
> into codes here -----
> (select pa.PR_Num,
> pa.appl_id,
> pq.PROT_ID,
> pq.DISP_Num,
> q.QUES_ID,
> q.QUES_TXT,
> q.QUES_CD,
> q.ANS_IND,
> ql.pro_ques_id,
> ql.val_ques_id,
> ql.ans_txt
> from proj pa, ques q, prot pq, logic ql
> where q.pr_num = pa.pr_num
> AND pq.pr_num = q.pr_num
> AND pq.quest_id = q.ques_id
> AND ql.pro_pr_num (+) = pq.pr_num
> AND ql.pro_id (+) = pq.pro_id
> AND ql.pro_ques_id (+) = pq.ques_id) aq,
>
> 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
>
>
>

The bad guy is your outher joins, you want the row in the result set even if it dosn't match the criteria, hence you get a full scan. Migth try with unions, but -

Nothing beats poor design in spoiling performance. :-)

/Svend Received on Fri Jan 18 2002 - 12:41:06 CST