Re: Coste Based Optimizer get confused after raneming a table ( Indexes will not be used )

If all else fails, I imagine you can always build new indexes on those tables, based on the old index structure (?).

Pat.

Gerd Kock wrote:

> did you use estimnate statistics or compute?
> Try to compute statistics. Or try these
>
> analyze table ertrag delete statistics
> analyze table ertrag COMPUTE statistics for all indexes
> analyze table ertrag validate structure cascade
>
> Gerd Kock
> ex
> CSC Ploenzke AG
> now
> www.BITS-on-the.net
>
> On Tue, 02 May 2000 01:22:07 -0700, Karl R. <kreitsch_at_zdnetonebox.com>
> wrote:
>
> >ORACLE 7.3.4.1, AIX 2.X
> >PROC 2.2
> >
> >Table ERTRAG 700.000 Rows, Table AUX_VERTRIEBSPARTNERLISTE
> >10.000 Rows
> >
> >After copying table to ertrag with less rows due to a where-
> >clause to ertrag_tmp
> >and renaming the origanl table ERTRAG to ertrag_0427 and
> >renaming ertrag_tmp to ERTRAG and rebuilding the indexes, the
> >Cost Based Optimizer
> >do not know the Indexes on the new ERTRAG-table. Only in rule-
> >based Mode( after deleting statistics ) the index is used.
> >
> >How can i use the table ERTRAG in cost Based mode again, with
> >acknowledging the indexes ?
> >I droped the ertrag_0427-table and rebuild the statistics on
> >table ERTRAG, but the problem still remains.
> >
> >
> >Statement
> > SELECT
> > E.ID ,
> > E.HERKUNFT ,
> > E.FK_DIMZ ,
> > E.FK_DIMV ,
> > E.FK_DIMD ,
> > E.FK_DIMC ,
> > E.FK_DIMT ,
> > E.FK_DIME ,
> > E.FK_DIMX ,
> > E.ERTRAG_BRUTTO ,
> > E.ERTRAG_NETTO ,
> > E.ERTRAG_MWST ,
> > E.ANZAHL_COUPONS ,
> > E.VJ_ERTRAG_BRUTTO ,
> > E.VJ_ERTRAG_NETTO ,
> > E.VJ_ERTRAG_MWST ,
> > E.VJ_ANZAHL_COUPONS ,
> > E.FK_ANWD_UPDATE ,
> > E.UPDATE_USER ,
> > E.UPDATE_TIMESTAMP ,
> > :b0
> > FROM
> > ERTRAG E ,
> > AUX_VERTRIEBSPARTNERLISTE A
> > WHERE
> > ((A.FK_DIMV=:b1 and
> > A.REFERENZDATUM=:b2) and
> > A.FK_DIMV_AGEN=E.FK_DIMV)
> >
> >Execution-Plan
> >RULE BASED
> >
> > SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=112342
> >Bytes=13930408)
> > NESTED LOOPS (Cost=38 Card=112342 Bytes=13930408)
> > INDEX (RANGE SCAN) OF AUXV_IND01 (NON-UNIQUE) (Cost=2
> >Card=18 Bytes=432)
> > TABLE ACCESS (BY ROWID) OF ERTRAG
> > INDEX (RANGE SCAN) OF ERTR_IND01 (NON-UNIQUE)
> >
> >COST BASED
> >
> > SELECT STATEMENT Optimizer=CHOOSE (Cost=1219 Card=2971
> >Bytes=276303)
> > HASH JOIN (Cost=1219 Card=2971 Bytes=276303)
> > INDEX (RANGE SCAN) OF AUXV_IND01 (NON-UNIQUE) (Cost=2
> >Card=18 Bytes=432)
> > TABLE ACCESS (FULL) OF ERTRAG (Cost=1160 Card=775173
> >Bytes=53486937)
> >
> >
> >Karl Reitschuster
> >Senior Consultant CSC Ploenzke AG
> >Oracle Databases, Implementation, Performance-Tuning
> ><!Jesus is Lord!>
> >* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
> >The fastest and easiest way to search and participate in Usenet - Free!
> >
Received on Wed May 03 2000 - 00:00:00 CDT