Oracle FORALL & PostgreSQL ? op any|all (ARRAY)

Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:

• Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
• Postgres-XL的项目发起人Mason Sharp
• pgpool的作者石井达夫(Tatsuo Ishii)
• PG-Strom的作者海外浩平(Kaigai Kohei)
• Greenplum研发总监姚延栋
• 周正中(德哥), PostgreSQL中国用户会创始人之一
• 汪洋，平安科技数据库技术部经理
• ……

2015年度PG大象会报名地址：http://postgres2015.eventdove.com/ PostgreSQL中国社区： http://postgres.cn/ PostgreSQL专业1群： 3336901（已满） PostgreSQL专业2群： 100910388 PostgreSQL专业3群： 150657323

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