Reconcilation between 2 tables based on selected columns.

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Reconcilation between 2 tables based on selected columns.

Postby Anand » Fri Jun 08, 2012 4:29 am

Hi,

I need your expert advice to the following requirement.

My requirement is as follows:-
------------------------------------------
I have 2 tables say "table1" and "table2", each contains more than 10 millions of data in it.
I have to perform matching (i.e. reconcilation) of records based on the few columns selected from each of the tables.

Please suggest from technical perspective(I mean what Oracle function or Procedure) ,
what approach I should follow in order to match the records of these two tables based on selected column of these 2 tables?

Please keep a note that the records of tables can grow in size even more than 10 millions also,so performance is the
major criteria.

Thanks in advance.

Regards,
Anand Kumar Ojha
Anand
Member
 
Posts: 22
Joined: Thu Jun 07, 2012 2:59 pm

Re: Reconcilation between 2 tables based on selected columns

Postby Tim... » Fri Jun 08, 2012 6:37 am

Hi.

I don't understand what you mean by matching/reconciliation. You need to explain what you want to achieve properly.

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

Re: Reconcilation between 2 tables based on selected columns

Postby Anand » Fri Jun 08, 2012 9:18 am

Hi,

OK.

Let me explain you with an example:-
We have 2 different sources(or simply tables) for reconcilation or matching.
These 2 different sources i.e "table1" and "table2" contains say the "call detailed record" of a particular subscriber whose mobile no is 9015582852.

Something like this:-

"Table1"[Source 1]

Mobile NO Call_DT Call_Duration(in min) Toll_Value(Call_Duration * Call Rate)
----------------- ---------------- ----------------- -----------
9015582852 09-mar-2012 50.00 100$

"Table2" [Source 2]

Mobile NO Call_DT Call_Duration(in min) Toll_Value(Call_Duration * Call Rate)
----------------- ---------------- ----------------- -----------
9015582852 09-mar-2012 50.00 110$

My requirement is to match the Source 1(i.e. table1) with Source 2(i..e table2) based on some column say "Toll_Value"
and then to store the variance (or delta) in a temporary table for further reference.

So,If I match(or reconcile) "table 1" with "table 2" based on matching column "Toll_Value",the variance would be 10$ (ie.110$ - 100$).

P.S: Reconciliation will have the following consideration also.

1. The columns on basis of which I need to reconcile or match will be more than one.
2. More than 10 millions records will be there in the above tables.

Thanks,
Anand Kumar Ojha
Anand
Member
 
Posts: 22
Joined: Thu Jun 07, 2012 2:59 pm

Re: Reconcilation between 2 tables based on selected columns

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

Hi.

Isn't this just a join?

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

Re: Reconcilation between 2 tables based on selected columns

Postby Anand » Fri Jun 08, 2012 9:55 am

Hi,

No,its something like this:-

(Table1 - Table2) U (Table2 - Table 1)
or
simply
(Table1 Minus Table2) U (Table2 Minus Table1)

i..e How many rows are in "Table1" and not in "Table 2" And How many rows are in "Table2" not in "Table 1".

Regards,
Anand Kumar Ojha
Anand
Member
 
Posts: 22
Joined: Thu Jun 07, 2012 2:59 pm

Re: Reconcilation between 2 tables based on selected columns

Postby Tim... » Fri Jun 08, 2012 10:39 am

Hi.

You said you were looking for matches. Now you are saying you are looking not for the matches, but for the things that don't match. That is very different! :)

Funny you should ask this at the same time as someone else asked the question on this thread: viewtopic.php?f=1&t=13565

An alternative it to perform a full outer join between the two tables. Whichever columns contain NULL is an indication of the record being missing from that table.

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


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 4 guests