Re: Help needed with slow union join

Paul (paul_at_see.my.sig.com) wrote:

: "Vince" <vinnyop_at_yahoo.com> wrote:

: > If there is no overlap in the data between the 2 selects, try using
: > UNION ALL instead of UNION.

: What is the technical rationale for doing such a thing?

UNION removes duplicates whereras UNION ALL does not remove duplicates.

Oracle cannot know whether there are any duplicates except by removing them, so it always goes through the process of removing duplicates even if the tables can never contain duplicates.

There are various ways to remove duplicates, but generally speaking Oracle either has to sort the two tables or it has to do an indexed lookup of every value from one table in the other table.

If you know the two tables do not have duplicates, or if you do not care that the result might have duplicates, then you can avoid all the above work by using UNION ALL.

Notice that (some what ironically) the UNION forces the removal of duplicates that come from the _same_ table even though the two tables may have nothing in common. It's all entirely logical, but may be unexpected if you haven't had to think about it before. Received on Wed May 31 2006 - 17:53:20 CDT