| Merge Vs Cursor Processing [message #449921] |
Fri, 02 April 2010 05:16  |
saikumar_mudigonda
Messages: 23
Registered: June 2008
Location: hyderabad
|
Junior Member |
|
|
Hi All,
My requirement is ..
SQL> CREATE TABLE EMP (EMPID VARCHAR2(100), U_EMPID VARCHAR2(100));
Table created.
SQL> INSERT INTO EMP VALUES ('DUMMY1','J0001');
1 row created.
SQL> INSERT INTO EMP VALUES ('DUMMY2','J1002');
1 row created.
SQL> INSERT INTO EMP VALUES ('DUMMY3','J1003');
1 row created.
SQL> SELECT * FROM EMP;
EMPID U_EMPID
------------------
DUMMY1 J0001
DUMMY2 J1002
DUMMY3 J1003
in this example empid is dummy empid (which will be issued for contractors), once the contractors are considered to be taken into payroll that u_empid will be updated by HR manually, we need to replace empid with u_empid. then data will become like this.
SQL> SELECT * FROM EMP;
EMPID U_EMPID
---------------------------
DUMMY1 J0001
DUMMY2 J1002
DUMMY3 J1003
J1001 NULL
J1002 NULL
J1003 NULL
now my requirement is there are approximately 60 child tables are there for EMP table, and EMPID is the foreign key for all of them with "on delete cascade" defined on those. Now first i need to create records in all the child tables with actual employee id's (which is u_empid). if the actual id is already exists then just need to update rest of the columns in that table except the EMPID.
Once the records are created with actual empid, then i will issue a delete command to remove dummy employee id's in emp table which will take care of deletion in child tables as well.
I was thinking of two options, one is go with Merge other is write a cursor and handle the update in exception block using when duplicate value on index feature.
I need a suggestion, which one will be faster and good in performance.
Thanks
Sai Kumar.
|
|
|
|
| Re: Merge Vs Cursor Processing [message #449932 is a reply to message #449921] |
Fri, 02 April 2010 07:10  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Merge will be faster that insert-or-update-on-exception, especially as you increase the number of rows you are working on.
Doesn't seem like a very good implementation to me: all these dummy rows lying around - deleting them - inserting new rows. Have you considered redesigning?
Ross Leishman
|
|
|
|
| Re: Merge Vs Cursor Processing [message #450105 is a reply to message #449921] |
Sun, 04 April 2010 23:35 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
It is not a great design. Problem is though, it is somewhat common in legacy system design and there is likely a lot of existing application code built around the meaning of these dummy values and being able to identify the dummies. If it is like some of the systems I work on it would take a couple of man years to "redesign".
Also, once again Ross and I will butt heads as once again I am compelled to restate my opinions on MERGE.
It is true, MERGE will be faster than cursor processing and the traditional update_else_insert logic, sometimes by a wide margin if data runs into the millions of rows.
It is also true that MERGE is an advanced oracle feature and like most oracle advanced features, it does not play well with other oracle advanced features. In particular, it does not work in an intuitive way with table triggers, and completely fails with exceptions when it encounters instead-of-triggers.
If you want to use MERGE you will have to forget about using INSTEAD-OF-TRIGGERS, and re-evaluate your expectations for table triggers.
If you have plans for INSTEAD-OF-TRIGGERS and TABLE TRIGGERS or your system already uses these, then you should forget about using MERGE.
It is a choice like most things in life. Figure out which is more important to you, MERGE or TRIGGERS, you can't have both. Choose and move on.
Good luck, Kevin
[Updated on: Mon, 05 April 2010 23:31] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
