|
>> It might raise another problem, that the nulls are generated through LEFT >> JOINS where no rows are defined. Then the 0 or -1 value need to be
>> a computed value. Won't this throw off index lookups? (I might be >> more confused in this area). > >Not following this. 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.
I could ofc. by default assign all unassigned circuits to '0' in
circuit_customer. I'm not a fan though.
>BTW, if you want a fast plan over the current
>data without consideration of aesthetics, try this: > >CREATE VIEW view_circuit_with_status AS ( > SELECT r.*, > s.circuit_status, > s.customer_id AS s_customer_id, > p.line_speed, > p.customer_id AS p_customer_id > FROM view_circuit r > JOIN view_circuit_product_main s > ON r.circuit_id = s.circuit_id > AND r.customer_id, s.customer_id > JOIN view_circuit_product p > ON r.circuit_id = p.circuit_id > AND r.customer_id, s.customer_id > UNION ALL SELECT r.*, > s.circuit_status, > s.customer_id AS s_customer_id, > p.line_speed, > p.customer_id AS p_customer_id > FROM view_circuit r > JOIN view_circuit_product_main s > ON r.circuit_id = s.circuit_id > AND r.customer_id IS NULL > AND s.customer_id IS NULL > JOIN view_circuit_product p > ON r.circuit_id = p.circuit_id> I will have to figure something out, but this specific case is still problematic
since we would like to filter this view using different criteria's, like circuit_no,
products or customers.
But with all these detours, I assume that a change to IS NOT DISTINCT FROM,
is difficult or not wanted?
|
