Home » SQL & PL/SQL » SQL & PL/SQL » SQL Doubt: Update of salary ALL employees by 10%
SQL Doubt: Update of salary ALL employees by 10% [message #232759] Mon, 23 April 2007 03:55 Go to next message
hp_tvm
Messages: 5
Registered: April 2007
Junior Member
Hi - Which is the simplest and most efficient way to execute the following - To update the salary of all employees by 10%.

The normal method would be to use - UPDATE ... SET SAL = SAL*1.1

Thank you
Re: SQL Doubt: Update of salary ALL employees by 10% [message #232760 is a reply to message #232759] Mon, 23 April 2007 03:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That would be the way, yes.

One update in pure SQL. I can't see any alternative being more performant.

MHE
Re: SQL Doubt: Update of salary ALL employees by 10% [message #232761 is a reply to message #232760] Mon, 23 April 2007 04:00 Go to previous messageGo to next message
hp_tvm
Messages: 5
Registered: April 2007
Junior Member
Thanks. But Im looking at updating 1 million records at one shot. Do you still think this is the best way or does Oracle 10g has some new features that would permit bulk update
Re: SQL Doubt: Update of salary ALL employees by 10% [message #232762 is a reply to message #232761] Mon, 23 April 2007 04:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Laughing What's more bulk than a single update?

MHE
Re: SQL Doubt: Update of salary ALL employees by 10% [message #232769 is a reply to message #232762] Mon, 23 April 2007 04:16 Go to previous messageGo to next message
hp_tvm
Messages: 5
Registered: April 2007
Junior Member
It looks straightforward I agree. What i wanted to know is whether there was some syntax like BULK etc. I have heard of BULK COLLECT. Has it anything to do with this? Can it be used in a SQL

Incidentally this question was put to my friendf during an interview. So I was wondering if there's something more than what meets the eye
Re: SQL Doubt: Update of salary ALL employees by 10% [message #232777 is a reply to message #232769] Mon, 23 April 2007 04:29 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Tom Kyte has a mantra. It goes like this:
  • If you can do it in a single SQL statement do so.
  • If you cannot do it in a single SQL Statement, then do it in PL/SQL.
  • If you cannot do it in PL/SQL, try a Java Stored Procedure.
  • If you cannot do it in Java, do it in a C external procedure.
  • If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…


I tend to believe him. Bulk operations in PL/SQL are faster than regular cursor loops but I don't think they can beat the SQL engine in performance.

MHE
Re: SQL Doubt: Update of salary ALL employees by 10% [message #232804 is a reply to message #232769] Mon, 23 April 2007 05:57 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The FORALL and BULK options are ways to improve the performance of Pl/Sql where you are stepping through a set of records one at a time. They do help, but they still leave the final performance slower than a straight SQL operation.

A couple of things that might be worth looking at:
1) If there are triggers on the table that would fire for each row of this update, consider disabling them. You'll need to check that the triggers don't do anything vital, but that could speed things up.

2) If the column(s) being updated are indexes, it might be quicker to drop the indexes and rebuild them at the end of the operation. This will probably mean that your users won't be able to use a chunk of your application while you're doing the update, but anything really large is (probably) going to be done out of hour anyway.
Previous Topic: wrong filesize with utl_file.fgetattr ?
Next Topic: Catch a TABLE NAME
Goto Forum:
  


Current Time: Sat Dec 10 07:24:20 CST 2016

Total time taken to generate the page: 0.06852 seconds