Subject Re: Why does the optimizer choose NATURAL for this tiny table?
Author Dmitry Yemanov
14.11.2014 00:13, 'Leyne, Sean' wrote:
> Do you think that a CAST() within T would help?

It could help if my guess was correct. Although the situation is a bit
weird, as text columns are comparable regardless of their length. Maybe
charset/collation of the expression has been altered/lost after the
IIF(COALESCE, ...) stuff.

I'm just out of better ideas at the moment. If single condition (without
OR) also fails to use an index, then it's not related to the cost
estimation (UK retrieval would be used anyway). MERGE JOIN automatically
casts the join condition to the common datatype and we see it working
when OR is omitted. LOOP JOIN, however, needs directly comparable
datatypes. This is the only remaining possibility, as far as I can see.