Re: Cost Based Analysis

Ken Whitaker wrote:
>
> What is the difference between
>
> analyze table XXXX ESTIMATE statistics;
> analyze table XXXX COMPUTE statistics;
>
>
> I usually am faced with explaining the rule vs cost to developers but this
> question has
> merit. Looking at the explain plan I noticed no difference on queries;
> whereas without
> any analyze ie rule based may use a different explain plan.
>
> Anyone noticed anything, I tend to tell folks to use the estimate as it
> runs faster; why would the
> compute be better to use.

  Sometimes, estimate statistics is equivalent to compute statistics under two conditions:

1. The time to estimate statistics is similar to the compute statistics. Oracle will take compute statistics even though you specify estimate statistics.
2. The percentage is over 50% when you specify estimate statistics. Oracle will take compute statistics even thoug your specify estimate statistics.

   As a result, Oracle may compute statistics under some situations even you take estimate statistics.

   Deniably, the Oracle optimizer is not quite intelligent in some situations. Sometimes, it may take wrong decisions in the data selectivity. As a result, it chooses the wrong execution plan.

---
Name   : Lun Wing San (Certified Oracle Database Administrator)

Title  : Oracle Application Developer of Hong Kong Productivity Council
         Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841

This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.