Re: Strange optimizer behavior

In article <7vin65$up$1_at_nnrp1.deja.com>,   Ben Ryan <benryan_at_my-deja.com> wrote:
> In article <7vgcce$h5l$1_at_nnrp1.deja.com>,
> gary_and_lilia_at_my-deja.com wrote:
> > We have an SQL statement similar to the following:
> >
> > SELECT *
> > FROM A,B
> > WHERE A.CODE = B.CODE
> > AND A.FIELD1 = 1
> > AND A.FIELD2 BETWEEN 100 AND 200
> >
> > Both tables have a million rows.
> > Currently, table A has only 1 distinct value for
> > FIELD1, while there are 800,000 distinct values
> > for FIELD2. FIELD1 and FIELD2 are both indexed.
> >
> > If statistics are not generated for table A, the
> > Oracle optimizer chooses FIELD1 as the index to
> > use. If statistics are then generated, the
> > optimizer chooses FIELD2 as the index to use.
> >
> > So far, this is predictable. However, after I
> > delete the statistics for table A, the optimizer
> > continues to choose FIELD2 as the index to use.
> > I would expect it to go back to using FIELD1.
> >
> > We are using Oracle 7.3.4.0. Can anyone explain
> > why this behavior would occur?
> >
> > Thanks,
> > Gary Hirschhorn
>
> I would guess that when you created the statistics,
> Oracle marked the execution plan for SELECT statement
> invalid. Then when you re-executed the statement a new
> execution plan was therefore created. However, when you
> deleted the statistics, Oracle did not invalidate
> the existing execution plan, so when you re-tried
> the SELECT statement it used the cached plan. You
> could test this by changing the case of one letter
> in your select statement, after you delete the
> statistics.
>

Another think to check: When you put statistics on Table A did you also put statistics on Table B. If so did you remember to remove them also? [For anyone real new, if any table in a join has statistics on it in a non-hinted SQL statment then the optimizer will make assumptions about the non-analyzed tables and run cost based assumming the database is not set to rule]

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Nov 01 1999 - 12:00:14 CST