Re: Cost optimizer in Oracle 7.3.2.1.0 for Digital Unix

From: Jonas Plumecocq <jplumeco_at_vitgbsd1.telstra.com.au>
Date: 1996/12/24
Message-ID: <59nkru$jmu@cdn_news.telecom.com.au>#1/1

markp28665_at_aol.com (MarkP28665) wrote:
>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.

We have had similar problems with the cost based optimizer. One query, which normally took approx 5 seconds, took 15 minutes using the cost based optimizer. The cost based optimizer totally buggered up and went for full table scans of all tables. The peculiar thing was, that the thing that caused it to do this was the condition "column_name = columname" as a result of a decode statement (depending on some other value).
It didn't like 1 = 1 either.
Take this out, and it works fine.... If anyone has an explanation for this, I'd like to hear it. We got Oracle world wide support onto it a couple of months ago and haven't heard from them since on the topic.

Cheers,

JP. Received on Tue Dec 24 1996 - 00:00:00 CST