Re: RULE vs COST based optimiser

In article <4kuu1q$130_at_red.interact.net.au>,

   grant_at_towersoft.com.au (Fuzzy) wrote:
>A 750 Meg Oracle database is split roughly between 500 Meg of data and
>250 Meg of indexes. Any ideas on what kind of improvement (if any)
>will be made by switching to cost-based?

warning: religious issue.

thru 7.2.x, the cost optimizer is deadly whether or not you have used analyze table. especially for 4GL tools which generate SQL, it tends to assume wontonly that sequential scans are cheaper than indexed lookups. we have not performed a study of the issue, but have observed that by following the traditional oracle rules (and using rule hints, or rule-by-default mode) we can always improve performance.

the best oracle consultants in the world will admit that the current cost optimizer cannot outperform hand-optimization in every case.

otoh, there may be cases in which carelessly-written queries might benefit from the cost method and suffer under the rule method. however, would you run such a query in a production application, irrespective of optimizer choice?

so the answer to your question is, on the whole, negative "improvement".

--
Dave Mausner, Consulting Manager, Braun Technology Group, Chicago.