On 23 May 2002 at 10:19, Jim Starkey wrote:

> At 11:22 PM 5/22/02 +0100, Jason Chapman (JAC2) wrote:
> >Vince,
> >
> >Now that's a name I haven't seen on the lists for a while.
> >
> >Jim,
> >
> >IYNSHO, how broke is the optimizer? (H=humble) :-)
> >
>
> There are two general models for optimizers: heuristic and
> cost based. A heuristic based optimizer uses a rule set
> to deduce the best order based on a set of rules. A cost
> based optimizer estimates the approximate cost of alternative
> orders and picks the cheapest.
>
> Every budding database design instinctively knows that the
> heuristic approach is the only way to go. Then they learn
> that the combinatorial complexity of a 17 way join causes the
> approach to collapse in a smoking heap of conflicting rules.
>
> The Interbase optimizer was a cost-based optimizer with a
> heavy emphasis of search tree pruning. Sometime in the
> version 4 timeframe somebody thought they could improve
> the optimizer with a few heuristic rules. He was wrong,
> and substantially broken the optimizer.
>
> Fixing the optimizer isn't a question of making it smarter
> but finding and removing a dumb ughly wart.

Did the person that wrote the changes manage to comment and compartmentalize
what they did, or is this something that pollutes the entire optimizer code? In other
words does anyone know if this is simple surgery or will we need to kill the patient in
the process.

> The plan crap evolved solely out of the need to work around
> the busted optimizer. A much, much better idea is to cure
> the problem rather than treating the symptom.
>

I agree, as a DBA/programmer rather then DB developer, I would rather just write
the SQL and let the engine figure out the best way to give me the result, plans are a
poor-cousin work around, caused by a ( justified ) lack of faith in the optimizer.

Paul Schmidt, President
Tricat Technologies
paul@...
www.tricattechnologies.com