tag:blogger.com,1999:blog-3086558868352622884.post5971407638895371783..comments2023-06-05T07:33:53.363-07:00Comments on Inside the Oracle Optimizer - Removing the black magic: Outerjoins in OracleOptimizer Development Grouphttp://www.blogger.com/profile/17974370052224384106noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-3086558868352622884.post-71032692350268931432008-02-15T11:01:00.000-08:002008-02-15T11:01:00.000-08:00Thanks Gints! I applied the same adjustments as J...Thanks Gints! I applied the same adjustments as Jonathan to my mental model and now all is well again. :-)<BR/><BR/>Cheersroberthttps://www.blogger.com/profile/01724179181550310220noreply@blogger.comtag:blogger.com,1999:blog-3086558868352622884.post-33827495565745112682008-02-01T04:44:00.000-08:002008-02-01T04:44:00.000-08:00Speaking about "Q1: I do not understand the querie...Speaking about "Q1: I do not understand the queries N and O. What is the difference between a filter appearing in the ON Clause or a filter appearing in the WHERE clause?"<BR/>it is also nicely explained In Jonathan Gennick's 5 years old article <A HREF="http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html" REL="nofollow">What's in a Condition?</A>Gints Plivnahttps://www.blogger.com/profile/17716037729002528529noreply@blogger.comtag:blogger.com,1999:blog-3086558868352622884.post-4960034078546466842008-01-04T13:02:00.000-08:002008-01-04T13:02:00.000-08:00This is awesome! The Q&A is priceless. Seriously, ...This is awesome! The Q&A is priceless. Seriously, I hope you guys keep this up. It is a great learning experience for those of wishing to understand "how things work"; not only the original post, but the interactions you have with the visitors as well. Your commitment to "setting the record straight" is ... stellar.Charles Schultzhttps://www.blogger.com/profile/07973399674184183130noreply@blogger.comtag:blogger.com,1999:blog-3086558868352622884.post-49610945918359330962007-12-18T05:22:00.000-08:002007-12-18T05:22:00.000-08:00can you clarify the general conditions where the n...can you clarify the general conditions where the new native FOJ cannot be applied.The6Campbellshttps://www.blogger.com/profile/13547723526098327673noreply@blogger.comtag:blogger.com,1999:blog-3086558868352622884.post-26566013968966445242007-12-14T06:06:00.000-08:002007-12-14T06:06:00.000-08:00I do not understand your comment about join N. Sin...I do not understand your comment about join N. Since all rows from T1 are removed from the result where T1.h is not 11 this should be equivalent:<BR/><BR/>SELECT T1.m, T2.n<BR/>FROM T1, T2<BR/>WHERE T1.h = 11 <BR/> and T1.y = T2.y(+)<BR/> and T1.q > 3;<BR/><BR/>What am I missing?roberthttps://www.blogger.com/profile/01724179181550310220noreply@blogger.comtag:blogger.com,1999:blog-3086558868352622884.post-25429827762396464792007-12-13T00:58:00.000-08:002007-12-13T00:58:00.000-08:00Thanks a lot for this superb analysis, it brings d...Thanks a lot for this superb analysis, it brings down my doubts about the ANSI syntax at much more accettable levels ;-)Unknownhttps://www.blogger.com/profile/08695289755900054684noreply@blogger.comtag:blogger.com,1999:blog-3086558868352622884.post-87649027340313183142007-12-12T08:26:00.000-08:002007-12-12T08:26:00.000-08:00Regarding queries N and O:Why is it that the predi...Regarding queries N and O:<BR/>Why is it that the predicate <I>T1.h = 11</I> cannot be moved out of the lateral view? I understand that by definition, the ON clause predicates must be applied before the WHERE clause, but in this case (predicates on the LEFT table only) doing so wouldn't change the result set, would it?<BR/>I must be missing something...<BR/><BR/>Thanks for the blog!<BR/><BR/>Keep these posts coming :-)<BR/><BR/>FladoFladohttps://www.blogger.com/profile/04901763101139511192noreply@blogger.comtag:blogger.com,1999:blog-3086558868352622884.post-74477912930058392722007-12-12T05:50:00.000-08:002007-12-12T05:50:00.000-08:00HiAs you pointed out "Before Oracle 11gR1 all ANSI...Hi<BR/><BR/>As you pointed out "Before Oracle 11gR1 all ANSI full outerjoins were converted into a UNION ALL query with two branches". Now, according to the following test it seams that it is already available as of 10.2.0.3, but not activated per default.<BR/><BR/>SQL> SELECT * FROM v$version WHERE rownum = 1;<BR/><BR/>BANNER<BR/>------------------------------------------------------------------<BR/>Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi<BR/><BR/>SQL> EXPLAIN PLAN FOR<BR/> 2 SELECT /*+ NATIVE_FULL_OUTER_JOIN */ *<BR/> 3 FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;<BR/><BR/>Explained.<BR/><BR/>SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'basic'));<BR/><BR/>PLAN_TABLE_OUTPUT<BR/>------------------------------------------------------------------<BR/><BR/>Plan hash value: 53297166<BR/><BR/>------------------------------------------<BR/>| Id | Operation | Name |<BR/>------------------------------------------<BR/>| 0 | SELECT STATEMENT | |<BR/>| 1 | VIEW | VW_FOJ_0 |<BR/>| 2 | HASH JOIN FULL OUTER| |<BR/>| 3 | TABLE ACCESS FULL | T1 |<BR/>| 4 | TABLE ACCESS FULL | T2 |<BR/>------------------------------------------<BR/><BR/>SQL> ALTER SESSION SET "_optimizer_native_full_outer_join" = force;<BR/><BR/>Session altered.<BR/><BR/>SQL> EXPLAIN PLAN FOR<BR/> 2 SELECT *<BR/> 3 FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;<BR/><BR/>Explained.<BR/><BR/>SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'basic'));<BR/><BR/>PLAN_TABLE_OUTPUT<BR/>-------------------------------------------------------------------<BR/><BR/>Plan hash value: 53297166<BR/><BR/>------------------------------------------<BR/>| Id | Operation | Name |<BR/>------------------------------------------<BR/>| 0 | SELECT STATEMENT | |<BR/>| 1 | VIEW | VW_FOJ_0 |<BR/>| 2 | HASH JOIN FULL OUTER| |<BR/>| 3 | TABLE ACCESS FULL | T1 |<BR/>| 4 | TABLE ACCESS FULL | T2 |<BR/>------------------------------------------<BR/><BR/>So, my hope is that you are planning to activate it in 10.2.0.4 as well :-) <BR/><BR/>Cheers,<BR/>ChrisAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3086558868352622884.post-53834284145720624652007-12-12T03:38:00.000-08:002007-12-12T03:38:00.000-08:00Nice explanation.However, is there any behavior ch...Nice explanation.<BR/>However, is there any behavior change with the Optimizer when used ANSI standard syntax over the other one?<BR/><BR/>Regards<BR/><BR/>JaffarThe Human Flyhttps://www.blogger.com/profile/03489518270084955004noreply@blogger.comtag:blogger.com,1999:blog-3086558868352622884.post-59434112572028655552007-12-11T22:57:00.000-08:002007-12-11T22:57:00.000-08:00Thanks so much for this blog! Like many others, I...Thanks so much for this blog! Like many others, I will be reading and re-reading every word.<BR/>One question: your last section shows the optimizer "silently" converting an outer join to an inner join when a filter predicate would eliminate the null appended rows. Is this new 11G behavior, or do previous versions do this? My experience with 9i leads me to believe that the needless outer joins stay and may degrade performance.<BR/>You could call this optimization the "Hibernate corrector", since Hibernate defaults to left outer joins everywhere :(Stew Ashtonhttps://www.blogger.com/profile/10004507258457241890noreply@blogger.com