Re: Cost Based Optimizer Question

Hello

The rule base optimiser will be used if :

• optimizer_mode=rule is set in the ora.ini file
• there are no statistics on any of the tables
• the rule hint is used in the query ie: select /* +RULE */ toto from titi

The cost based optimizer will be used if :

• optimizer_mode=chose or optimizer_mode=cost is set in the ora.ini file
• There are statistics on any of the tables

If you want to use statistics it is generally a good idea to put them on all of the tables in the query otherwise oracle will estimate the missing statistics which might slow down the query. If you don't want the CBO ever then delete the statisitcs. If you want to mix the two use the HINT.

Hope this helps

Paul

In article <392AFD95.67763049_at_deja.com>,   ttrivedi <ttrivedi_at_deja.com> wrote:
>
>
> Hey gurus,
> I have a quick question . Is it true that if the statistics for even a
> SINGLE table is available
> then the cost based optimizer WILL be used irrespective of what is
> specified ?
>
> Like if I do
> analyze table one compute statistics
> then I cannot use the rule based optimizer any more ?
>
> Tapan
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 24 2000 - 00:00:00 CDT