Home » SQL & PL/SQL » SQL & PL/SQL » Fetching the unmatched records from two queries. (Oracle 10.2.0.4)
icon4.gif  Fetching the unmatched records from two queries. [message #400845] Wed, 29 April 2009 23:19 Go to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member


I have Query A and Query B,

Query A fetches some million records and Query B fetches some million records.

I want to compare these two set of records and find out the unmatched records using below formula.

Quote:
Result of Query A - Result of Query B
UNION ALL
Result of Query B - Result of Query A


To achive this what is the bestway for designing a pl/sql?
If I use cursors for both the queries and 2 cursor-for loops, this will degrades the perfomance.

Pls give me a solution for this.

regards,
Nataraj.

Re: Fetching the unmatched records from two queries. [message #400849 is a reply to message #400845] Wed, 29 April 2009 23:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tom Kyte: Comparing the Contents of Two Tables

Regards
Michel
Re: Fetching the unmatched records from two queries. [message #400850 is a reply to message #400845] Wed, 29 April 2009 23:29 Go to previous messageGo to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
you have given the answer in you are question itself


select a,b from 
(select * from  A minus select * from  B)
union
select a,b from (
select * from  B minus select * from  A)




this will work
Re: Fetching the unmatched records from two queries. [message #400851 is a reply to message #400850] Wed, 29 April 2009 23:38 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Yes Rangan, you are right,
But I have some 186 set of queries to be executed like this one.

I need to write a PL/SQL for all of those.

Also I need to perform one more operation along with above said.

Quote:
Count(Query A) = Count(Query B)
THEN
Result of Query A - Result of Query B
UNION
Result of Query B - Result of Query A


But here I need to execute both the queries only once. So to achieve this we need to store the query results in the buffer or cursor or collection or temp table .. etc,

Pls suggest me the best strategy which suits my requirement.

regards,
Nataraj.

[Updated on: Wed, 29 April 2009 23:54]

Report message to a moderator

Re: Fetching the unmatched records from two queries. [message #400856 is a reply to message #400851] Thu, 30 April 2009 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the link I posted, this is the best way.

Regards
Michel
Re: Fetching the unmatched records from two queries. [message #401318 is a reply to message #400856] Mon, 04 May 2009 01:17 Go to previous message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Thanks Miche,

that was very useful.


regards,
Nataraj.
Previous Topic: Help needed
Next Topic: Convert Relational table into Hierarchical table (split from hijacked thread)
Goto Forum:
  


Current Time: Sun Dec 04 02:35:04 CST 2016

Total time taken to generate the page: 0.22938 seconds