title: Performance Issue with Oracle Merge Statements with more than 2 Million records
body: <p>I am executing the below MERGE statement for Insert Update operation. </p>
  
  <p>It is working fine for 1 to 2 million records but for more than 4 to 5 billion records it takes 6 to 7 hours to complete.</p>
  
  <p>Can anyone suggest some alternative or performance tips for Merge Statement</p>
  
  <pre><code> merge into employee_payment ep
    using (
       select
          p.pay_id vista_payroll_id,
          p.pay_date pay_dte,
          c.client_id client_id,
          c.company_id company_id,
          case p.uni_ni when 0 then null else u.unit_id end unit_id,
          p.pad_seq pay_dist_seq_nbr,
          ph.payroll_header_id payroll_header_id,
          p.pad_id vista_paydist_id,
          p.pad_beg_payperiod pay_prd_beg_dt,
          p.pad_end_payperiod pay_prd_end_d
       from
          stg_paydist p
          inner join company c on c.vista_company_id = p.emp_ni
          inner join payroll_header ph on ph.vista_payroll_id = p.pay_id
          left outer join unit u on u.vista_unit_id = p.uni_ni
       where ph.deleted = '0'
    ) ps
       on (ps.vista_paydist_id = ep.vista_paydist_id)
  
    when matched then
    update
       set ep.vista_payroll_id      = ps.vista_payroll_id,
           ep.pay_dte               = ps.pay_dte,
           ep.client_id             = ps.client_id,
           ep.company_id            = ps.company_id,
           ep.unit_id               = ps.unit_id,
           ep.pay_dist_seq_nbr      = ps.pay_dist_seq_nbr,
           ep.payroll_header_id     = ps.payroll_header_id
  
    when not matched then
    insert (
       ep.employee_payment_id,
       ep.vista_payroll_id,
       ep.pay_dte,
       ep.client_id,
       ep.company_id,
       ep.unit_id,
       ep.pay_dist_seq_nbr,
       ep.payroll_header_id,
       ep.vista_paydist_id
    ) values (
       seq_employee_payments.nextval,
       ps.vista_payroll_id,
       ps.pay_dte,
       ps.client_id,
       ps.company_id,
       ps.unit_id,
       ps.pay_dist_seq_nbr,
       ps.payroll_header_id,
       ps.vista_paydist_id
    ) log errors into errorlog (v_batch || 'EMPLOYEE_PAYMENT') reject limit unlimited;
  </code></pre>