--- In firebird-support@yahoogroups.com, "jstahl80" wrote:

> --- In firebird-support@yahoogroups.com, Svein Erling Tysvær wrote:
> > select ...
> > from wb_prg_run w inner join objectmap m on (w.objid = m.objid)
> > inner join ch_pos_charge p on (m.to_objid = p.objid+'')
> >
> > prevents using an index for p.objid in the JOIN ... ON clause (if
> > p.objid isn't a string, you may have to use 0 rather than ''). Of
> > course, a WHERE clause may make this index useful again, but the
> > above change is a strong hint to use P as the first table in the
> > plan.
>
> I tried this, but +'' is not accepted by sql parser. But after some
> research in the back-ends of my memory I remembered that to concat
> strings one has to use ||. p.objid||'' is working so far, the
> optimizer is walking as i want it now.

Yes, sorry about that typo. Originally I wrote +0, but then I noticed
that objid was a string and changed to '' without even thinking about
the ||. It must be years since I did this mistake last time.

This solution should permanently prevent plan join (m natural, w
index, p index), the choice for the optimizer is now between plan join
(m natural, w index, p natural) and plan join (p natural, m index, w
index). Of course there are a few more options as well, basically p
has to go natural in any case and then the optimizer can choose
whether or not it wants to use indexes for the other tables (and the
optimizer loves indexes - too much so in my opinion).

Happy to hear you didn't need to use PLAN explicitly,
Set