Re: Allowing join removals for more join types

<div class="WordSection1"><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">On</span><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"></span><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">19May 2014 12:15 </span>David Rowley Wrote,<span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"></span><pclass="MsoNormal"> <p
class="MsoNormal">>Ithink you are right here, it would be correct to remove that join, but I also think that the
queryin question could be quite easily be written as:<p class="MsoNormal"> <p class="MsoNormal">>select t1.a from t1
leftjoin t2 on t1.a=t2.b;<p class="MsoNormal"> <p class="MsoNormal">>Where the join WILL be removed. The distinct
clausehere technically is a no-op due to all the columns of a unique index being present in the clause. Can you think
ofa use case for this where the sub query couldn't have been written out as a direct join to the relation?<p
class="MsoNormal"> <pclass="MsoNormal">>What would be the reason to make it a sub query with the distinct? or have I
gottensomething wrong here?<p class="MsoNormal"> <p class="MsoNormal">>I'm also thinking here that if we made the
joinremoval code remove these joins, then the join removal code would end up smarter than the rest of the code as the
currentcode seems not to remove the distinct clause for single table queries where a subset of the columns of a
distinctclause match all the columns of a unique index.<p class="MsoNormal"> <p class="MsoNormal">>Can you think of
asimilar example where the subquery could not have been written as a direct join to the relation?<p
class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><p
class="MsoNormal">Ithink, you are write that above given query and be written in very simple join.<p
class="MsoNormal"> <pclass="MsoNormal">But what my point is, In any case when optimizer cannot pull up the  subquery
(becauseit may have aggregate, group by, order by, limit, distinct etc.. clause),<p class="MsoNormal">That time even,
Itwill check Whether join is removable or not only when distinct or group by clause is there if it has unique index
thenit will not be check, is there no scenario where it will be useful ?<p class="MsoNormal"> <p class="MsoNormal">May
bewe can convert my above example like below  <span style="font-family:Wingdings"> à</span> in this case we have unique
indexon field a and we are limiting it by first 100 tuple (record are already order because of index)<p
class="MsoNormal"> <pclass="MsoNormal">Create table t1 (a int, b int); <p class="MsoNormal">Create table t2 (a int, b
int);<pclass="MsoNormal">Create unique index on t2(a);<p class="MsoNormal"> <p class="MsoNormal">create view v1 as<p
class="MsoNormal">selectx.a, y.b <p class="MsoNormal">from t1 x left join (select t2.a a1, b from t2  limit 100) as y
onx.a=y.a1;<p class="MsoNormal"> <p class="MsoNormal">select a from v1;  <span style="font-family:Wingdings">à</span>
forthis query I think left join can be removed, But in view since non join field(b) is also projected so this cannot be
simplifiedthere.<p class="MsoNormal"> <p class="MsoNormal">In your patch, anyway we are having check for distinct and
groupclause inside subquery, can’t we have check for unique index also ?<p class="MsoNormal"> <p
class="MsoNormal">Regards,<pclass="MsoNormal">Dilip<p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal"> </div>