Re: EXPLAIN PLAN : better with RULE!

On 17 Jul 2002, Alex Filonov wrote:

> I had bad experience after upgrade from 8.0.6 to 8.1.6. Several complex
> queries which ran fine on 7.3 and 8.0.6 with RBO started running forever
> on 8.1.6. I found several problems.

I had a similar problem with Siebel under 8.1.7, and here is what Oracle said:

Cost mode will be forced when there are any statistics on any table involved in the query.

This will also happen when you are using any of the following options or features of the Oracle Database:

1. Partitioned Tables
2. Index-Organized Tables
3. Reverse Key Indexes
4. Funciton Based Indexes
5. Sample clause in a select statement
6. Parallel execution or Parallel DML
7. Star Transformations
8. Star Joins
9. Extensible Optimizer
10. Query Rewrite (Materialized Views)
11. Progress Meter
12. Hash Joins
13. Bit Map Index
14. Partitioned Views
15. Hints

If any of these are being used, then even if the OPTIMIZER_MODE is set to RULE, CBO will be forced. Discontinue use of these features to return to RULE.

   / Charles J. Fisher                   |"How many who came into this world /
  /  cfisher_at_rhadmin.org                 | with me have already left it!"   /
 /   http://rhadmin.org                  |       -Marcus Aurelius          /
---------------------------------------------------------------------------