This content has been marked as final. Show 4 replies
Why not simply introduce a third new table for messages to be kept?
- VEHICLE, with the KEEP_DATA column Y/N.
Vehicles with KEEP_DATA = Y have messages in third table.
Other vehicles have messages in second table.
Optionally build a UNION-ALL view if your UI requires it.
Optionally build Instead-Of triggers if your UI requires it.
Or, better provision transactional API's for your UI...
Thanks for the quick answer.
If possible would like not to change the application-server.
Currently the application-server is accessing the tables directly, not by view.
But I will keep your idea in mind if there are no other satisfying solutions.
What is your DB version?
The problem of this idea is that i have to update billions of rows.If this is your underlying problem then if you are in 11g and above you can use [url http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/d_parallel_ex.htm]DBMS_PARALLEL_EXECUTE and split your update into multiple chunks and execute it parallel.
I mean something like this:As far as i know such a thing is not possible.
alter table MESSAGE
add column (select keep_data from vehicle where VEHICLE.vehicle_id = MESSAGE.vehicle_id as keep_message) ;
Is your requirement that all messages for some vehicles must be kept, or is it some messages for some vehicles?
If it is the former, then keep_data is an attribute of the vehicle, and the column should be added to the vehicle table. If it is the latter, then you need to add it to the messages table. If you have to add it to the messages table, then I would likely only update the messages that require keeping and leave the others null, rather than try to update the whole table with Y or N.