Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Correlated Update (Performance Question w/source code]
Situation:
I am trying to update the PS_LOCATION_LL with data from the PER_NAM_V2
table (which have some 16,000 records). Only the PS_LOCATION_LL records
which have corresponding records in the JOB_CURR_VW view that pass a few
tests should be updated. The requirements are:
The PS_LOCATION_LL (a), PER_NAM_V2 (b) and PS_CURR_VW (c) are joined on a.per_id_no=b.emplid and c.emplid=b.emplid. The select I have used is:
update ps_location_ll a
SET (EMAIL_ID_NAM_LL, EMAIL_NICK_NAM_LL)=
(select nvl(b.PER_EMAIL_ID_NAM,' '), nvl( b.PER_EMAIL_NICK_NAM, ' ') from lp.Per_nam_v2 b where a.emplid=b.per_id_no) WHERE exists (select nvl(b.PER_EMAIL_ID_NAM,' '), nvl( b.PER_EMAIL_NICK_NAM, ' ') from ps_job_curr_vw c, lp.per_nam_v2 b where c.emplid = a.emplid and c.emplid = b.per_id_no and c.empl_status in ('A','L' ,'P') );
Question:
I am considered about the performance of this as I am referencing the PER_NAM_V2 view twice which has about 16,000 records in it. Is there a more efficient way to do this (using a cursor or simply changing the update statement)? Any suggestions would be appreciated. Received on Fri Nov 22 1996 - 00:00:00 CST