Not always. It depends on performance, if it is acceptable then no need. If it
performs poorly and rewriting, would perform better then sure go ahead. If
outer joins always performed poorly, Oracle wouldn’t include it in the product.
Many times poor performance of outer join queries including full outer joins
are not the fault of the outer join but of the complexity of the query. A
simple full outer join of two tables will likely perform quite well, a full
outer join included in a query with 20 tables, 6 views that each have 20 tables
some of which are the same in the other views may not perform so well. The more
complex the query, the more tables involved, the poorer the table/view and
query design the more likely the optimizer will make a mistake and you’ll have
poor performance. Sometimes a complex query needs to be broken down into its
major pieces that performs reasonably well with each piece potentially stored
in a global temporary (or permanent) table prior to doing an outer/full outer
join to get the best performance.
Ken
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Hemant K Chitale
Sent: Friday, March 3, 2017 1:42 AM
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: Outer Joins and IN / NOT IN
Is there truth to the belief that LEFT and FULL Outer Joins should (always) be
rewritten for performance ? I don't have the tables and SQLs so this was just
an exploratory talk. My point is that we should write what seems obvious and is
easy to understand rather than use convoluted subqueries.
Hemant K Chitale
Sent from my smartphone
