Re: outer join between parent-child, not null and FK constraints
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Fri, 25 Jan 2013 14:17:23 -0800 (PST)
Message-ID: <1359152243.98367.YahooMailNeo_at_web121601.mail.ne1.yahoo.com>
An outer join CAN return unmatched rows, but that doesn't mean it will.� Simply because Oracle doesn't return unmatched rows using an outer join is no indication that the outer join should not be executed.� On 11.2.0.3 here is what the optimizer did with your original query as far as join elimination is concerned (taken from a 10053 trace): �
Join Elimination (JE)
SQL:******* UNPARSED QUERY IS *******
SELECT "EMP"."ENAME" "ENAME","DEPT"."DNAME" "DNAME" FROM "BING"."EMP" "EMP","BING"."DEPT" "DEPT" WHERE "EMP"."DEPTNO"(+)="DEPT"."DEPTNO" SQL:******* UNPARSED QUERY IS *******
SELECT "EMP"."ENAME" "ENAME","DEPT"."DNAME" "DNAME" FROM "BING"."EMP" "EMP","BING"."DEPT" "DEPT" WHERE "EMP"."DEPTNO"(+)="DEPT"."DEPTNO" Query block SEL$1 (#0) unchanged
OJE:�� outer-join not eliminated
OJE: End: finding best directive for query block SEL$1 (#0) JE:�� Considering Join Elimination on query block SEL$1 (#0)
Join Elimination (JE)
�
There is your answer, like it or not.� The optimizer CAN eliminate an outer join if it finds sufficient cause to do so.� It didn't in this case, maybe because DEPT has a record unmatched in EMP, maybe for some other reason. �
According to natural logic, yes, your argument holds water.� The optimizer, however, doesn't always behave using 'natural logic' and has other considerations involving statistics, etc.�before it makes its final decision.� This time, for this query, it decided to keep the outer join.
From: amonte <ax.mount_at_gmail.com>
To: Stephane Faroult <sfaroult_at_roughsea.com> Cc: David Fitzjarrell <oratune_at_yahoo.com>; Oracle-L Group <oracle-l_at_freelists.org> Sent: Friday, January 25, 2013 12:59 PM
Subject: Re: outer join between parent-child, not null and FK constraints
Date: Fri, 25 Jan 2013 14:17:23 -0800 (PST)
Message-ID: <1359152243.98367.YahooMailNeo_at_web121601.mail.ne1.yahoo.com>
An outer join CAN return unmatched rows, but that doesn't mean it will.� Simply because Oracle doesn't return unmatched rows using an outer join is no indication that the outer join should not be executed.� On 11.2.0.3 here is what the optimizer did with your original query as far as join elimination is concerned (taken from a 10053 trace): �
Join Elimination (JE)
SQL:******* UNPARSED QUERY IS *******
SELECT "EMP"."ENAME" "ENAME","DEPT"."DNAME" "DNAME" FROM "BING"."EMP" "EMP","BING"."DEPT" "DEPT" WHERE "EMP"."DEPTNO"(+)="DEPT"."DEPTNO" SQL:******* UNPARSED QUERY IS *******
SELECT "EMP"."ENAME" "ENAME","DEPT"."DNAME" "DNAME" FROM "BING"."EMP" "EMP","BING"."DEPT" "DEPT" WHERE "EMP"."DEPTNO"(+)="DEPT"."DEPTNO" Query block SEL$1 (#0) unchanged
CVM: Considering view merge in query block SEL$1 (#0) OJE: Begin: find best directive for query block SEL$1 (#0) OJE: Considering outer-join elimination on query block SEL$1 (#0) OJE: considering predicate"EMP"."DEPTNO"(+)="DEPT"."DEPTNO"rejected
OJE:�� outer-join not eliminated
OJE: End: finding best directive for query block SEL$1 (#0) JE:�� Considering Join Elimination on query block SEL$1 (#0)
Join Elimination (JE)
�
There is your answer, like it or not.� The optimizer CAN eliminate an outer join if it finds sufficient cause to do so.� It didn't in this case, maybe because DEPT has a record unmatched in EMP, maybe for some other reason. �
According to natural logic, yes, your argument holds water.� The optimizer, however, doesn't always behave using 'natural logic' and has other considerations involving statistics, etc.�before it makes its final decision.� This time, for this query, it decided to keep the outer join.
David Fitzjarrell
From: amonte <ax.mount_at_gmail.com>
To: Stephane Faroult <sfaroult_at_roughsea.com> Cc: David Fitzjarrell <oratune_at_yahoo.com>; Oracle-L Group <oracle-l_at_freelists.org> Sent: Friday, January 25, 2013 12:59 PM
Subject: Re: outer join between parent-child, not null and FK constraints
Hi Stephane
I am asking this simply because I was thinking if Oracle is capable to
eliminate join from 11gR1 why it cannot eliminate redundant outer joins.
That's all. I am not meaning we should not use outer join nor write proper
queries. I simply wanted to prove that my thinking about outerjoin+fk+not
null, if all 3 conditions are met outer join is not needed
Thanks
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 25 2013 - 23:17:23 CET