Re: RBO vs CBO

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Wed, 18 Sep 2002 11:00:42 +1000
Message-ID: <05Qh9.35033$g9.98760@newsfeeds.bigpond.com>

"Jennifer" <Jennifer_at_DAG[DELETEME].ca> wrote in message news:gPHh9.12512$V21.290128_at_news...
>
> Is it true that, even if the OPTIMIZER_MODE is set to RULE, that once
> ANALYZE is performed on a table, the Cost Based Optimizer is invoked
> instead?
>

Hi Jennifer,

No, it's not true. If the OPTIMIZER_MODE is set to rule then statistics have no effect.

However if the OPTIMIZER_MODE was set to CHOOSE, then yes it would have an effect (as it uses RBO if no referenced object has statistics and uses CBO if any referenced object has statistics).

Note that hints also uses the CBO regardless if OPTIMIZER_MODE is set to rule. The only exception to this is the RULE hint.

> If this is not the case, how can I switch to the cost-based optimizer
while
> keeping the OPTIMIZER_MODE = RULE in the "init.ora" file?

You can switch to the CBO in one of two ways:

ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS (or FIRST_ROWS or CHOOSE) Note: 9i has a number of new first_row options.
Use any appropriate hint (SELECT /*+ hint */ ...)

Cheers

Richard
>
> --
> _________________________________
> Jennifer Champagne
> Decision Academic Graphics Inc.
> Tel: (613) 233-2365 x226
> Email: Jennifer.Champagne_at_DagSoft.com
>
>
Received on Tue Sep 17 2002 - 20:00:42 CDT