Subject Re: [firebird-support] Re: Optimizer randomly chooses plans (WAS Big table and DB slowdowns)
Author Helen Borrie
At 04:02 PM 3/09/2004 +0200, you wrote:

> > Set's strategy, for making it
> > ignore that index, will help avoid this slow-down effect from the
> > mistakenly choosing that index, based on old statistics.
>And can I be sure, using this technique, that the optimizer has enough hints
>to always pick the right PLAN ? make it choose the SAME plan. Not necessarily the right one,
though. Most of the time, the optimizer is right when it decides on
Natural - it has worked out that it would be cheaper than riding the
index. This isn't SQLServer, which is entirely rule based and uses a
completely different architecture for indexing and optimization, based on
rules alone.

With a relatively smaller set on the left, the Fb optimizer will often -
correctly - choose the natural scan, because the left stream will be built
into a bitmap, which will be faster than pulling up the index pages.