[askdba] Re: SQL -HELP

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

Even I simplified the SQL:

select  count(1)
from spy_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'

From SCHEMA#1

FIRST_ROWS    SELECT STATEMENT   Cost = 12
1.1     SORT AGGREGATE
2.1       NESTED LOOPS
3.1         TABLE ACCESS FULL SPY_CLAIM_CASE
3.2         INDEX UNIQUE SCAN SPY_SCORE_K1 

From SCHEMA#2:

FIRST_ROWS    SELECT STATEMENT   Cost = 957
1.1     SORT AGGREGATE
2.1       HASH JOIN
3.1         TABLE ACCESS FULL SPY_CLAIM_CASE
3.2         INDEX FAST FULL SCAN SPY_SCORE_K1



On Tue, 7 Dec 2004 21:17:24 -0800, VuCanDo <vuquyen@xxxxxxxxx> wrote:
> Yes and the amount of data for SCHEMA#1 is couple millions rows bigger
> than SCHEMA#2.
> 
> 
> 
> 
> 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
> > >
> > >
> >
> >
>

Other related posts: