Re: Analyzing Table

"Alfonso Fecál" <AlfonsoF_at_hotmail.com> wrote in message news:wrwC7.40087$%B6.16081144_at_typhoon.southeast.rr.com...
> Sorry if this question is silly to the group. If I have a table with
> zero rows, do I need to analyze it, or will the optimiser behave the
> same way whether it is analyzed or not.
>
> In other words, does it know from looking at the LAST_ANALYZED column
> whether or not the NUM_ROWS value of zero is defaulted or calculated.
>
> I am speaking of 8.1.6.x
>

No, the optimizer will not necessarily behave the same way.

(I'm assuming here that by "analyze" you are referring to using the analyze statement to collect statistics, not validate structure.)

In the absence of any hints in a SQL SELECT statement...

If optimizer mode is set to CHOOSE, and a query involves only tables that do not have statistics, then the optimizer will use RBO (RULE based optimization). If any of the tables referenced in the query do have statistics, then the optimizer will use CBO (COST based optimization) and will estimate statistics for any tables that do not have statistics.

Is it going to make a difference in your case? That we can't tell without having more details. Do you have a reason for not using CBO and collecting statistics on your tables? Received on Sat Oct 27 2001 - 17:49:03 CDT