Bulk绑定是如何提高性能的。有空再翻译吧。(from oracle)

转载 2006年06月02日 16:43:00

How Do Bulk Binds Improve Performance?

The assigning of values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into three categories:

  • in-bind When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.
  • out-bind When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.
  • define When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.

A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding. If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth.

To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement.

To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.

For full details of the syntax and restrictions for these statements, see "FORALL Statement" and "SELECT INTO Statement".

Example: Performing a Bulk Bind with DELETE

The following DELETE statement is sent to the SQL engine just once, even though it performs three DELETE operations:

DECLARE
   TYPE NumList IS VARRAY(20) OF NUMBER;
   depts NumList := NumList(10, 30, 70);  -- department numbers
BEGIN
   FORALL i IN depts.FIRST..depts.LAST
      DELETE FROM emp WHERE deptno = depts(i);
END;

Example: Performing a Bulk Bind with INSERT

In the example below, 5000 part numbers and names are loaded into index-by tables. All table elements are inserted into a database table twice: first using a FOR loop, then using a FORALL statement. The FORALL version is much faster.

SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));

Table created.

SQL> GET test.sql
 1  DECLARE
 2     TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
 3     TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
 4     pnums  NumTab;
 5     pnames NameTab;
 6     t1 NUMBER(5);
 7     t2 NUMBER(5);
 8     t3 NUMBER(5);
 9     
10     
11  BEGIN
12     FOR j IN 1..5000 LOOP  -- load index-by tables
13        pnums(j) := j;
14        pnames(j) := 'Part No. ' || TO_CHAR(j); 
15     END LOOP;
16     t1 := dbms_utility.get_time;
17     FOR i IN 1..5000 LOOP  -- use FOR loop
18        INSERT INTO parts VALUES (pnums(i), pnames(i));
19     END LOOP;
20     t2 := dbms_utility.get_time;
21     FORALL i IN 1..5000  -- use FORALL statement
22        INSERT INTO parts VALUES (pnums(i), pnames(i));
23     get_time(t3);
24     dbms_output.put_line('Execution Time (secs)');
25     dbms_output.put_line('---------------------');
26     dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1));
27     dbms_output.put_line('FORALL:   ' || TO_CHAR(t3 - t2));
28* END;
SQL> /
Execution Time (secs)
---------------------
FOR loop: 32
FORALL:   3

PL/SQL procedure successfully completed.

相关文章推荐

ORACLE批量绑定FORALL与BULK COLLECT

 FORALL与BULK COLLECT的使用方法: 1.使用FORALL比FOR效率高,因为前者只切换一次上下文,而后者将是在循环次数一样多个上下文间切换。 2.使用BLUK COLLECT一次取出...
  • quanhj
  • quanhj
  • 2011年01月26日 15:37
  • 439

Oracle 批绑定(batch bind)FORALL, BULK COLLECT

FORALL 用法小结:    本文主要翻译、整理了ORACLE官方文档上有关FORALL的部份内容,不妥之处,还希望多和大家交流。  在发送语句到SQL引擎前,FORALL语句告知PL/SQL ...

Oracle PL/SQL开发基础(第二十弹:批量绑定和BULK COLLECT)

批量绑定在编写PL/SQL代码时,PL/SQL引擎通常会与SQL引擎进行交互,比如将SQL语句发送到PL/SQL引擎,SQL引擎在执行了SQL语句后,会返回一些数据给PL/SQL引擎。频繁的交互会大大...

Oracle Bulk 与 性能优化 说明

一. Bulk 概述        本来只想测试一下Bulk Collect 和update性能的,但发现Bulk 的东西还是很多的,在OTN上搜了一些,整理如下。   1.1 Bulk Bin...

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

当运行一个pl/sql程序时, pl/sql语句引擎会执行pl/sql语句。但如果在这个过程中引擎遇到sql语句,它会把这个语句传给sql引擎(后台发生上下文切换)。   在PL/SQL 和SQL引擎...
  • zj0078
  • zj0078
  • 2013年12月19日 08:47
  • 2989

Oracle数据库SQL语句绑定变量一----性能问题

SQL语句编写时,一般都是这样的: select * from emp where empno=7369; 或者select * from emp where empno=7499; 这两条SQL语句...

FORALL使用--insert/delete/update操作的批绑定Bulk Binding

Forall是Oracle在PL/SQL中提供的一种批量处理语法。它提供了比传统for loop更好的处理性能优势。两者的差异主要体现在处理引擎上下文切换上的性能损耗优势。 在PL/SQL语句中出现语...

oracle批量处理(bulk collect)

  • 2012年03月19日 09:37
  • 54KB
  • 下载

bulk collect limit性能比较

bulk collect limit性能比较 http://blog.sina.com.cn/s/blog_4bda1bf301010z7f.html 当采集大批量数据的时候,内存无法承担直接...
  • orion61
  • orion61
  • 2012年04月15日 10:05
  • 743

Oracle数据库的批量操作,forall,BULK COLLECT

oracle forall FORALL语句的一个关键性改进,它可大大简化代码,并且对于那些要在PL/SQL程序中更新很多行数据的程序来说,它可显著提高其性能。 1: 用FORALL来增强DML...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Bulk绑定是如何提高性能的。有空再翻译吧。(from oracle)
举报原因:
原因补充:

(最多只允许输入30个字)