Hello Dmitry,

>> 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.

I hope we are not talking about re-preparing in general, but just for
statements where an execution plan could change, if parameter values
would have been known before query preparation. A LIKE operand in the
SQL statement is one example.

In general, using prepared statements giving me better performance is
one use case. Another one is taking SQL injection etc. into account.

> 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.

Sounds good. Again. Only for those kind of statements, where an
execution plan could differ for already known parameter values.

> It costs you a bit of the
> extra prepare time and used memory but allows the tricks like the one
> you're speaking about.

The extra prepare time might be an issue, as we have seen the difference
in a firebird-support discussion on a largish table with a lot of
pointer pages, which are read at prepare time, AFAIK.

> And the appropriate mechanism already exists in
> FB3 (see CORE-3076 for example of its usage).

Right. Thanks.

> 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.

Ok. For the LIKE example. I could for sure add some logic in the client
code to differ between the usage of LIKE and STARTING WITH depending on
the wildcard pattern in a search field, but if the engine could do that,
it would be at a central place and the engine already transforms a LIKE
into a STARTING WITH, if e.g. LIKE 'Firebird%' is used, so no trailing %
wildcard.

> Feel
> free to please the tracker with your suggestion :-)

Will do. Thanks!


Regards,
Thomas