OraFAQ Forum: Performance Tuning » UPDATE query with JOIN

Home » RDBMS Server » Performance Tuning » UPDATE query with JOIN

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UPDATE query with JOIN [message #401592]

Tue, 05 May 2009 12:38

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?

Report message to a moderator

Re: UPDATE query with JOIN [message #401593 is a reply to message #401592]

Tue, 05 May 2009 12:46

BlackSwan
Messages: 26766
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/

Report message to a moderator

Re: UPDATE query with JOIN [message #401594 is a reply to message #401592]

Tue, 05 May 2009 12:50

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator