Reconcilation between 2 tables based on selected columns.

[Anand]

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

[Tim...]

Hi.

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

Cheers

Tim...

[Anand]

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

[Tim...]

Hi.

Isn't this just a join?

Cheers

Tim...

[Anand]

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

[Tim...]

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