Re: Strange optimizer behavior

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.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Oct 31 1999 - 18:34:13 CST