Solved

MERGE and BULK Update Using Cursor

Posted on 2010-09-13
10
1,491 Views
Last Modified: 2013-11-11
Hi Experts,

              For a huge set of data merging should i be using MERGE or Bulk update using Cursor in oracle?.How do we  decide which one is appropriate based on the situation?.

Thanks
prashu
0
Comment
Question by:prashubk
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 13

Expert Comment

by:riazpk
ID: 33669353
Since MERGE will process the sets of records, i would prefer it over bulk update. Don't go for PL/SQL if you can do it in straight SQL (think in sets).
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 33669369
If using of PL/SQL is necessary then use collections,fetch bulk collect and forall.
0
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 33669394
update of an inline view would be the most effective in this case.

update (select ...,...,... from tableA,tableB) set ........
0
 

Author Comment

by:prashubk
ID: 33671251
Thanks for the input..i have a procedure that is where i would be  taking the values from two globle temp table.Each table consists of about 300,000 records.
I have used MERGE for populating the data. I was just thinking since i'm dealing with huge data and commit will happen only at the end will it pose any issues.
If i use cursor then can fetch 10000 records at a time from the cursor  then commit after updating .Again fetch another 10000 and commit after updating as shown in the piece of code.Please suggest which is better for this kind of situation
nArraySize NUMBER := 10000;

OPEN list;

LOOP        

 FETCH list BULK COLLECT 

            INTO    xId, tCode,pCode,colOne,colTwo,colThree LIMIT nArraySize;

          BEGIN

            FORALL i IN 1 .. xId.COUNT

              UPDATE TRANS_TABLE

                SET col1 = colOne(i),

                    col2 = colTwo(i),

                    col3 = colThree(i)                    

               WHERE 

	          x_code = xId(i)

                 AND t_Id = tCode(i)

                 AND p_code = pCode(i);

            COMMIT;

	    EXCEPTION

            WHEN OTHERS THEN

              pRetCode := SUBSTR(SQLERRM, 1, 150);

          END;

          EXIT WHEN list%NOTFOUND;

        END LOOP;

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 33671355
if you are only updating 300,000 rows, then no need to split into pieces.    That's a small transaction.  Do it in SQL, no pl/sql needed or desired.
0
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.

 

Author Comment

by:prashubk
ID: 33671436
Hi Sdstuber

              Thanks, Then in when i should consider using splitting of data into small pieces. I mean transactions in numbers?.Please suggest
0
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 33671768
that depends on the size of your database + size of UNDO + size of your server
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 125 total points
ID: 33671859
Do it in one transaction if you can.

If you run out of undo/rollback and you can't extend your undo/rollback  then go with split transactions.

0
 
LVL 4

Assisted Solution

by:boriskalavsky
boriskalavsky earned 125 total points
ID: 33671863
update of an inline view would be the most effective in this case (if you have PK constraints):
http://www.adp-gmbh.ch/ora/sql/inline_view_update.html

or you can use merge statement with if you need update/insert/delete functionality:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#i2081218
http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php
0
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 33671868
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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

747 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

12 Experts available now in Live!

Get 1:1 Help Now