Performance using oracle SET operators

All posts relating to Oracle database administration.

Moderator: Tim...

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
Posts: 43
Joined: Sun Oct 31, 2010 8:12 am

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:
Site Admin
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 10 guests