Fine tuning update query

Question

I am developing a batch process in the peoplesoft application engine.

I have inserted data in staging table from JOB table.

There are 120,596 employees in total, whose data have to be processed, this is in development environment.

In testing environment, the number of rows to be processed is 249047.

There are many non job data which also have to be sent for employees.

My design is in such way that I will write individual update statements to update the data in the table, then I will select data from the staging table and write it in the file.

The update is taking too much time, I would like to know a technique to fine tune it.

Searched for many things, and even tried using /* +Append */ in the update query, but it throws an error message, sql command not ended.

Also, my update query has to check for nvl or null values.

Is there any way to share the code over stackoverflow, I mean, this is insert,update statement, written in peoplesoft actions, so that people here can have a look into that?

Kindly suggest me a technique, my goal is to finish the execution within 5-10 minutes.

My update statement:

I have figured out the cause. It is this update statement

 UPDATE %Table(AZ_GEN_TMP) 
  SET AZ_HR_MANAGER_ID = NVL(( 
 SELECT e.emplid 
  FROM PS_EMAIL_ADDRESSES E 
 WHERE UPPER(SUBSTR(E.EMAIL_ADDR, 0, INSTR(E.EMAIL_ADDR, '@') -1)) = ( 
 SELECT c.contact_oprid 
  FROM ps_az_can_employee c 
 WHERE c.emplid = %Table(AZ_GEN_TMP).EMPLID 
   AND c.rolename='HRBusinessPartner' 
   AND c.seqnum = ( 
 SELECT MAX(c1.seqnum) 
  FROM ps_az_can_employee c1 
 WHERE c1.emplid= c.emplid 
   AND c1.rolename= c.rolename ) ) 
   AND e.e_addr_type='PINT'), ' ') 

In order to fine tune this,I am inserting the value contact_oprid in my staging table, using hint.

SELECT /* +ALL_ROWS */ c.contact_oprid 
      FROM ps_az_can_employee c 
     WHERE c.emplid = %Table(AZ_GEN_TMP).EMPLID 
       AND c.rolename='HRBusinessPartner' 
       AND c.seqnum = ( 
     SELECT MAX(c1.seqnum) 
      FROM ps_az_can_employee c1 
     WHERE c1.emplid= c.emplid 
       AND c1.rolename= c.rolename ) ) 
       AND e.e_addr_type='PINT')

and doing an update on staging table:

 UPDATE staging_table
  SET AZ_HR_MANAGER_ID = NVL(( 
 SELECT e.emplid 
  FROM PS_EMAILtable E 
 WHERE UPPER(REGEXP_SUBSTR(e.email_addr,'[^@]+',1,1)) = staging_table.CONTACT_OPRID 
   AND e.e_addr_type='PINT'),' ') /

This will take 5 hours, as it has to process 2 lakhs rows of data.

Is there any way using which the processing can be speed up, i mean, using hints or indexes?

Also, if I don't use this, the processing to update other value is very fast, gets finished in 10 minutes.

Kindly help me with this.

Thanks.

Answer 1

I have resolved this, used MERGE INTO TABLE oracle statement, and now the process takes 10 minutes to execute, including file writing operation. Thanks all for your help and suggestions.