The thing is, this index also exists in the better performance schema, why
did the optimizer not use the index in that schema when both schemas were
analyzed for tables and indexes?
"Vinayak Kulkarni" <kdotvinayak.void_at_usadot.net> wrote in message
news:9lh43t$qfp$1_at_new-proxy.bscc.bls.com...
> A quick look at the explain plan indicates that there exists an index on
> TAB_TRIPS in the schema with **poor** performance. It seems that the query
> performs better with a FTS.
>
> HTH.,
> Vinayak
> --
> I never X-post.
>
> "Leigh Gold" <intan_5ee_at_yahoo.com> wrote in message
> news:9lgq1t$rfl$1_at_news3.cadvision.com...
> > I have 2 schemas with exactly the same objects (tables, indexes,
triggers,
> > procedures, etc), and both are analyzed for tables and indexes.
> > However, when I did the same transactions to these 2 schemas with the
same
> > number of rows, one was done in under a minute and the other over 2
> minutes.
> >
> > I did a trace on both with their session ids (the transaction were done
> > through an app), I noticed the only different from the tkprof output
were
> as
> > listed below.
> >
> > Can anyone help in explaining what caused the difference?
> >
> > ** schema with better performance **
> >
> > Rows Execution Plan
> > ------- ---------------------------------------------------
> > 0 SELECT STATEMENT GOAL: CHOOSE
> > 92 NESTED LOOPS
> > 93 NESTED LOOPS (OUTER)
> > 93 NESTED LOOPS (OUTER)
> > 93 NESTED LOOPS (OUTER)
> > 93 NESTED LOOPS (OUTER)
> > 93 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> > 'TAB_TRIPS'
> > 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> > 'TAB_WAYBILL'
> > 92 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> > 'TAB_WAYBILL_PK' (UNIQUE)
> > 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> > 'TAB_CONTRACT_RATE'
> > 92 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> > 'TAB_CONTRACT_RATE_PK' (UNIQUE)
> >
> >
> > ------------------------------------------------------------------
> > ** schema with slow performance **
> >
> > Rows Execution Plan
> > ------- ---------------------------------------------------
> > 0 SELECT STATEMENT GOAL: CHOOSE
> > 92 CONCATENATION
> > 1 NESTED LOOPS (OUTER)
> > 2 NESTED LOOPS (OUTER)
> > 2 NESTED LOOPS
> > 2 NESTED LOOPS (OUTER)
> > 2 NESTED LOOPS (OUTER)
> > 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> > 'TAB_TRIPS'
> > 2 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> > 'TAB_TRIPS_IDX1' (UNIQUE)
> > 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> > 'TAB_CONTRACT_RATE'
> > 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> > 'TAB_CONTRACT_RATE_PK' (UNIQUE)
> > 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> > 'TAB_WAYBILL'
> >
> >
> > Thanks,
> >
> > L.Gold
> >
> >
>
>
Received on Thu Aug 16 2001 - 17:15:08 CDT
