Oracle SQL 全外连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4147702/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL full outer join
提问by mahen
I was joining 2 tables using FULL OUTER JOIN which took 6 minutes to run and give the output.
我正在使用 FULL OUTER JOIN 加入 2 个表，这需要 6 分钟才能运行并给出输出。
SELECT *
FROM tab1 FULL OUTER JOIN tab2
ON tab1.id = tab2.id
;
I did the same thing using UNION of LEFT OUTER JOIN and RIGHT OUTER JOIN. This took only 15 seconds
我使用 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 的 UNION 做了同样的事情。这仅用了 15 秒
SELECT *
FROM tab1, tab2
WHERE tab1.id (+) = tab2.id
UNION
SELECT *
FROM tab1, tab2
WHERE tab1.id = tab2.id (+)
;
Does anyone know why this is happening?
有谁知道为什么会这样？
采纳答案by McKay
You probably have a lot of rows in both tables, few rows in common, and no index on the columns in question.
您可能在两个表中都有很多行，很少有行是共同的，并且相关列上没有索引。
回答by nvogel
Check the explain plans. Bear in mind that the two queries you specified are NOT logically equivalent. The second query eliminates duplicate rows (UNION) but the first one does not. This may be part of the explanation for the difference in performance.
检查解释计划。请记住，您指定的两个查询在逻辑上并不等效。第二个查询消除了重复行 (UNION)，但第一个查询没有。这可能是对性能差异的部分解释。
回答by Quassnoi
FULL OUTER JOINis somewhat different from the other join types since no single leading table can be selected.
FULL OUTER JOIN与其他连接类型有些不同，因为无法选择单个前导表。
The only method that would handle it efficiently would be a MERGE JOIN, however, Oraclewon't select it.
唯一可以有效处理它的方法是 a MERGE JOIN，但是Oracle不会选择它。
Which plan does Oracleyield for your queries?
哪个计划Oracle对您的查询产生影响？
回答by Mike Meyers
I experienced this with Oracle 9.2. Could work around it by breaking the FULL OUTER JOINdown into a left outer join and a right anti-join (or something?).
我在 Oracle 9.2 上遇到过这种情况。可以通过将FULL OUTER JOIN分解分解为左外连接和右反连接（或其他什么？）来解决它。
SELECT a.*, b.*
from tableA a
left outer join tableB b on (a.a = b.a)
union all
SELECT a.*, b.*
from tableA a
right outer join tableB b on (a.a = b.a)
where a.a is null
This is pretty much the query you provided but using a UNION ALLand removing the duplicates from the second part of the query rather than using a UNION. Sometimes I couldn't even get performance out of that and needed to break the query down even further but can't remember what that involved.
这几乎是您提供的查询，但使用 aUNION ALL并从查询的第二部分中删除重复项，而不是使用UNION. 有时我什至无法从中获得性能，需要进一步分解查询，但不记得涉及到什么。
For me the situation seemed to be improved with Oracle 10 but a FULL OUTER JOINisn't something I need very often so haven't gone back to benchmark it recently.
对我来说，Oracle 10 的情况似乎有所改善，但FULL OUTER JOIN它不是我经常需要的东西，所以最近没有回去对它进行基准测试。
