ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle批量绑定 forall bulk collect用法以及测试案例

oracle批量绑定 forall bulk collect用法以及测试案例

原创 Linux操作系统 作者:tyylm 时间:2008-03-13 16:24:48 0 删除 编辑

通过bulk collect减少loop处理的开销

采用bulk collect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。可以在select into,fetch into,returning into语句使用bulk collect。注意在使用bulk collect时,所有的into变量都必须是collections.

 

举几个简单的例子:

--在select into语句中使用bulk collect

DECLARE

TYPE SalList IS TABLE OF emp.sal%TYPE;

sals SalList;

BEGIN

-- Limit the number of rows to 100.

SELECT sal BULK COLLECT INTO sals FROM emp

WHERE ROWNUM <= 100;

-- Retrieve 10% (approximately) of the rows in the table.

SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;

END;

/

--在fetch into中使用bulk collect

DECLARE

TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;

dept_recs DeptRecTab;

CURSOR c1 IS

SELECT deptno, dname, loc FROM dept WHERE deptno > 10;

BEGIN

OPEN c1;

FETCH c1 BULK COLLECT INTO dept_recs;

END;

/

--在returning into中使用bulk collect

CREATE TABLE emp2 AS SELECT * FROM employees;

DECLARE

TYPE NumList IS TABLE OF employees.employee_id%TYPE;

enums NumList;

TYPE NameList IS TABLE OF employees.last_name%TYPE;

names NameList;

BEGIN

DELETE FROM emp2 WHERE department_id = 30

RETURNING employee_id, last_name BULK COLLECT INTO enums, names;

dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');

FOR i IN enums.FIRST .. enums.LAST

LOOP

dbms_output.put_line('Employee #' || enums(i) || ': ' || names(i));

END LOOP;

END;

/

DROP TABLE emp2;

 

 

oracle批量绑定forall bulk collect

oracle批量绑定forall bulk collect http://otn.oracle.com/oramag/oracle...tech_plsql.html

                                                           http://otn.oracle.com/docs/products...colls.htm#23723

关于Bulk Binds中LIMIT的使用,请看TOM的解说

                                                           http://asktom.oracle.com/pls/ask/f?...5918938803188,Y

Oracle10g中对于forall的增强

                                                           http://www.itpub.net/showthread.php?s=&threadid=184794

批量绑定(Bulk binds)可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )提高了性能.

批量绑定(Bulk binds)包括:

       (i) Input collections, use the FORALL statement,用来改善DML(INSERT、UPDATE和DELETE) 操作的性能

       (ii) Output collections, use BULK COLLECT clause,一般用来提高查询(SELECT)的性能

10g开始forall语句可以使用三种方式:

       i in low..up

       i in indices of collection  取得集合元素下标的值

       i in values of collection   取得集合元素的值

       forall语句还可以使用部分集合元素

       sql%bulk_rowcount(i)表示forall语句第i元素所作用的行数

CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));

CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));

CREATE TABLE parts3 (pnum INTEGER, pname VARCHAR2(15));

CREATE TABLE parts4 (pnum INTEGER, pname VARCHAR2(15));

set   serveroutput   on   --把屏幕显示开关置上

DECLARE

     TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;

     TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;

     pnums NumTab;

     pnames NameTab;

     iterations CONSTANT PLS_INTEGER := 50000;

     t1 INTEGER; t2 INTEGER; t3 INTEGER; t4 INTEGER; t5 INTEGER;

     stmt_str varchar2(255);table_name varchar2(255);

BEGIN

     FOR j IN 1..iterations LOOP -- load index-by tables

          pnums(j) := j;

          pnames(j) := 'Part No. ' || TO_CHAR(j);

     END LOOP;

    

     t1 := dbms_utility.get_time;

     FOR i IN 1..iterations LOOP -- use FOR loop

          INSERT INTO parts1 VALUES (pnums(i), pnames(i));

     END LOOP;

    

     t2 := dbms_utility.get_time;

     FORALL i IN 1..iterations -- use FORALL statement

          INSERT INTO parts2 VALUES (pnums(i), pnames(i));

     t3 := dbms_utility.get_time;

     table_name:='parts3';

     stmt_str := 'INSERT INTO ' || table_name || ' values (:num, :pname)';

     FOR i IN 1..iterations LOOP -- use FORALL statement

          EXECUTE IMMEDIATE stmt_str USING pnums(i), pnames(i);

     END LOOP;

     t4 := dbms_utility.get_time;

     table_name:='parts4';

     stmt_str := 'INSERT INTO ' || table_name || ' values (:num, :pname)';

     FORALL i IN 1..iterations-- use FORALL statement

          EXECUTE IMMEDIATE stmt_str USING pnums(i), pnames(i);

     t5 := dbms_utility.get_time;

     dbms_output.put_line('Execution Time (secs)');

     dbms_output.put_line('---------------------');

     dbms_output.put_line('FOR loop: ' || TO_CHAR((t2 - t1)/100));

     dbms_output.put_line('FORALL: ' || TO_CHAR((t3 - t2)/100));

     dbms_output.put_line('FOR loop: ' || TO_CHAR((t4 - t3)/100));

     dbms_output.put_line('FORALL: ' || TO_CHAR((t5 - t4)/100));

    

     COMMIT;

END;

/

DROP TABLE parts1;

DROP TABLE parts2;

DROP TABLE parts3;

DROP TABLE parts4;

/*

bulk collect 语句:

     用于取得批量数据,只适用于select into ,fetch into 及DML语句的返回子句

    

     DECLARE

          TYPE type_emp IS TABLE OF scott.emp%ROWTYPE INDEX BY BINARY_INTEGER;

          tab_emp type_emp;

          TYPE type_ename IS TABLE OF scott.emp.ename%TYPE INDEX BY BINARY_INTEGER;

          tab_ename type_ename;

          CURSOR c IS

               SELECT *

               FROM scott.emp;

     BEGIN

          SELECT * BULK COLLECT

          INTO tab_emp

          FROM scott.emp;

         

          FOR i IN 1 .. tab_emp.COUNT LOOP

               dbms_output.put_line(tab_emp(i).ename);

          END LOOP;

          dbms_output.new_line;

          DELETE scott.emp RETURNING ename BULK COLLECT INTO tab_ename;

          FOR i IN 1 .. tab_emp.COUNT LOOP

               dbms_output.put_line(tab_emp(i).ename);

          END LOOP;

         

          ROLLBACK;

         

          OPEN c;

          FETCH c BULK COLLECT

               INTO tab_emp;

               dbms_output.new_line;

               FOR i IN 1 .. tab_emp.COUNT LOOP

                    dbms_output.put_line(tab_emp(i).sal);

               END LOOP;

          END;

     */

使用Bulk Collect提高Oracle查询效率

Oracle8i中首次引入了Bulk Collect特性,该特性可以让我们在PL/SQL中能使用批查询,批查询在某些情况下能显著提高查询效率。现在,我们对该特性进行一些简单的测试和分析。

1. 首先,我们创建一个表,并插入100000条记录

在SQL/Plus中执行下列脚本:

drop table empl_tbl

/

create table empl_tbl(last_name varchar2(20),

first_name varchar2(10),

salary number(10))

/

begin

for i in 3000..102999 loop

insert into empl_tbl(last_name,first_name,salary) values('carl'||(i-3000),'wu'||(103000-i),i);

end loop;

end;

/

commit

/

select count(*) from empl_tbl;

/

2. 使用三种方法计算表中某一字段含有多少个不重复值

2.1 使用常规的Distinct来实现

SQL> select count(distinct last_name) "Distinct Last Name" from empl_tbl;

Distinct Last Name

------------------

            100000

 

Executed in 0.36 seconds

我们可以看到,常规方法需要0.36秒查出该表中有100000个不重复的Last_name值。

2.2 使用游标来实现

我们执行下面语句来统计Last_name字段的不重复值个数:

declare

  all_rows number(10);

  temp_last_name empl_tbl.last_name%type;

begin

  all_rows:=0;

  temp_last_name:=' ';

  for cur in (select last_name from empl_tbl order by last_name) loop

     

      if cur.last_name!=temp_last_name then

       all_rows:=all_rows+1;

      end if;

      temp_last_name:=cur.last_name;

     

  end loop;

  dbms_output.put_line('all_rows are '||all_rows);

end;

请注意上面代码中的黑体部分使用了一个For Loop游标,为了提高程序可读性,我们没有显示定义游标变量。

执行结果:

all_rows are 100000

PL/SQL procedure successfully completed

Executed in 1.402 seconds

游标需要1.4秒才能查出该表中有100000个不重复的Last_name值,所耗时间是Distinct查询的3倍多。

2.3 使用Bulk Collect批查询来实现

示例代码如下:

declare

  all_rows number(10);

  --首先,定义一个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表集合变量

  temp_last_name empl_tbl.last_name%type;

begin

  all_rows:=0;

  temp_last_name:=' ';

  --使用Bulk Collect批查询来充填集合变量

  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:=all_rows+1;

      end if;

      temp_last_name:=last_name_arr(i);

  end loop;

 dbms_output.put_line('all_rows are '||all_rows);

end;

请注意上面代码中,我们首先定义了一个Index-by表数据类型last_name_tab,然后定义了一个该集合数据类型的变量last_name_arr,最后我们使用Bulk Collect批查询来充填last_name_arr,请注意它的使用语法。

执行结果:

all_rows are 100000

PL/SQL procedure successfully completed

Executed in 0.28 seconds

从上面执行结果,我们可以看到,Bulk Collect批查询只需要0.28秒就能查出该表中有100000个不重复的Last_name值,所耗时间只有游标查询的1/5,同时它比Distinct常规查询的速度也要快。

3. 测试结果分析

为什么会出现上面的结果呢?我们可以使用Oracle的SQL_Trace来分析一下结果。在SQL命令行中,使用alter session set sql_trace=true语句打开Oracle的Trace,然后在命令行中执行上面三种查询并使用TKPROF工具生成Trace报告。

3.1 常规Distinct查询结果分析********************************************************************************select count(distinct last_name)

from

 empl_tbl

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      0.28       0.32        198        425          4           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.28       0.32        198        425          4           1

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 62 

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT GROUP BY

 100000   TABLE ACCESS FULL EMPL_TBL

********************************************************************************

上述查询首先对empl_tbl进行全表扫描,然后分组排序得出结果。SQL解析、执行的时间都可忽略不计,主要时间花在读取数据上,因为当前SQL只是一个查询,没有任何增、删改操作。在数据读取阶段,需要从磁盘读取198个Oracle数据块,一致性读取(query,consistent gets)数据块425块。因为磁盘物理读是非常耗时的,所以该查询执行起来不是特别快。

3.2 游标查询效率分析********************************************************************************

SELECT LAST_NAME

FROM

 EMPL_TBL ORDER BY LAST_NAME

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        0      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch   100001      0.71       0.62        198        425          4      100000

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total   100002      0.71       0.62        198        425          4      100000

Misses in library cache during parse: 0

Optimizer goal: CHOOSE

Parsing user id: 62     (recursive depth: 1)********************************************************************************该方法的主要耗时也是在数据读取阶段,虽然磁盘读取数据块和一致性读取数据块的数目同Distinct SQL查询相等,但是,因为该方法中的游标要循环100001次,所以上面的SQL的读取会发生100001次,总共读出了100000行数据,这就是为什么使用游标需要1.4秒的原因。下面我们看看使用Bulk Collect会发生什么?

3.3 Bulk Collect的查询效率分析********************************************************************************

SELECT LAST_NAME

FROM

 EMPL_TBL

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        0      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      0.08       0.07          0        425          0      100000

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.08       0.07          0        425          0      100000

Misses in library cache during parse: 0

Optimizer goal: CHOOSE

Parsing user id: 62     (recursive depth: 1)

********************************************************************************

虽然这种方法也要读取100000行数据,但是读取操作只发生了1次,并且Bulk Collect语句将所需数据一次性读入内存,所以该方法没有从磁盘读取数据块,所以这种方法比上述两种方法都具有优势,所以执行效率最高。

4. 结论

通过上面的测试和分析,我们可以看到Bulk Collect批查询在某种程度上可以提高查询效率,它首先将所需数据读入内存,然后再统计分析,这样就可以提高查询效率。但是,如果Oracle数据库的内存较小,Shared Pool Size不足以保存Bulk Collect批查询结果,那么该方法需要将Bulk Collect的集合结果保存在磁盘上,在这种情况下,Bulk Collect方法的效率反而不如其他两种方法,有兴趣的读者可以进一步测试。

另外,除了Bulk Collect批查询外,我们还可以使用FORALL语句来实现批插入、删除和更新,这在大批量数据操作时可以显著提高执行效率。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11405836/viewspace-206150/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-03-13

  • 博文量
    3
  • 访问量
    8905