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

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 Tue May 02 2000 - 00:00:00 CDT