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*/ Received on Mon May 03 2004 - 02:06:17 CDT