Search code examples
sqlfirebirdsql-execution-planinsert-updatefirebird2.5

How to use PLAN with UPDATE OR INSERT INTO

I am using Firebird 2.5 and came across a problem that I can not resolve by myself.

There is a statement using UPDATE OR INSERT INTO. I would like to make it use a specific execution plan. But - no matter where I place the PLAN - I get following error message (line number varies with PLAN's position):

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 2, column 5.
plan.

I did not find anything about the usage of PLAN with UPDATE OR INSERT INTO in the corresponding documentation.

Aspects of my question: Is it even possible to use them together? Does this work or is it planned to work in a later version of Firebird? Is there an obvious reason it does not work, that I did not see? Which alternatives exist to circumvent this?

Solution

  • It is not possible to do this in Firebird 2.5 (and also not possible in 3.0). Looking at the parser definition, the PLAN clause is only supported on:

    For a merge statement it should be possible to specify a plan for the source (if it is a select query), but not for the merge itself. The plan clause is not defined for update or insert (nor is it for insert, for example).

    As far as I am aware there is nothing planned to add this to Firebird 4. You should consider adding an improvement ticket in the tracker, but I don't know if this is even possible at all.