Looking for guidlines [message #295171] |
Mon, 21 January 2008 10:43  |
jzhou2005@hotmail.com
Messages: 6 Registered: January 2008
|
Junior Member |
|
|
I am looking for a directions/recommendation for following solution. Any help would be greatly appreciated.
Thank you in advance.
I have three tables:
person_demographics,
Person_eligy_dts,
Person_eligy_key, each with volume of 800 million records each.
Person_key_rule is another table that contains set of rules to determine person uniqueness by supplier. This table is small.
Person_demographics is a parent table of person_eligy_dts and person_eligy_key. person_demographics has a primary key of person_id, which is a foreign key of table person_eligy_key, but is a primary key for Person_eligy_dts.
I have a file which will be read in as an Oracle external table (the size varies from 4Million to 12million). I need to match the external table with the person_eligy_key table via definitions defined in person_key_rule table (basically the where clause is dynamically determined by who suppliers the data). if I matched with person_key_rule table, which means I can find a person_id field, I will compare data in external data with person_demographics and person_eligy_dts data, and then based on a set of business rules to apply updates to person_demographics and person_eligy_dts table. If not matched, I will perform insert to all three tables.
The business requires that this process needs to provide tracking counts (total count, how many inserts, how many updates) and perform all the transaction as efficient as possible. The external table file will be sent to the down stream application with the new person id for those inserts.
Thank you.
|
|
|
|
|
|
|
|
Re: Looking for guidlines [message #295517 is a reply to message #295512] |
Tue, 22 January 2008 08:58   |
jzhou2005@hotmail.com
Messages: 6 Registered: January 2008
|
Junior Member |
|
|
Micheal,
This is business requirements. Plus the external table with the updated person_id will be returned to the application that sent the file. Two applications need to have records reconciliation.
Thanks,
Joyce
|
|
|
|
Re: Looking for guidlines [message #295546 is a reply to message #295529] |
Tue, 22 January 2008 12:07   |
jzhou2005@hotmail.com
Messages: 6 Registered: January 2008
|
Junior Member |
|
|
The process will populdate a table with new inserts, updates so they can monitoring percentage of changes by supplier, if there are too many insert, it might trigger the Analyst to take a look at the data and the data might be bad.
|
|
|
Re: Looking for guidlines [message #299381 is a reply to message #295546] |
Mon, 11 February 2008 15:19   |
coleing
Messages: 213 Registered: February 2008
|
Senior Member |
|
|
Options:-
1. Make sure your records have an UPDATE_TIMESTAMP so you can tell what has been updated.
2. In the UPDATE section of the MERGE, increment a package variable, then interrogate it at the end of the merge to get the UPDATE count.
To get the insert count, its just the full SQL%ROWCOUNT minus the package variable.
|
|
|
Re: Looking for guidlines [message #299439 is a reply to message #299381] |
Tue, 12 February 2008 01:09  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
1. We can imagine many things if model is updatable.
2. Show us how you make that. Above all on 800 million row tables and with 10 million rows updated (see OP's first post).
Regards
Michel
|
|
|