Home » SQL & PL/SQL » SQL & PL/SQL » Looking for guidlines (PL/SQL)
Looking for guidlines [message #295171] Mon, 21 January 2008 10:43 Go to next message
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 #295178 is a reply to message #295171] Mon, 21 January 2008 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what is the question?

Regards
Michel
Re: Looking for guidlines [message #295179 is a reply to message #295178] Mon, 21 January 2008 11:13 Go to previous messageGo to next message
jzhou2005@hotmail.com
Messages: 6
Registered: January 2008
Junior Member
Micheal,

Thank you for replying. I was wondering for the process I descripted in prior email, what would be the most efficient approach.

Joyce
Re: Looking for guidlines [message #295273 is a reply to message #295171] Mon, 21 January 2008 23:36 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Plus You may get advantage on using MERGE statement instead of seperate Insert and update

Thumbs Up
Rajuvan
Re: Looking for guidlines [message #295507 is a reply to message #295273] Tue, 22 January 2008 08:42 Go to previous messageGo to next message
jzhou2005@hotmail.com
Messages: 6
Registered: January 2008
Junior Member
Rajuvan,

Thank you for your reply. Can I get records count for insert/update for merge statement or I have to create my own tracking count process?

Thanks,
Joy
Re: Looking for guidlines [message #295512 is a reply to message #295507] Tue, 22 January 2008 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you need this.
You just have to know the number of merged rows, why it is so important to know how are inserted and how many are updated?

Regards
Michel
Re: Looking for guidlines [message #295517 is a reply to message #295512] Tue, 22 January 2008 08:58 Go to previous messageGo to next message
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 #295529 is a reply to message #295517] Tue, 22 January 2008 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is business requirements.

Ask the reason and to modify this.

Quote:
Two applications need to have records reconciliation.

What is that?

Regards
Michel
Re: Looking for guidlines [message #295546 is a reply to message #295529] Tue, 22 January 2008 12:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: ROW COUNTER for update, insert and reject... :(
Next Topic: Subscript beyond count
Goto Forum:
  


Current Time: Tue Dec 06 15:57:57 CST 2016

Total time taken to generate the page: 0.16881 seconds