Re: LBO/CBO

Hi Marc,

Cool, making some progress :)

The reason why the statement is using the RBO if the optimizer_mode is set to choose is because you have no statistics on the table. Choose basically says if there are no statistics on any of the referenced objects I'll use the RBO, else I'll use the CBO (all_rows).

So to use the CBO, you can either:

1. Analyze your tables and generate the statistics the optimizer needs in order to do it's job properly (using the DBMS_STATS package (preferred) or the ANALYZE command). Choose will then use the CBO.
2. Change the optimizer_mode either at the instance level (via the OPTIMIZER_MODE=blah parameter ), or change the setting at the session level (ALTER SESSION SET OPTIMIZER_MODE=blah) or use change at the statement level via a hint.

Note I would strongly recommend generating the statistics regardless ...

The CBO 'blah' options you have are:

ALL_ROWS (optimizer will choose a plan that selects all rows as efficiently as possible)
FIRST_ROWS (optimizer will choose a plan that selects the first rows as efficiently as possible)

As you're on 9.2, you also have these options for the optimizer to select a plan that returns x number of rows as efficiently as possible:

FIRST_ROWS_1
FIRST_ROWS_10
FIRST_ROWS_100
FIRST_ROWS_1000

With the hint option, you can select any number rows /*+ FIRST_ROWS(42) */

Select the CBO option which best suits your needs (and remember those statistics, they're vital).

Cheers

Richard
"Marc Zinzen" <mzinzen_at_genese.de> wrote in message news:3D9437D3.ACE15686_at_genese.de...
> Hi.
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------

--


>

> --------------------------------------------------------------------

> | Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

> --------------------------------------------------------------------

> |   0 | SELECT STATEMENT

> |   1 |  TABLE ACCESS FULL   | T_IND_UNT   |       |       |       |

> --------------------------------------------------------------------

>

> Note: rule based optimization

> The Problem is the Note at the Bottem. Rule bases optimization.

> I did an alter session set optimizer_mode = choose;

> Still it says rule based. But i want to do this query Cost based.

> Where is the switch????? Does anybody know.

> Marc

>