Hi Juan!

The logic of using +0 is very simple. You have an index for T.K_TARGET which the optimizer chooses to use in its plan. T.K_TARGET+0 isn't indexed, hence adding +0 prevents the optimizer from using the index in this case. Then the optimizer must try to find another plan.

Normally, the optimizer finds a good plan in its first attempt without adding +0 anywhere. When it comes up with a sub-optimal plan, +0 is a good way to guide the optimizer away from its original choice (sometimes you need more than one +0). Of course, in many cases adding +0 may result in a worse plan, but it is a good help when used correctly. For character fields, use ||'' instead of +0.

This works safely in all versions of InterBase/Firebird that I've used. The optimizer of course improves from version to version, so having +0 may be required in e.g. Firebird 1.0 to get acceptable performance, but the very same +0 may prevent the optimizer in Firebird 2.0 to use a brilliant plan that Firebird 1.0 never could use. I haven't encountered such a situation and I don't think it would ever really matter since Firebird 2.0 could normally use the same plan as Firebird 1.0 chose, but you may miss some optimizer improvements if you change versions of Firebird without checking the plan with and without +0. For forward compatibility, the only danger is if the optimizer learns that it can use an index for K_TARGET if a constant is added. Since this type of 'manual optimization' is quite common, I doubt this will happen in the foreseeable future.

In your case, I would recommend test 3, simply because TG then can use the index for both K_TARGET and K_GROUP, which would normally be faster than only using the index for K_TARGET.

Generally, if the order of tables in the plan isn't what you want, starting with +0 in the JOIN clause is a good option (my only excuse for starting with the WHERE clause, is that I like to be unpredictable, if I'd been awake I should have known better).

Set

Juan Pedro López wrote:

> Hi Svein.
>
> Thank you very much for your advice.
>
> Here are the results ot the test:
>
> First test, query is quite fast:
>
> SELECT FIRST 10 SKIP 0 T.K_TARGET, T.K_PHONE, T.V_NAME, T.D_CREATION
> FROM TARGETS T JOIN TARGET_GROUPS TG ON TG.K_TARGET = T.K_TARGET WHERE
> T.K_USER=2262 AND T.C_TYPE=0 AND TG.K_GROUP+0 = 2610 ORDER BY T.V_NAME;
>
> PLAN JOIN (T ORDER NAME INDEX (RDB$53), TG INDEX (RDB$FOREIGN63))
>
> Second test, the same result:
>
> SELECT FIRST 10 SKIP 0 T.K_TARGET, T.K_PHONE, T.V_NAME, T.D_CREATION
> FROM TARGETS T JOIN TARGET_GROUPS TG ON TG.K_TARGET = T.K_TARGET+0 WHERE
> T.K_USER=2262 AND T.C_TYPE=0 AND TG.K_GROUP+0 = 2610 ORDER BY T.V_NAME;
>
> PLAN JOIN (T ORDER NAME INDEX (RDB$53), TG INDEX (RDB$FOREIGN63))
>
> Third test, the plan is quite similar to FB 1.0.3:
>
> SELECT FIRST 10 SKIP 0 T.K_TARGET, T.K_PHONE, T.V_NAME, T.D_CREATION
> FROM TARGETS T JOIN TARGET_GROUPS TG ON TG.K_TARGET = T.K_TARGET+0 WHERE
> T.K_USER=2262 AND T.C_TYPE=0 AND TG.K_GROUP = 2610 ORDER BY T.V_NAME;
>
> PLAN JOIN (T ORDER NAME INDEX (RDB$53), TG INDEX (RDB$PRIMARY22))
>
> I guess there are some logics in this tests but now I can't understand
> them.
>
> Could you please give us a little explanation about what the "+0" means
> inside an SQL query and how to use it to improve performance?
>
> Is it safe to use it thinking in forward and even backward
> compatibility?

[Non-text portions of this message have been removed]