Re: Partitioned table and cost-based optimization

> Hi everyone,
>
> I use an Oracle 9i server on Windows 2000 and I observed the following
> phenomenon: When I select from a partitioned table "A_Partitioned" Oracle
> applies a COST based optimization whereas when I select from an upartitioned
> table "A_Unpartitioned" it applies the RULE based optimization. Both tables
> have the same structure, tablespace, data, indices and so on except that one
> is partitioned the other is not. Even when I use /*+ RULE */ Oracle still
> applies COST based optimization on the partitioned table.
>
> In my case the rule-based approach is much more performant, so I wish to
> make oracle select from the partitioned table in a rule-based manner. Why
> does Oracle act like this and how can I change the behaviour ?
>
> Thanks a lot,
>
> André
> :)
>
> Details:
>
> /*+ RULE */ SELECT * FROM "A_Partitioned" WHERE MSN=1 /*foreign key
> reference and index on MSN column*/ /* Oracle uses COST based opt.*/
>
> /*+ RULE */ SELECT * FROM "A_Unpartitioned" WHERE MSN=1 /*foreign key
> reference and index on MSN column*/ /* Oracle uses RULE based opt. even
> without the +RULE directive*/

Andre,

    don't rely on hints to tell Oracle which optimizer model to use. Use the optimizer_mode initialization parameter. See the reference manual on tahiti.oracle.com for the possible values. If this parameter is set at CHOOSE, then Oracle will decide at run-time to run CBO if at least one "selectable" object (table, view, partition or MV) referenced has stats, otherwise it will use RBO. If you persist on using hints, they have to come AFTER the word SELECT, not before. Posting the execution plans and the important initialization parameters would help us give you more concrete advice.

HTH Daniel Received on Mon May 03 2004 - 11:31:40 CDT