On 22-7-2014 01:36, Jan Petersen jaybird-list-20140721@...
[Firebird-Java] wrote:
...

> SELECT ID, FIRST_NAME, LAST_NAME
> FROM ADDRESS
> WHERE ADDRESS.LAST_NAME LIKE ?;
>
> Parameter 1 = "Smith%"
>
> PLAN (ADDRESS NATURAL)
>
> I tried to force Firebird to use the correct PLAN, but that didn’t work
> either:
> “Index IDX_ADDRESS cannot be used in the specified plan.“
>
> Thinking about it, i understand why the optimizer can’t really know what
> parameters will be used later on und obviously takes the save route by
> omitting the index altogether. But why isn’t it even possible to
> manually suggest a PLAN?
>
> Is there really no possible way to somehow convince Firebird to use the
> index in this case?
> If possible, I really wouldn't want to loose parameters and fiddleing
> with user input by hardcoding the queries.

Suggesting a plan here that uses the index IDX_ADDRESS is not allowed
because a parametrized LIKE simply cannot use an index, therefor the
suggested plan is invalid and rejected.

The ability to use an index with a LIKE and a literal value that does
not start with a _ or % is an optimization that is only possible because
then Firebird knows that it can use the index. With a parametrized LIKE
that is not the case (instead of 'Smith%' you could just as well have
provided '%Smith', and for that value the plan wouldn't work).

As Jiri already suggested, you can use STARTING (or STARTING WITH) if
you always do a prefix search. With this predicate, Firebird is able to
use an index if it is parametrized.

As this question is not directly related to Jaybird (and the use of
Firebird in Java), it would have been more on topic on Firebird-support.

Mark
--
Mark Rotteveel