Is the stats updated ? what about the data each table is holding are they similar ? Regards Kurian On Wed, 8 Dec 2004 09:35:03 +0530, Abraham Kurian <kurian24@xxxxxxxxx> wrote: > Is Stats are updated ? what about the amount of data each table in holding ? > > Regards > Kurian > > > > > On Tue, 7 Dec 2004 15:49:15 -0800, VuCanDo <vuquyen@xxxxxxxxx> wrote: > > 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 > > > > >