List,
OLTP
application with 24x7 requirement. 300,000 records per day are inserted
into the transaction table. Environment: Solari 7. Oracle
817.
The
transaction table layout.
Security ID
Account ID
Account Type
Trade
Date
And
other columns in this table.
In the
above table, the primary key is -- Security ID + Account ID + Account Type +
Trade Date
There
are many to one relationships built to other child tables
from Transaction Table
Scenario:
User
inserts a record into transaction table. In the first record, Account ID
value is "HP" and he might insert a record into the child table (Or this
transaction may not insert a record into a child table). After some time, the
user queries the original record with the primary key and then changes the value
in the column - Account ID to "IBM". Now, the original transaction
record is NOT UPDATED. A record IS INSERTED with the new values.
Also, he might or might not insert a record into a child table with this new
values of primary key.
Now
the user would query the transaction table with Account ID = IBM.
But, the user wants to get all the previous records also; in this case, he
want to see the record with Account ID = "HP" also. Also, he want to see the
related records from the child tables.
I
tried with the idea of sequence number generation but it was
failing.
Any
ideas or suggestions are much appreciated.
Thanks,
Rao
Maheswara Rao,
Oracle
DBA
SunGard
Securities
