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

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

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

1.   优化过程详解

1.1. 第一次优化——处理庞大的IN-LIST操作

回到我们最初的需求，首先看看这个删除工作的基本语句，请注意这个IN 操作，由于这个TEMP_MID_HUBEI 表有130多万行记录，这将是一个多么庞大的IN-LIST!

 

 

DELETE from SSF       WHERE mid IN (SELECT mid                       FROM TEMP_MID_HUBEI);

 

 

下面，我们在测试环境下看看，系统机器空闲，资源极大丰富，数量比真实环境小很多的情况下，这条删除语句的执行效率：

SQL> DELETE from SSF WHERE mid IN (SELECT mid FROM TEMP_MID_HUBEI);     18 rows deleted.     Elapsed: 00:05:35.79 SQL>rollback;

我你们看到原始的删除语句需要大约5分半钟。

 

 

现在，我们使用RBO（加上 RULE HINT），观察一下语句的执行情况：

SQL> DELETE /*+ RULE */ from SSF WHERE mid IN (SELECT mid FROM TEMP_MID_HUBEI);     18 rows deleted.     Elapsed: 00:00:47.27 SQL>rollback;

 

 

我们看到，这条语句的执行时间缩短到只需要47秒钟，单条语句的执行效率提高了7倍以上，这也就是我们常说的，对付IN-LIST或者大量OR条件的一大有利武器。

 

 

好了，我们现在如果在生产库上直接运行修改后的delete语句来删除数据性能会提高多少呢？会是我们看到的7倍么？

我将删除操作所涉及到的所有表的操作写成一个脚本，首先使用少量行删除的操作做测试，得到如下的测试结果：

oracle@db02:/oracle/lunar > cat nohup.out     SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 7 12:37:56 2005     Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.         Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning and Real Application Clusters options JServer Release 9.2.0.4.0 - Production         37 rows deleted.                 henan_SUBSO     Elapsed: 00:06:39.81     Commit complete.     Elapsed: 00:00:00.01     4 rows deleted.                      hubei_SUB     Elapsed: 00:02:42.71     Commit complete.     Elapsed: 00:00:00.01     1 row deleted.                       hubei_SUBSC     Elapsed: 01:31:51.25     Commit complete.     Elapsed: 00:00:00.01     18727 rows deleted.              hubei_SUBNFO     Elapsed: 00:01:35.55     Commit complete.     Elapsed: 00:00:00.01     21 rows deleted.                 fujian_SUINFO     Elapsed: 00:00:15.49     Commit complete.     Elapsed: 00:00:00.01     35423 rows deleted.              GINFO     Elapsed: 00:02:19.80     Commit complete.     Elapsed: 00:00:00.04 Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning and Real Application Clusters options JServer Release 9.2.0.4.0 - Production oracle@db02:/oracle/lunar >

 

 

嗯，看到上面可喜的结果，我决定进行第二次优化，分段处理，也就是将所有超过10000条以上的删除操作拆分成小段操作（比如，以10000条记录为标准删除记录，然后提交）。