[askdba] Re: SQL -HELP
- From: VuCanDo <vuquyen@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Thu, 9 Dec 2004 17:52:10 -0800
Got it working after gather indexes stat for shema#2.
Thanks Abraham.
On Thu, 9 Dec 2004 09:28:51 +0530, Abraham Kurian <kurian24@xxxxxxxxx> wrote:
> hi
> Its possible that , the optimizer would choose the best possible path
> to fetch the rows . in your case the PLAN changes from a Unique Scan
> to a Index FFS ..
> Can you send me the complete Explain Plan with ALL_ROWS.
>
> send me the O/p of
> select * from table(dbms_xplan.display());
>
> Also, the details of indexed columns
>
> regards
>
>
> Kurian
>
> On Tue, 7 Dec 2004 22:48:48 -0800, VuCanDo <vuquyen@xxxxxxxxx> wrote:
> > 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:
