topic Re: Composite primary keys in Operating System - HP-UX

Composite primary keys

jilpangs — Fri, 11 Feb 2005 12:28:46 GMT

Company
BusGroup
Pdate
BusType
Pctr_num
Dealer_num
Fund_num
Segment

These are the composite primary keys in my table.

I also have following columns in same table.
Daily_balance
Daily_accts
Avg Bal

Now,

We are going to update fund_num in this table.
For eg (Attached Excel for better picture)
Fund numbers 5 and 8 are going to be changed to 10(a new fund num, hence wont be in my table)

When i update 5 to 10, i wont get any contraint issues, cos 10 is a new fund num.... when i again update
8 to 10, there could be possibility for constraint violation. In that cases i need to add up daily balance, daily account and also calculate avg balance.
unlike daily balance and daily account , avg balance
cannot be added up directly...its needs recalculation.

I have given sample data and required output in attached excel...

Can anyone help me how to acheive them thru a procedure or by any means. I have 25 millions of records in this table.

Thanks
Raj

Re: Composite primary keys

Brian Crabtree — Fri, 11 Feb 2005 14:14:37 GMT

Oi. Well, if pdate is a date field, then you shouldn't have a problem of it conflicting.

However, the only way to do what you want is to build a PL/SQL block to make these changes for you.

Exactly how many records do you need to combine in this way?

Brian

Re: Composite primary keys

jilpangs — Fri, 11 Feb 2005 14:27:21 GMT

Yes Pdate is a date field but both 5 and 8 fund numbers will have same date when they are
merged as 10...so i'll be getting a conflict.

PL/SQL is the way to go. Is that possible to give a sample code , how to calculate avg balance if there is a conflict ..also i need to remove the record if its already present..
i'm not a expert in pl/sql. please help.

4714368 records to be updated.

Thanks
Raj

Re: Composite primary keys

Yogeeraj_1 — Sat, 12 Feb 2005 01:13:13 GMT

hi,

for PLSQL, have a look at the following URL:
http://oraclesvca2.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/toc.htm

it is a must read!

if you stil have any trouble with your PLSQL procedure, do let us know.

regards
Yogeeraj

Re: Composite primary keys

Yogeeraj_1 — Sat, 12 Feb 2005 01:14:52 GMT

hi again,

you also have examples of PLSQL procedures at this same site:
http://oraclesvca2.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/a_samps.htm#4582

regards
yogeeraj