Sv: Re: Re: Removing INNER JOINs

På fredag 01. desember 2017 kl. 03:53:29, skrev David Rowley <
david(dot)rowley(at)2ndquadrant(dot)com <mailto:david(dot)rowley(at)2ndquadrant(dot)com>>:
On 1 December 2017 at 15:34, Andreas Joseph Krogh <andreas(at)visena(dot)com
<mailto:andreas(at)visena(dot)com>> wrote:  Can someone please explain, in
layman-terms, what the problems with FKs are related to JOIN-removal?
 
Pretty much what I just wrote after "Unfortunately not," above, although you
asked a few seconds before I sent.
 
We're able to use UNIQUE INDEXes as proofs to remove LEFT JOINs as (with the
exception of deferred unique indexes) these are updated right away, rather than
deferred until the end of the statement as is the case with NOT DEFERRABLE and
not DEFERRED foreign keys. The fact that the foreign keys do not update the
referenced rows right away means that there is a non-zero window of time that
the constraint is violated, therefore, if a query which is run, or is running
during that time, we could return the incorrect results if we were to remove an
INNER JOIN during the planning of that query.

 
Hm...
The fact that Oracle has solved this makes me think you guys can solve this
too if you put enough brain-power to it:-)
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>