Partitioning and the rule-based optimiser

[normanpang]

A client currently has an 8.1.7.10 database running in rule-based mode. We are thinking of implementing partitioning to help manage a few of their big tables. What features will be unavailable with partitioning with RBO?

thanks

[Zaggy]

Well, from what I have read and my personal experience, RBO does not work on partitioned tables.

Also, partitioned tables can have two types of indexes in relationship to the partitions. You can have global and/or local indexes. Local indexes require less maintenance when DDL operations are conducted on the partitioned table, but are not as optimal as a global index. Global indexes have great performance over local, but are sensitive to DDL operations, which require rebuild after most DDL operations.

I would also check metalink on problems with partition tables and parallel query. Some of these problems maybe show stoppers for you or parallel query may have to be turned off for certain SQL.

[Raminder]

Oracle Partitioning requires Cost Based Optimization to be in place.
Why stick on with Rule based optimization if db is on Oracle 8i ?

[sudip]

If thinking on shifting to CBO then do take some time to check yr system for performance. You may have to apply to specific queries to tune them.

[pando]

Raminder sometimes you are forced to use RBO by the application makers for example Oracle Financials (yea it sounds ironic when Oracle application is using RBO and want to rule it out in its rdbms server). Also Siebel products suggest using RBO, as well as data dictionary uses RBO

Back to normanpang´s question, before you want to dig in partitioning you better talk to your application maker why they suggest you to use RBO because I dont think partitioning works with RBO as others suggested

[normanpang]

As stated by pando, we do not have the option of switching to CBO due to the application.

I'm still wondering what I can do and can't do with partitioning using RBO.

[Raminder]

Thanks Pando for the important info.
I will keep it in mind if ever I get a chance to use Oracle Financials.

[jmodic]

Thera are two main areas that are touched by partitioning:

a) managebility
b) performance

As far as performance is concerned (or beter to say, as much as optimizer's part of the performance is concerned), you will not see any improvements with partitioning if running RBO.

But as managebility is concerned, you get all the goodies that partitioning offers, no matter if you use CBO or RBO.

[pss]

I have 2 databases with partitioned objects on-side. The first one is on NT uses Oracle 8.0.5 with few partitioned tables half a billion rows in each. I rebuilt it after crash a month ago. It had CBO, statistics and global indexes befor crash. Now it has local indexes, RBO and NO statistics in data dictionary tables. Same queries work good (with the same speed or a little bit faster than it was before).
The second instance is on HP using Oracle 8.1.6 64-bit. We have statistics in data dictionary but RBO in init.ora and global indexes. We have a lot of problem with performance. It looks like Oracle uses CBO descpite RBO parameter in init.ora
So you may have different behaviour on different Oracle RDBMS version.

[pss]

On Metalink I found notes that we have to deal with CBO if we have any kind of parallel query parameters (on object or database level) no matter what optimizer mode we have in init.ora file.

Sergey Popov