[odb-users] How to change the join type of relations in a view
adanesh at noornet.net
Tue Jul 5 08:02:27 EDT 2016
The join type has effects on the opimization of queries.
Section "6.1 Order of tables in a join" says:
Inner joins can be freely reordered. However a left outer join is neither commutative nor associative and hence will not be reordered. Inner joins to the left and right of the outer join might be reordered if the optimizer thinks that is advantageous but the outer joins are always evaluated in the order in which they occur.
In my case, there is a big difference in perforamnce when I use inner join instead of left join. Using inner join has one limitation, because we can't have null pointers in the list of related objects; but in my case, this feature isn't needed.
From: Boris Kolpackov <boris at codesynthesis.com>
Sent: Tuesday, July 5, 2016 3:19 PM
To: علی دانش
Cc: odb-users at codesynthesis.com
Subject: Re: [odb-users] How to change the join type of relations in a view
adanesh at noornet.net writes:
> r += "FROM \"TagIndex\"";
> r += " INNER JOIN \"SubjectIndex_tags\" ON";
> r += "\"SubjectIndex_tags\".\"value\"=\"TagIndex\".\"id\"";
> r += " LEFT JOIN \"SubjectIndex\" ON";
> r += "\"SubjectIndex_tags\".\"object_id\"=\"SubjectIndex\".\"id\"";
Object relationships are always translated to LEFT JOIN, there is no
way to change that and I believe there should be no need either. Can
you show a situation where changing it to INNER JOIN will make a
More information about the odb-users