Performance using oracle SET operators

All posts relating to Oracle database administration.

Moderator: Tim...

Posts: 43
Joined: Sun Oct 31, 2010 8:12 am

Performance using oracle SET operators

Postby kaushik.nandy » Fri Jun 08, 2012 9:26 am


(A-B) UNION (B- A)

Here A and B are tables with 50 millions records each.

If this query operation has the worst performance , then is there any better way to get the same results ?
Assume all the table are indexed and partioned on key value.

Kaushik Nandy

Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Performance using oracle SET operators

Postby Tim... » Fri Jun 08, 2012 9:40 am


The question is too vague. Here are some thoughts.

1) If the point of this is to make sure all records are returned because you are using OUTER JOINS, then maybe you need a FULL OUTER JOIN, rather than a UNION of two LEFT OUTER JOINs.

2) If you don't care about duplicates, the UNION ALL will be quicker than the UNION because the removal of duplicates requires extra work and sorts.


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website:
My blog:

Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 2 guests