Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle pl/sql

Posted on 2014-11-12
3
Medium Priority
?
208 Views
Last Modified: 2014-11-13
Hi,

I have a table ORDER(order_id number, Migrate_flag varchar2(1));

data
1,Y
2,Y
3,N
 
I have another table SUB_ORDRDS(sub_order_id number,order_id number);

ie for all there orders sub orders will be stored here.

data
100,1
101,1
102,1
104,2
105,2
106,2

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.


Thank you.
0
Comment
Question by:GouthamAnand
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40439179
One of the way is that when inserting the orders into the new_orders table you also need to set a flag on a column in this table. Call this column say 'MIGRATE_STATUS' and make it a CHAR(1).
When you inserted the data from your ORDERS table (for the migrate_flag of 'Y') make the MIGRATE_STATUS = 'I' (stands for incomplete data)
Then, when you are populating the new_sub_orders table, do not use the migrate_flag of the orders table anymore. Instead join the old sub_orders table with the new_orders table for the records with the MIGRATE_STATUS = 'I'. When you insert the new_sub_orders table run the UPDATE for the new_orders and set the MIGRATE_STATUS = 'C' (complete).
Do everything in a single transaction
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40439247
This is a generic situation while using oracle database to do batch processing, I have had faced this issue many times especially during the migrations from legacy to some product database.

The approach which is to split and divide the records to harness the power of parallel processing.

Add a column to your original table

ORDER(order_id number, Migrate_flag varchar2(1),run_number number);

order_id  migrate_flag    run_number
1,                Y                        0
2,                Y                        0
3,                N                        1
4                 N                        1
5                 Y                         2


So by using an extra column you can always pick the run number and can run it many times

so the cursor would become something like this

procedure first_process(p_run_number in number) as
cursor do_process
select * from orders where run_number=p_run_number
and migrate_flag='N';
begin
for l1 in do_process
loop
do_something;
update orders set run_number=p_run_number,migrate_flag='Y';
commit;
end loop;
end;


You just have to do a task before you could do this, you have to assign run_numbers to your records, this too can be automated by using a sequence and storing the start order_number and end_order_number..

By this way you can do this task faster by performing single row commits and can run the process n number of times without the problem of duplication.
0
 

Author Closing Comment

by:GouthamAnand
ID: 40439418
Thank  you very much.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question