This forum is currently locked. You can't register or post questions at this time. (read more)

Performance using oracle SET operators

All posts relating to Oracle database administration.

Moderator: Tim...

kaushik.nandy
Member
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

Hi,

(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.

Regards,
Kaushik Nandy

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

Re: Performance using oracle SET operators

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

Hi.

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.

Cheers

Tim...
Tim...
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: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 2 guests

cron