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

From: Karl R. <kreitsch_at_zdnetonebox.com>
Date: 2000/05/02
Message-ID: <31b879d4.c01427bc@usw-ex0105-034.remarq.com>#1/1

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 whereclause  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