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.

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.

Jim Starkey