Re: How to hint 2 coulms IS NOT DISTINCT FROM each other

On Fri, Nov 4, 2016 at 9:38 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
> On 4 November 2016 at 14:41, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> On Fri, Nov 4, 2016 at 8:08 AM, Kim Rose Carlsen <krc(at)hiper(dot)dk> wrote:
>>> The nulls are generated by something like this
>>> SELECT c.circuit_id,
>>> cc.customer_id
>>> FROM circuit AS c
>>> LEFT JOIN circuit_customer AS cc
>>> ON c.circuit_id = cc.circuit_id
>>>
>>> To make a magic '0' customer we would be required to use
>>> COALESCE(cc.customer_id, '0')
>>> I dont think the optimizer will do anything clever with the '0' we have
>>> computed from null.
>>
>> It would if you explicitly indexed it as such;
>> CREATE INDEX ON circuit_customer((COALESCE(customer_id, '0'));
>
> Merlin, it's a LEFT JOIN. There probably are no NULLs in the
> circuit_customer.customer_id column, so that COALESCE isn't going to
> achieve anything at all.

Hang on -- upthread the context was inner join, and the gripe was join
fast with '=', slow with INDF. When he said the nulls were
'generated', I didn't follow that they were part of the original
query. If the nulls are generated along with the query, sure, an
index won't help.

I maintain my earlier point; with respect to the original query, to
get from performance of INDF to =, you have three options:
a) expr index the nulls (assuming they are physically stored)
b) convert to ((a = b) or a is null and b is null) which can help with
a bitmap or plan
c) covert to union all equivalent of "b"

merlin