Wouldn't it be better to always use plans then for frequently used complex
queries, to minimize the optimization phase time?

In article <3.0.5.32.20000326125355.00a03e40@...>, Jim
Starkey wrote:
- snip

>
> The Interbase optimizer has three general phases: join order selection,
> index join generation (streams), and joining of streams
> into rivers by sort/merge.
>
> Join order selection is cost based with the optimizer make a crude
> guess of the number of pages necessary to get to a record. This
> involves guesses of the cardinality of the various tables and
> selectivity on indexes, none of which is known with any accuracy.
> In theory this is simple: consider all possible join orders and
> pick the cheapest. And this is what V1 did. Worked pretty well.
> Then somebody threw a 17 way join. It still worked pretty well --
> the resultant query executed in less than 10 seconds. Unfortuantely,
> the optimization phase took 14 hours.
>

- snip

>
> <flame>
> A cost based optimizer is an attractive nuisance. Many
> people think in terms of rule based optimizers (which don't
> scale with complexity), and can't resist the temptation
> to override a cost based optimizer with the odd rule there
> and there to get around a glitch. This usually (read
> "always") breaks the cost base scheme with really embarrassing
> pessimizations. Rather than go back and repair the
> architectural damage, the temptation is to build in a
> mechanism of "plans" to report and/or override the optimizer.
>
> A plan, in essense, is a bug on a bug. So the failure to
> backup a plan required by a database procedure in gbak
> is a bug on a bug on a bug. Plans are definitely in the
> category of things database developers invent to make
> poor performance somebody else fault.
> </flame>
>

- snip

* eMailBye
* Christer Matson, Siljansnas, Sweden
* Science etSense AB
* Mon, 27 Mar 2000 08:56 +0200