prashubk
asked on
MERGE and BULK Update Using Cursor
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
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
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).
If using of PL/SQL is necessary then use collections,fetch bulk collect and forall.
update of an inline view would be the most effective in this case.
update (select ...,...,... from tableA,tableB) set ........
update (select ...,...,... from tableA,tableB) set ........
ASKER
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
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;
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.
ASKER
Hi Sdstuber
Thanks, Then in when i should consider using splitting of data into small pieces. I mean transactions in numbers?.Please suggest
Thanks, Then in when i should consider using splitting of data into small pieces. I mean transactions in numbers?.Please suggest
that depends on the size of your database + size of UNDO + size of your server
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
please take a look at http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php