14.03.2012 13:38, Thomas Steinmaurer wrote:

>
> When a parametrized query containg a LIKE operator gets prepared, the
> optimizer doesn't know the parameter value for the LIKE operand, thus it
> won't take an index on that column into account.
>
> Imagine I feed the parameter value with something like 'Firebird%', an
> index could be used of course, but not in case of a parametrized query.
> Would it be technical possible in Firebird to produce some kind of
> re-evaluate an adapted plan once the parameter values are known?

I have a feeling that re-preparing a query is neither trivial nor
sometimes desirable "feature". And it's not a short-term task for sure.

Another solution I can think of is to prepare this particular retrieval
with two different access paths (NATURAL and INDEX) and choose between
them at runtime based on the parameter value. It costs you a bit of the
extra prepare time and used memory but allows the tricks like the one
you're speaking about. And the appropriate mechanism already exists in
FB3 (see CORE-3076 for example of its usage).

However, the issue with LIKE is a bit harder to implement, as in fact
it's not LIKE that gets optimized via an index lookup, instead the
engine injects an artificial STARTING predicate which is optimized as
usual (via an index) and ANDed to the original LIKE predicate. So the
conditional part should be clever enough to use either one or two
boolean conditions at runtime. This goes outside the pure optimizer
logic and involves other layers as well. Doable though, methinks. Feel
free to please the tracker with your suggestion :-)


Dmitry