使用bulk collect 和 forall 提高游标性能

最新推荐文章于 2021-06-11 08:51:50 发布

原创最新推荐文章于 2021-06-11 08:51:50 发布 · 5.5k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#游标 #plsql

oracle11G 专栏收录该内容

31 篇文章

订阅专栏

当运行一个pl/sql程序时， pl/sql语句引擎会执行pl/sql语句。但如果在这个过程中引擎遇到sql语句，它会把这个语句传给sql引擎（后台发生上下文切换）。

在PL/SQL和SQL引擎(engines)中，太多的上下文切换(context switches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。而使用批挷定能显著提高性能。

在SQL语句中，为PL/SQL变量指定值称为挷定(binding)，
　　DML语句能传递所有集合元素到一个单个操作中，这过程称为批挷定（bulk binding）。
　　如果集合有20个元素，批挷定让你用单个操作等效于执行与20个SELECT，INSERT， UPDATE或DELETE语句。这个技术通过减少在PL/SQL和SQL引擎(engines)间的上下文切换来提高性能。批挷定包括：
　　1．带INSERT, UPDATE, and DELETE语句的批挷定：在FORALL语句中嵌入SQL语句
　　2．带SELECT语句的批挷定：在SELECT语句中用BULK COLLECT 语句代替INTO 。

作用：BULK COLLECT提供对数据的高速检索。

优点：可以将多个行引入一个或多个集合中，而不是单独变量或记录中,减少了上下文切换，性能高。

缺点：1.消耗更多的内存(PGA)；由于该数集合据存储在每个会话中,假设一个会话多消耗5M，内存，那么1000个就消耗约5G内存。

2.BULK COLLECT INTO的目标对象必须是集合类型。

3.不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。

4.如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于 BULK COLLECTINTO 子句中。

bulk collect语句可以使用三种方式：

1在select into语句中使用bulk collect

2在fetch into中使用bulk collect

3在returning into中使用bulk collect

create table empl_tbl(last_name varchar2(20),
first_name varchar2(10),
salary number(10));

--创建表
create table demo_t(
  id number(5),
  name varchar2(50),
  sales number
);

--在select into语句中使用bulk collect
DECLARE
TYPE contractList IS TABLE OF sad.cm_contracts_t.contract_number%TYPE;
contracts contractList;
BEGIN
-- Limit the number of rows to 100.
SELECT contract_number BULK COLLECT INTO contracts FROM sad.cm_contracts_t
WHERE ROWNUM <= 100;


--在fetch into中使用bulk collect
DECLARE
TYPE contracts_list IS TABLE OF sad.cm_contracts_t%ROWTYPE;
contracts_l contracts_list;
CURSOR c1 IS
SELECT contract_number,contract_id FROM contracts_l WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;

--在returning into中使用bulk collect
--CREATE TABLE item AS SELECT * FROM sad.sad_prm_item_ti;
declare 
  -- Local variables here
TYPE lineid IS TABLE OF item.lineid%TYPE;
line_id lineid;
begin
DELETE FROM item t WHERE t.lineid='1'
RETURNING lineid  BULK COLLECT INTO line_id;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN line_id.FIRST .. line_id.LAST
 LOOP
    dbms_output.put_line('item : '||line_id(i));
 END LOOP;
end;

-- Created on 2013/12/4 by ZWX190516 
DECLARE
  -- Local variables here
  all_row       NUMBER(10);
 all_rows_bluk       NUMBER(10);
  temp_last_name empl_tbl.last_name%TYPE;
  --首先，定义一个Index-by表数据类型
  TYPE last_name_tab IS TABLE OF empl_tbl.last_name%TYPE INDEX BY BINARY_INTEGER;
  last_name_arr last_name_tab;
  --定义一个Index-by表集合变量
  dis_count NUMBER;
  
  --数据量
   data_count NUMBER :=100000;
  --记录时间
  t1 NUMBER;
  t2 NUMBER;
  t3 NUMBER;
  t4 NUMBER;
BEGIN
  all_row       := 0;
  all_rows_bluk       := 0;
  temp_last_name := ' ';


  --写入1W笔数据
  FOR i IN 1 .. data_count LOOP
    INSERT INTO empl_tbl
      (last_name, first_name, salary)
    VALUES
      ('carl' || (i), 'wu' || (data_count-1), i);
  END LOOP;

  COMMIT;
  
  
--记录时间
  SELECT DBMS_UTILITY.get_time INTO t1 FROM DUAL;
  
  --查询不相同数据使用时间
  SELECT COUNT(DISTINCT last_name) "Distinct Last Name"
    INTO dis_count
    FROM empl_tbl; 
    
--记录时间
  SELECT DBMS_UTILITY.get_time INTO t2 FROM DUAL;
  
  --使用简单游标实现
  BEGIN
    FOR cur IN (SELECT last_name FROM empl_tbl ORDER BY last_name) LOOP
      IF cur.last_name != temp_last_name THEN
        all_row := all_row + 1;
      END IF;
      temp_last_name := cur.last_name;
    END LOOP;
    dbms_output.put_line('all_rows are ' || all_row);
  END;

--记录时间
  SELECT DBMS_UTILITY.get_time INTO t3 FROM DUAL;
  --使用Bulk Collect来实现
BEGIN
  SELECT last_name BULK COLLECT INTO last_name_arr FROM empl_tbl;
  FOR i IN 1 .. last_name_arr.count LOOP
    IF temp_last_name != last_name_arr(i) THEN
      all_rows_bluk := all_rows_bluk + 1;
    END IF;
    temp_last_name := last_name_arr(i);
  END LOOP;
  dbms_output.put_line(' BULK COLLECT  all_rows are ' || all_rows_bluk);
  END;
--记录时间
  SELECT DBMS_UTILITY.get_time INTO t4 FROM DUAL;
  
  
    DBMS_OUTPUT.put_line('Execution Time (hsecs)');
  DBMS_OUTPUT.put_line('---------------------');
  DBMS_OUTPUT.put_line('distince: ' || TO_CHAR(t2 - t1));
  DBMS_OUTPUT.put_line('cursor one by one :   ' || TO_CHAR(t3 - t2));
    DBMS_OUTPUT.put_line('cursor BULK COLLECTe :   ' || TO_CHAR(t4 - t3));

END;

FORALL

作用：FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。

在发送语句到SQL引擎前，FORALL语句告知PL/SQL引擎批挷定输入集合。尽管FORALL语句包含一个迭代(iteration)模式，它并不一是个FOR循环。

优点：一次性绑定数据写进行操作。减少上下文切换，提高数据库性能。

缺点：1.操作逻辑单一，只能写一个sql。

2.若出现异常，必须捕获异常后提交才能保证数据正常操作。

forall语句使用三种方式(只允许一条sql)：

1.FORALL 下标变量(只能当作下标被引用) IN下限..上限。

2.INDICES OF collection_name (引用特定集合元素的下标(该集合可能为稀疏))。

3.VALUES OF colletion_name （把该集合中的值当作下标，且该集合值的类型只能是PLS_INTEGER BINARY_INTEGER)

）。

--批量插入演示简单；必须顺序
declare
  type tb_table_type is table of demo_t%rowtype
    index by binary_integer;
  tb_table tb_table_type;
begin
  for i in 1..10 loop
    tb_table(i).id:=i;
    tb_table(i).name:='NAME'||i;
  end loop;
  forall i in 1..tb_table.count
    insert into demo_t values tb_table(i);
end;

--批量修改演示
declare
  type demo_t_type is table of demo_t%rowtype
  index by binary_integer;
  demo_t demo_t_type;
begin
  for i in 1..10 loop
    demo_t(i).id:=i;
    demo_t(i).name:='NAMES'||i;
  end loop;
  forall i in 1..demo_t.count
    update tb1 t set row = demo_t(i) where t.id = demo_t(i).id;
end;

--批量删除演示
declare
  type demo_t_type is table of demo_t%rowtype
  index by binary_integer;
  demo_t demo_t_type;
begin
  for i in 1..10 loop
    demo_t(i).id:=i;
    demo_t(i).name:='NAMES'||i;
  end loop;
  forall i in 1..demo_t.count
    delete tb1 where id = demo_t(i).id;
end;


select * from demo_t

--批量写入不连续的数组

declare
  type demo_t_table_type is table of demo_t%rowtype
    index by binary_integer;
  demo_t_table demo_t_table_type;
begin
  for i in 1..10 loop
    demo_t_table(i).id:=i;
    demo_t_table(i).name:='NAME'||i;
  end loop;
  demo_t_table.delete(3);
  demo_t_table.delete(6);
  demo_t_table.delete(9);
  forall i in indices of demo_t_table
    insert into demo_t values demo_t_table(i);
end;



--按照下标写入数组数据
declare
  type index_poniter_type is table of pls_integer;
  index_poniter index_poniter_type;
  type demo_t_table_type is table of demo_t%rowtype
    index by binary_integer;
  demo_t_table demo_t_table_type;
begin
  index_poniter:=index_poniter_type(1,3,5,7);
  for i in 1..10 loop
    demo_t_table(i).id:=i;
    demo_t_table(i).name:='NAME'||i;
  end loop;
  forall i in values of index_poniter
    insert into demo_t values demo_t_table(i);
end;

-- Created on 2013/12/5 by ZWX190516 
DECLARE
  -- Local variables here
  TYPE numtab IS TABLE OF NUMBER(20) INDEX BY BINARY_INTEGER;

  TYPE nametab IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

  pnums  numtab;
  pnames nametab;
  t1     NUMBER;
  t2     NUMBER;
  t3     NUMBER;
BEGIN
  FOR j IN 1 .. 1000000 LOOP
    pnums(j) := j;
    pnames(j) := 'Seq No. ' || TO_CHAR(j);
  END LOOP;

--记录时间
  SELECT DBMS_UTILITY.get_time INTO t1 FROM DUAL;
--普通游标写入
  FOR i IN 1 .. 1000000 LOOP
    INSERT INTO blktest VALUES (pnums(i), pnames(i));
  END LOOP;
  
--记录时间
  SELECT DBMS_UTILITY.get_time INTO t2 FROM DUAL;

--forall写入
  FORALL i IN 1 .. 1000000
    INSERT INTO blktest VALUES (pnums(i), pnames(i));

--记录时间
  SELECT DBMS_UTILITY.get_time INTO t3 FROM DUAL;

  DBMS_OUTPUT.put_line('Execution Time (hsecs)');
  DBMS_OUTPUT.put_line('---------------------');
  DBMS_OUTPUT.put_line('FOR loop: ' || TO_CHAR(t2 - t1));
  DBMS_OUTPUT.put_line('FORALL:   ' || TO_CHAR(t3 - t2));

END;

异常：Forall在出现异常情况后，捕获到异常不进行事物处理，那么将会自动回滚。

其他用法：

1.用%BULK_ROWCOUNT属性计算FORALL迭代影响行数。

1.2用%BULK_EXCEPTIONS属性处理FORALL异常

CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(15));
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(20, 'Bookkeeper'); -- 10-char job title
INSERT INTO emp2 VALUES(30, 'Analyst');
INSERT INTO emp2 VALUES(30, 'Analyst');
Comit;
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 30);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
UPDATE emp2 SET job = job || ' (temp)'
WHERE deptno = depts(j);
-- raises a "value too large" exception
EXCEPTION
WHEN OTHERS THEN
COMMIT;
END;

PL/SQL Developer Test script 3.0
12
DECLARE 
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 50); 
BEGIN 
FORALL j IN depts.FIRST..depts.LAST 
UPDATE demo_t t SET sales = sales * 1.10 WHERE t.id=depts(j);
dbms_output.put_line( SQL%BULK_ROWCOUNT(3)  );
END;


在进行SQL数据操作语句时，SQL引擎打开一个隐式游标(命名为SQL)，该游标的标量属性(scalar attribute)有 %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT。 
　　FORALL语句除具有上边的标量属性外，还有个复合属性(composite attribute)：%BULK_ROWCOUNT，该属性具有索引表(index-by table)语法。它的第i个元素存贮SQL语句(INSERT, UPDATE或DELETE)第i个执行的处理行数。如果第i个执行未影响行，%bulk_rowcount (i)，返回0。FORALL与%bulk_rowcount属性使用相同下标。
0
0

PL/SQL Developer Test script 3.0
27
DECLARE 
TYPE NumList IS TABLE OF NUMBER; 
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1); 
errors NUMBER; 
dml_errors EXCEPTION; 
PRAGMA exception_init(dml_errors, -24381); 
BEGIN 
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS 
DELETE FROM demo_t  WHERE sales > 500000/num_tab(i); 
EXCEPTION 
WHEN dml_errors THEN 
errors := SQL%BULK_EXCEPTIONS.COUNT; 
dbms_output.put_line('Number of errors is ' || errors); 
FOR i IN 1..errors LOOP 
dbms_output.put_line('Error ' || i || ' occurred during '|| 
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); 
dbms_output.put_line('Oracle error is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); 
END LOOP; 
END;


执行期间引发的所有异常都被保存游标属性 %BULK_EXCEPTIONS中，它存贮一个集合记录，每记录有两个字段： 
　　%BULK_EXCEPTIONS(i).ERROR_INDEX：存贮在引发异常期间FORALL语句迭代(重复：iteration) 
　　%BULK_EXCEPTIONS(i).ERROR_CODE：存贮相应的Oracle错误代码 
　　%BULK_EXCEPTIONS.COUNT存贮异常的数量。(该属性不是%BULK_EXCEPTIONS集合记录的字段)。如果忽略SAVE EXCEPTIONS，当引发异常时，FORALL语句停止执行。此时，SQL%BULK_EXCEPTIONS.COUNT 返回1, 且SQL%BULK_EXCEPTIONS只包含一条记录。如果执行期间无异常 SQL%BULK_EXCEPTIONS.COUNT 返回 0

注意： 1.bulk collect 消耗的是PGA内存。每个会话都会占内存，所以尽可能使用limit限制。

2.当pl/sql优化等级为2(默认)或更高，把游标内容直接写在for循环之中有利于提高性能，除非其他特殊情况。

3.注意cursor%notfond的正确使用。

PL/SQL Developer Test script 3.0
26
-- Created on 2013/11/25 by ZWX190516 
declare 
  -- Local variables here
  i integer;
  TYPE type_t IS TABLE OF sad.cm_contracts_t%ROWTYPE;
  type_table type_t;  
  CURSOR cur_test IS
  SELECT * FROM sad.cm_contracts_t t
  WHERE rownum=1;
begin
  -- Test statements here
  OPEN cur_test;
  LOOP
      FETCH cur_test BULK COLLECT   INTO type_table  LIMIT 3;
   EXIT WHEN cur_test%NOTFOUND; --该句判断放在前边，导致漏掉数据。
    FOR i IN type_table.first..type_table.last
      LOOP
        dbms_output.put_line(type_table(i).contract_id);
        END LOOP;
        

   END LOOP;
   
   CLOSE cur_test;
  
end;
0
0