[askdba] SQL -HELP

  • From: VuCanDo <vuquyen@xxxxxxxxx>
  • To: ASKDBA <askdba@xxxxxxxxxxxxx>
  • Date: Tue, 7 Dec 2004 15:49:15 -0800

Hi Guru,

   I have a SQL statement and run against 2 schemas exactly the same,
but both give me a different explain plan run on same instance 9.2.0.4
on NT.  Schema#2 seem to take longer then schema#1 as you can see. 
What's wrong with this SQL?  See below:

select m.score_dt, count(1), sum(paid_amt), sum(allow_amt),
min(overall_score), max(overall_score)
from spy_mart_score m, spy_claim_case c
where m.claim_id = getClaim(c.claim_id) and m.claim_line =
getLine(c.claim_id) AND c.status = 'PEND' AND case_queue = '1'
group by m.score_dt
order by m.score_dt desc;

 

Explain plan from SCHEMA#1 is

 

FIRST_ROWS    SELECT STATEMENT   Cost = 22

1.1     SORT GROUP BY
2.1       TABLE ACCESS BY INDEX ROWID SPY_SCORE
3.1         NESTED LOOPS
4.1           NESTED LOOPS
5.1             TABLE ACCESS FULL SPY_CLAIM_CASE
5.2             TABLE ACCESS BY INDEX ROWID SPY_MART
6.1               INDEX UNIQUE SCAN SPY_MART_K1
4.2           INDEX RANGE SCAN SPY_SCORE_K1

 

Explain plan from SCHEMA#2 is

 

FIRST_ROWS    SELECT STATEMENT   Cost = 27169

1.1     SORT GROUP BY
2.1       NESTED LOOPS OUTER
3.1         HASH JOIN
4.1           TABLE ACCESS FULL SPY_CLAIM_CASE
4.2           TABLE ACCESS FULL SPY_MART
3.2         TABLE ACCESS BY INDEX ROWID SPY_SCORE
4.1           INDEX UNIQUE SCAN SPY_SCORE_K1

Thanks,

Vu

Other related posts: