Re: Cost optimizer in Oracle 7.3.2.1.0 for Digital Unix

To paraphrase >> Cost Optimizer Stinks; Rule based query took 10 minute vs 3 hours for cost <<

How did you generate your statistics? The default estimate is generally unsuitable and often produces results similiar to your complaint. I have seen a query that never finished under rule run in 10 minutes under cost so the optimizer cuts both ways.

Try 'analyze owner.table_name estimate statistics sample 50000 rows;' I have found that this usually works for our 10 to 15 million row tables that we could not compute and the default estimate often estimated a row count that was off by up to two million rows.

Also you may want to make use of hints for those queries that should always use a particular index that the optimizer will not use because it only has a few distinct values. /*+ INDEX (table label out of from cluase, index_name) */ on the select line if you or someone who reads this is unfamiliar with hints.

I think the cost based optimizer works about the same on all platforms, that is in a slightly unpredictable manner.

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Fri Dec 20 1996 - 00:00:00 CST