Home » RDBMS Server » Performance Tuning » UPDATE query with JOIN
icon5.gif  UPDATE query with JOIN [message #401592] Tue, 05 May 2009 12:38 Go to next message
jaytav001
Messages: 4
Registered: May 2009
Junior Member

Suppose I am having following 2 tables

Emp_det
Name Grade Sal
ABC A
PQR B
XYZ C

and

Grade_det
Grade Sal
A 1000
B 2000
C 3000

Now I want to update salary of employee having grade A.
Simplest way is

update emp_det e
set sal =
(
select g.sal from grade_det g
where e.grade = g.grade
and g.grade = 'A'
)
where exists
(
select g.sal from grade_det g
where e.grade = g.grade
and g.grade = 'A'
);

But using "where exists" is very slow if my tables contains high volume of data.

Is there any other way to do the same with fast execution?
Re: UPDATE query with JOIN [message #401593 is a reply to message #401592] Tue, 05 May 2009 12:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
UPDATE emp_det e
SET    sal = (SELECT g.sal
              FROM   grade_det g
              WHERE  e.grade = g.grade
                     AND g.grade = 'A')
WHERE  EXISTS (SELECT g.sal
               FROM   grade_det g
               WHERE  e.grade = g.grade
                      AND g.grade = 'A'); 

All columns in WHERE clauses need indexes.
All tables & indexes need current statistics.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: UPDATE query with JOIN [message #401594 is a reply to message #401592] Tue, 05 May 2009 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But using "where exists" is very slow if my tables contains high volume of data.

Is it a fact? Do you any evidence of this?

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the required information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
icon5.gif  Re: UPDATE query with JOIN [message #401595 is a reply to message #401593] Tue, 05 May 2009 12:55 Go to previous messageGo to next message
jaytav001
Messages: 4
Registered: May 2009
Junior Member

Assuming that index on tables are proper, is there any other way to write update query with join ???
Re: UPDATE query with JOIN [message #401596 is a reply to message #401592] Tue, 05 May 2009 13:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN PLAN
Re: UPDATE query with JOIN [message #401691 is a reply to message #401596] Wed, 06 May 2009 03:22 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Search for the UPDATE section in this article. It describes two techniques you can use that will avoid the problems you describe.

Ross Leishman
Previous Topic: Locks in Oracle
Next Topic: Slow Order by, Index needed?
Goto Forum:
  


Current Time: Fri Dec 09 07:50:56 CST 2016

Total time taken to generate the page: 0.08746 seconds