Optimiser question

[JMac]

Optimiser init ora parameter is set to CHOOSE.

The DB has never been ANALYZED.

Will the optimser always choose the Ora 8 RULES then?

[julian]

Depends. Generally, since there are no statistics, RULE will be used. But there are some exceptions: if IOTs or partitioned tables are invloved COST will be used. It's not that simple.

[sjadhavdba]

Originally posted by julian
It's not that simple.

Julian, can you explain me in detail !

[julian]

Julian, can you explain me in detail !

Here is how Oracle 'thinks' (according to Oracle himself):

There is a query. If one of the following is true (=Yes), then COST is used:

1. Is there a hint for table access, an ORDERED, FIRST_ROWS or ALL_ROWS hint ?
2. 7.3+ Does any table have PARALLEL set ?
3. 8.0+ As above and additionally, are there any: Index Organised Tables (IOT) or Partition Tables involved?
4. 8.1+ As above and additionally, are there any: Domain Indexes (Inter Media) or Parallelism set on Indexes or sample clauses or Parallel CTAS or Function-based indexes or Reverse Key Indexes or Query rewrite or materialized views.

If No, then Oracle checks if there is a RULE hint. If Yes, then RULE is used, if No, then Oracle checks if RULE is set at SYSTEM or SESSION level and if there is no CHOOSE hint. If Yes, then RULE is used, if No, then Oracle checks if ALL_ROWS or FIRST_ROWS set at to Yes at SYSTEM or SESSION level and if no, then CHOOSE hint is used. If Yes, then COST is used, if No, then Oracle checks if there are statistics for any of the tables. If Yes, then COST is used, otherwise RULE.