Re: Partitioned table and cost-based optimization

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:4095ef6a$1_at_olaf.komtel.net...
> 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,

See p.1-4 of "Oracle9 i Database Performance Tuning Guide and Reference" where you will find "Features that Require the CBO". One of those features is partitioned tables.

Douglas Hawthorne Received on Mon May 03 2004 - 02:33:47 CDT