PostgreSQL Oracle 兼容性之 - Oracle FORALL & PostgreSQL ? op any all (ARRAY)

背景

On Oracle

On my laptop running Oracle Database 11g Release 2, it took 4.94 seconds to insert 100,000 rows, one at a time. With FORALL, those 100,000 were inserted in 0.12 seconds. Wow!

Instead of doing that, I can simply remove all ineligible IDs from the l_employee_ids collection, as follows:

   FOR indx IN 1 .. l_employee_ids.COUNT  
   LOOP  
      check_eligibility (l_employee_ids (indx),  
                         increase_pct_in,  
                         l_eligible);  
  
      IF NOT l_eligible  
      THEN  
         l_employee_ids.delete (indx);  
      END IF;  
   END LOOP;  

But now my l_employee_ids collection may have gaps in it: index values that are undefined between 1 and the highest index value populated by the BULK COLLECT.

No worries. I will simply change my FORALL statement to the following:

FORALL indx IN INDICES OF l_employee_ids  
   UPDATE employees emp  
      SET emp.salary =  
               emp.salary  
             + emp.salary *   
                increase_salary.increase_pct_in  
    WHERE emp.employee_id =   
      l_employee_ids (indx);  

Now I am telling the PL/SQL engine to use only those index values that are defined in l_employee_ids, rather than specifying a fixed range of values. Oracle Database will simply skip any undefined index values, and the ORA-22160 error will not be raised.

On PostgreSQL

postgres=# do language plpgsql $$  
declare  
begin  
for i in 1..100000 loop  
  update test set info='test' where id=i;  
end loop;  
end;  
$$;  
DO  
Time: 3509.395 ms  
  
postgres=# do language plpgsql $$  
declare   
  i int[];  
begin  
  select array_agg(x) into i from generate_series(1,100000) t(x);   
  update test set info='test' where id = any(i);  
end;                                     
$$;  
DO  
Time: 1504.004 ms  

参考

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm#i48876

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/forall_statement.htm

http://www.postgresql.org/docs/9.4/static/arrays.html

http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html

digoal’s 大量PostgreSQL文章入口