| Subject | Re: Why does the optimizer choose NATURAL for this tiny table? |
|---|---|
| Author | Dmitry Yemanov |
| Post date | 2014-11-14T07:12:57Z |
14.11.2014 00:13, 'Leyne, Sean' wrote:
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.
Dmitry
>It could help if my guess was correct. Although the situation is a bit
> Do you think that a CAST() within T would help?
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.
Dmitry