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