I have a table ORDER(order_id number, Migrate_flag varchar2(1));
I have another table SUB_ORDRDS(sub_order_id number,order_id number);
ie for all there orders sub orders will be stored here.
There requirement is I need to migrate the orders data where the migate flag is 'Y' and all the sub orders under those orders to another two tables(insert or update) .
In the above example if I migrate all the orders first to table1.
after that I am joining the SUB_ORDERS table with the ORDERS table with condition migate_flag=Y and migrating SUB_ORDERS table data to table2.
My question is,
In the above example 1 and 2 orders will get migrate first to table1
Its taking around 15 min. of time in my real scenario due to huge number or records.
But if someone changes migrate_flag to 'N' for those orders which are Y before,
then the sub orders for those orders do not get migrate and data discrepancy may happen like an order might got migrated and sub orders under it do not get migrate.
How to achieve the both transactions as atomic? ie if order migrate then suborders also migrate otherwise both do not migrate.
One way I think is
1) In the order cursor,
I need to run the suborder cursor for every order and migrate suborders also immediately along with that particular order. But this is causing performance issue
I I need to run the suborder query many times ie for every order.
Another way is
I need to lock all order by writing cursor for update and migrate all orders and do not commit and then migrate all the sub orders after that execute commit and release the lock.
But locking those many records for such a long time is causing issues for the end user.
Can you please suggest is there any other way to do ?
like locking batch wise like only 1000 records and migrate orders and sub orders and then lock the next 1000 and so on. So that I could achieve atomic nature and also do not lock huge records for long time.