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 corrasponding records in the JOB_CURR_VW view that pass a few tests should be updated. The requirements are:

• The JOB_CURR_VW.empl_status is in ('L','P','A')
• The PER_NAM_V2.per_prm_cd='Y' (where the emplid=per_id_no)

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 concidered 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 simplily changing the update statement)? Any suggestions would be appreciated. Received on Fri Nov 22 1996 - 00:00:00 CET