CODEWITHSUNDEEP
oracle-databaseperformanceplsqlbulkinsert
Nishant Gupta
Nishant Gupta

Reputation: 19

How to improve performance of FORALL insert routine?

I have a requirement to insert all those records which do not already exist in the table with bulk collect in Oracle. My code is here, it's taking too much time.

forall i in 1 .. arr_upd_mbr.count save exceptions
      insert into claim.member_contact(member_id_rx, addr_street, addr_apt, 
        addr_city, addr_state, addr_zip, contact_phone,
        start_dt, end_dt, gender, dob)
      select 
        arr_upd_mbr(i).member_id_rx, arr_upd_mbr(i).mbr_addr_street, arr_upd_mbr(i).mbr_addr_apt,
        arr_upd_mbr(i).mbr_addr_city, arr_upd_mbr(i).mbr_addr_state, arr_upd_mbr(i).mbr_addr_zip || arr_upd_mbr(i).zip_reserve,
        arr_upd_mbr(i).primary_phone, trunc(sysdate), arr_upd_mbr(i).elg_end_dt,
        arr_upd_mbr(i).mbr_gender, arr_upd_mbr(i).mbr_dob
      from dual
      where not exists(select 1
                       from claim.member_contact
                       where member_id_rx            = arr_upd_mbr(i).member_id_rx
                         and nvl(addr_street, '~')   = nvl(arr_upd_mbr(i).mbr_addr_street, '~')
                         and nvl(addr_apt, '~')      = nvl(arr_upd_mbr(i).mbr_addr_apt, '~')
                         and nvl(addr_city, '~')     = nvl(arr_upd_mbr(i).mbr_addr_city, '~')
                         and nvl(addr_state, '~')    = nvl(arr_upd_mbr(i).mbr_addr_state, '~')
                         and nvl(addr_zip, '~')      = nvl((arr_upd_mbr(i).mbr_addr_zip || arr_upd_mbr(i).zip_reserve), '~')
                         and nvl(contact_phone, '~') = nvl(arr_upd_mbr(i).primary_phone, '~')
                         and nvl(gender, '~')        = nvl(arr_upd_mbr(i).mbr_gender, '~')
                         and nvl(dob, v_last_date)   = nvl(arr_upd_mbr(i).mbr_dob, v_last_date)
                         and sysdate between start_dt and end_dt);
    exception
      when e_dml_errors then
        save_mem_change_exp(p_mbr_enrl_log_id_rx, 'MEMBER_CONTACT(Update)', arr_upd_mbr);
  end;

Upvotes: 0

Views: 149

Answers (1)

APC
APC

Reputation: 146349

Doing not exists in the FORALL will severely reduce any performance benefits from the bulk operation.

You haven't provided any context so it's hard for us to provide any meaningful advice but you might get better performance from re-writing your code. For example :

  1. Revisit the WHERE clause of the subquery. What you have there must being running a Full Table Scan of claim.member_contact. Maybe you can find a better way of identifying existing addresses.
  2. Verify the presence of existing records when populating the array, and discard the ones you don't need. Then your FORALL statement will only insert new records.
  3. Forget about FORALL ... INSERT and use MERGE instead; you only need code the WHEN NOT MATCHED THEN INSERT branch.

Upvotes: 4

Related Questions