本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。

1. 背景描述

1.1. 任务描述

这个任务是需要从一系列大表中清理 3 个省的大批过时数据，具体的清理过程简单的说就是：

首先根据不同的 miscid 的值创建不同的临时表，类似于：

CREATE TABLE temp_mid

SELECT mid FROM ssr WHERE SUBSTR(ssid,1,7) IN

(SELECT prefixnum FROM prefix WHERE mcid='0012');

然后通过这个临时表连接另一个大表，做删除工作：

DELETE SSF

WHERE mid IN (SELECT mid

FROM TEMP_MID_HUBEI);

上述任务根据不同的关键字，需要执行几十次，如果不加任何优化的话，每一次都需要执行几十个小时。由于需求、操作和优化思路大体相同，下面我们就以上面的例子详细说说实际应用中如何一步步优化提速到近千倍的过程。

1.2. 数量级统计和描述

首先统计这个操作涉及到的几张表：

SELECT COUNT(*) FROM PREFIX;

SELECT COUNT(*) SSR FROM SSR;

SELECT COUNT(*) SSF FROM SSF;

SELECT COUNT(*) AS SSF_0012 FROM SSF WHERE MID IN (SELECT MID FROM TEMP_MID_HUBEI);

上述脚本的执行过程如下（请注意，由于创建临时表 TEMP_MID_HUBEI 的过程比较简单，因此这里没有赘述，仅仅是从建立临时表后的删除操作开始分析的）：

SQL> @LUNAR.SQL

PREFIX

----------

51854

ELAPSED: 00:00:00.14

SSF

-----------

** 83446270

ELAPSED: 00:04:53.27

SSR

----------

43466645

ELAPSED: 00:03:08.00

SSF_0012

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

** 131098

ELAPSED: 00:00:57.02

SQL>

我们注意到，这里面，我们需要做的是从一个 8300 多万行的大表中，通过和一个 130 多万行的表进行连接并删除其中的大部分数据。整个操作的过程，要求所有的表都可以实时访问，并且除了我们手工建立的临时表（ TEMP_MID_HUBEI ）以外，其他的表都可以实时访问和修改。

2. 背景知识—— Bulk Binding

在下面的优化过程中，我们使用了批量绑定（ Bulk Binding ）的思想，因此首先对这一知识作些解释。

2.1. 什么是 Bulk Binding ？

在 sql 语句中（动态地）给 PL/SQL 变量赋值叫做绑定（ Binding ）。一次绑定一个完整的集合叫做批量绑定（ Bulk Binding ）。

从 Oracle 8i 开始，在 PL/SQL 可以使用两个新的数据操纵语言（ DML ）语句： BULK COLLECT 和 FORALL 。这两个语句在 PL/SQL 内部按数组进行数据处理。

2.2. Bulk binds 的优点是什么呢？

批量绑定（ Bulk binds ）通过最小化在 PL/SQL 和 SQL 引擎之间的上下文切换提高了性能，它以一个完整的集合（如， varray, nested tables, index-by table, or host array ）为单位（一批一批的）向前或者向后绑定变量。在 Oracle 8i 以前，每个 SQL 语句的执行需要在 PL/SQL 和 SQL 引擎之前切换上下文，使用绑定变量后，就只需要一次上下文切换。

其中， BULK COLLECT 提供对数据的高速检索， FORALL 可大大改进 INSERT 、 UPDATE 和 DELETE 操作的性能。

2.3. 如何进行批量绑定（ Bulk Binds ）？

绑定变量包扩下面两个部分：

输入集合（ collections ），使用 FORALL 语句，一般用来改善 DML （ INSERT 、 UPDATE 和 DELETE) 操作的性能；
输出集合（ collections ），使用 BULK COLLECT 子句；一般用来提高查询（ SELECT ）的性能。

2.3.1. 输入集合 (FORALL)

输入集合是数据通过 PL/SQL 引擎到 SQL 引擎去执行 INSERT, UPDATE, DELETE 语句。输入集合使用 FORALL 语句，下面是 FORALL 的语法：

** FORALL index IN lower_bound..upper_bound

** sql_statement; ** **

2.3.2. FOR.. LOOP 语句和 FORALL 的比较

例 1 （ example1 ）：分别使用传统的 FOR .. LOOP 操作和我们这里介绍的 FORALL 操作向 lunartest 表中加载 1000000 条记录，对比一下他们的执行效率。

测试过程如下：

首先创建一个用来记录操作时间的存储过程 get_time ：

CREATE OR REPLACE PROCEDURE get_time (t OUT NUMBER)

BEGIN

SELECT TO_CHAR (SYSDATE, 'SSSSS')

INTO t

FROM DUAL;

END;

然后创建一个空表，分别使用 FOR .. LOOP 和 FORALL .. LOOP 插入数据，并记录和输出操作时间：

SQL> conn lunar/lunar

Connected.

SQL> SET SERVEROUTPUT ON

SQL> CREATE TABLE lunartest (pnum NUMBER(20), pname varchar2(50));

Table created.

Elapsed: 00:00:00.00

SQL> Create Or Replace PROCEDURE BulkTest IS

2 TYPE NumTab IS TABLE OF NUMBER(20) INDEX BY BINARY_INTEGER;

3 TYPE NameTab IS TABLE OF varchar2(50) INDEX BY BINARY_INTEGER;

4 pnums NumTab;

5 pnames NameTab;

6 t1 CHAR(5);

7 t2 CHAR(5);

8 t3 CHAR(5);

9 BEGIN

**10 FOR j IN 1..1000000 LOOP -- load index-by tables

** 11 pnums(j) := j;

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

** 13 END LOOP ;

15 get_time(t1);

17 FOR i IN 1..1000000 LOOP -- use FOR loop

18 INSERT INTO lunartest VALUES (pnums(i), pnames(i));

19 END LOOP ;

21 get_time(t2);

**23 FORALL i IN 1..1000000 --use FORALL statement

** 24 INSERT INTO lunartest VALUES (pnums(i), pnames(i));

25 get_time(t3);

27 DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');

28 DBMS_OUTPUT.PUT_LINE('---------------------');

29 DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR(t2 - t1));

30 DBMS_OUTPUT.PUT_LINE('FORALL: ' || TO_CHAR(t3 - t2));

31 END;

32 /

Procedure created.

Elapsed: 00:00:00.00

** SQL> exec BulkTest;

** Execution Time (secs)

** ---------------------

** FOR loop: 110

** FORALL: 54

** PL/SQL procedure successfully completed.

** Elapsed: 00:03:24.07

** SQL> ** select sum(bytes/1024/1024) MB from user_segments where segment_name='LUNARTEST';

----------

Elapsed: 00:00:11.06

SQL>

这里我们注意到使用 FOR .. LOOP 语句插入 1000000 条记录，需要 110 秒；而使用 FORALL 语句只需要 54 秒。

2.3.3. 如何处理回滚？

对于回滚的处理， FORALL 操作可以自动完成，也就是说，如果一个 FORALL 语句执行失败，那么 Oracle 会基于隐式的 SAVE POINT 一次回滚 SQL 语句中先前执行的部分。

2.3.4. 输出集合 s

输出集合是数据作为一个通过 SQL 引擎到 PL/SQL 引擎的（ SELECT 或者 FETCH 的）结果集。

输出集合通过在 SELECT INTO, FETCHINTO 和 RETURNING INTO 子句中加入 BULK COLLECT 子句实现，下面是 BULK COLLECT 子句的语法：

<table class="MsoNormalTable" style="MARGIN: a

如何给Large Delete操作提速近千倍？(一)

本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。

1. 背景描述

1.1. 任务描述

1.2. 数量级统计和描述

2. 背景知识—— Bulk Binding

2.1. 什么是 Bulk Binding ？

2.2. Bulk binds 的优点是什么呢？

2.3. 如何进行批量绑定（ Bulk Binds ）？

2.3.1. 输入集合 (FORALL)

2.3.2. FOR.. LOOP 语句和 FORALL 的比较

2.3.3. 如何处理回滚？

2.3.4. 输出集合 s