Solved

Oracle pl/sql

Posted on 2014-11-12
3
200 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
3 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
update set column values in oracle 3 43
UNIX SCP 5 47
JDeveloper 12c for 32 bit 4 34
alter database link to change the password 2 31
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now