SQL Update Performance

["Murphy, Mark" <MurphyM@xxxxxxxxxx>]

I have an update that I am trying to do on V5R2:

 

UPDATE ADDRINP ad1 

SET ad1.Addr1 = 'this is a test'               

WHERE exists( 

select * from contrcp ctr  where 

      ctr.cinact = 'CC01' and 

ctr.ctradr = ad1.akey)                                             

 

This statement takes forever to run because of the join order the optimizer is choosing.  ADDRINP is
in Join position 1 and CONTRCP is in join position 2.  This is most likely because I am updating
ADDRINP.  Only problem is that there are 3,000,000 records in ADDRINP, and putting it in join position
1 forces the whole file to be read in order to pick out 6 records to be processed.  Can anyone think
of a way to force CONTRCP to join position 1?

 

Mark Murphy

Star Base Consulting, Inc.

e: murphym@xxxxxxxxxx

 


This e-mail transmission contains information that is intended to be confidential and privileged.  If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful.  Please reply to the message immediately by informing the sender that the message was misdirected.  After replying, please delete and otherwise erase it and any attachments from your computer system.  Your assistance in correcting this error is appreciated.