三种批量删除PLSQL写法效率的比对

　　我们有一个重要的旧系统。最近，夜间维护存在一些问题。夜间执行时间未完成5小时。为了不影响业务，DBA手工kill夜间维护过程只能在早上高峰前进行。

　　本夜间维护程序采用PLSQL编写的存储过程，并通过数据库job定期启动执行。我很少使用存储过程。借此机会，我进行了辅导。存储过程中的逻辑相对简单。依次删除多个业务表。每个表的删除逻辑相同。为了便于解释，模拟下一个删除表的逻辑，例如，

　　TBL_CUSS表三个字段，第一个字段是NUMBER类型，第二个字段是VARCHAR2类型，第三个字段是DATE类型，

　　这个存储过程接受一个参数，这意味着删除几天前的数据，删除DELETE语句，根据一个时间字段与该参数进行比较，获得合格的记录集。同时，使用rownum限制每次执行DELETE-COMMIT事务的条数，并循环执行，直到ORA-1403无记录提示退出此逻辑。存储过程中最好的部分是使用批量提交，而不是执行DELETE删除所有记录的COMMIT。如果这样执行，可能会占用大量的UNDO回滚段，然后可能会出现空间不足或ORA-1555的错误。毕竟UNDO记录了SQL语句的逆向。对于DELETE语句，逆向是INSERT，即存储删除的整个记录。

　　有些朋友一眼就能看出这个存储过程的逻辑有一些问题。比如这个批量删除，不使用游标，相当于每次检索tbl_cuss表符合insert_time < trunc(SYSDATE)-:条件记录，每次只能删除rownum限制的条数，如果使用游标，检索只需执行一次，不考虑是否有索引，降低执行语句的次数可以提高性能。

　　针对这个问题，我写了第二个存储过程，

　　接受删除天数的参数，使用游标执行SELECT一次，读取符合insert_time < trunc(SYSDATE)-:1条件所有结果集中记录的rowid信息，在游标时使用BULK批量设置一次性执行的条数限制MAX__ROW_SIZE，删除语句是根据上述游标获得的rowid进行的DELETE。如果你知道rowid，你可以知道它代表了记录的物理位置。通过转换，可以得到存储在文件、块和行上的记录，即可以快速定位记录的物理位置。在RBO模式下，他的成本优先级是最好的，高于索引。继续写第三个存储过程，

　　这与第二个存储过程基本相同。唯一的区别是在第二个存储过程中使用for循环，而在第三个存储过程中使用forall。for循环将执行每个SQL语句，forall将所有SQL批量发送到SQL引擎。当然，可能还有其他写法，比如使用游标，但不使用BULK，根据rowid删除。这种写法执行SQL语句的次数与结果集数据量一致，效率可能不如原始procedure。

　　原则上，BULK的使用比单个语句要少，PLSQL和SQL引擎之间的切换频率也可以减少redo和undo的产生。对于循环中执行的DELETE，适合使用集合并放入forall。

　　本文的实验说明了forall的一些适用场景， javascript:void(0)

　　delete和insert都可以从forall上获得巨大的性能提升。但是对于update来说，opcode没有相关操作，提升应该不会那么明显。

　　接下来，我们将对这三个存储过程进行一些比较实验，并通过一些数据解释各自的应用场景。首先，我们创建了测试数据集，创建了1300万测试数据，

　　一共26天，每天有50万数据，

　　第一个存储过程名称是clear_without_fetch。 第二个存储过程名称是clear_all_fetch。 第三个存储过程名称是clear_fetch。

　　实验有以下几类(以下实施基本采用第二次实施的结果，避免第一次刷缓存) (1) 一次性删除1万条记录，insert_time不是索引，删除两天的数据(即100万)， clear_without_fetch使用010:16.31 clear_all_fetch用0000:40.50 clear_fetch用0000:21.73 clear_fetch胜出，clear_without_fetch最慢，表示TABLEE ACCESS SQL语句在FULL下，一次删除1万条记录，使用游标和BULK效率更高，使用forall效率高于for。

　　(2) 一次性删除5万条记录，insert_time不是索引，删除两天的数据(即100万)， clear_without_fetch用0000:26.98 clear_all_fetch用0000:39.80 clear_fetch用0000:22.24 clear_fetch胜出，但一次性删除5万条记录，TABLE ACCESS 由于FULL下SQL语句的执行次数减少到原来的5倍，效率有所提高，clear_all_fetch基本相同，因为SQL语句的执行次数与结果集一致，即100万次SQL。

　　(3) 一次性删除100万条记录，insert_time不是索引，删除两天的数据(即100万)， clear_without_fetch用0000:21.92 clear_all_fetch使用010:22.00 clear_fetch用0000:25.95 clear_without_fetch胜出，TABLE ACCESS SQL语句在FULL下执行一次，clear_fetch虽然还是批量发送SQL，但是性能优势不是很明显。

　　(4) 一次性删除1万条记录，insert_time是索引，删除两天的数据(即100万)， clear_without_fetch用0000:31.01 clear_all_fetch使用010:09.02 clear_fetch用0000:37.39 clear_without_fetch胜出，与TABLE相比，索引扫描执行效率提高 ACCESS FULL应该更明显。

　　(5) 一次性删除5万条记录，insert_time是索引，删除两天的数据(即100万)， clear_without_fetch用0000:35.35 clear_all_fetch使用010:03.26 clear_fetch用0000:37.40 clear_without_fetch胜出，clear_all_fetch和clear_fetch与1万基本一致。

　　(6) 一次性删除100万条记录，insert_time是索引，删除两天的数据(即100万)， clear_without_fetch用0000:23.33 clear_all_fetch使用010:27.80 clear_fetch用0000:33.68 clear_without与1万和5万相比，_fetch的胜利效率有所提高，我理解SQL执行次数主要从100次(1万次)开始->20次(5万)->1次(100万)。

　　在上述实验中，数据可能不是绝对接近的，这可能与环境因素(如机器配置、数据质量等)有关。因此，一般方向可以参考，不同的实验可能有点不同，但方向应该更接近，毕竟，原则是。

　　以上六个实验，三个存储过程SQL，各自执行的10046trace，从中可以看出一些线索，

　　clear_without_fetch存储过程中所有场景的trace，

　　(1) 一次性删除1万条记录，insert_time不是索引，删除两天的数据(即100万)，

　　由此可见，由于使用了绑定变量，一次分析，由于循环逻辑问题，执行了100+1次。(2) 一次性删除5万条记录，insert_time不是索引，删除两天的数据(即100万)，

　　(3) 一次性删除100万条记录，insert_time不是索引，删除两天的数据(即100万)，

　　elapsed和query可以比较(1)-(3)。(4) 一次性删除1万条记录，insert_time是索引，删除两天的数据(即100万)，

　　(5) 一次性删除5万条记录，insert_time是索引，删除两天的数据(即100万)，

　　(6) 一次性删除100万条记录，insert_time是索引，删除两天的数据(即100万)，

　　与TABLE相比，由于需要维护索引， ACCESS FULL，会有一些消费。clear_all_fetch存储过程中所有场景的trace，

　　(1) 一次性删除1万条记录，insert_time不是索引，删除两天的数据(即100万)，

　　(2) 一次性删除5万条记录，insert_time不是索引，删除两天的数据(即100万)，

　　(3) 一次性删除100万条记录，insert_time不是索引，删除两天的数据(即100万)，

　　无论(1)、(2)、(3)DELETE语句执行100万次，唯一的区别就是SELECT语句和执行次数。(4) 一次性删除1万条记录，insert_time是索引，删除两天的数据(即100万)，

　　(5) 一次性删除5万条记录，insert_time是索引，删除两天的数据(即100万)，

　　(6) 一次性删除100万条记录，insert_time是索引，删除两天的数据(即100万)，

　　与无索引相比，有些索引需要维持索引的消耗。clear_fetch存储过程中所有场景的trace，

　　(SELECT和clear_all_fetch存储过程相似，这里忽略)

　　(1) 一次性删除1万条记录，insert_time不是索引，删除两天的数据(即100万)，

　　(2) 一次性删除5万条记录，insert_time不是索引，删除两天的数据(即100万)，

　　(3) 一次性删除100万条记录，insert_time不是索引，删除两天的数据(即100万)，

　　(4) 一次性删除1万条记录，insert_time是索引，删除两天的数据(即100万)，

　　(5) 一次性删除5万条记录，insert_time是索引，删除两天的数据(即100万)，

　　(6) 一次性删除100万条记录，insert_time是索引，删除两天的数据(即100万)，

　　可以看到clear_fetch和clear_all_fetch唯一的区别是DELETE语句的执行次数。clear_fetch中的执行次数与循环次数相同，表示批量发送，单个DELETE相同，但执行次数不同，影响资源消耗和执行时间。

　　结论可以从实验中得出， (1) SQL使用TABLEEL ACCESS FULL执行计划，如果SQL执行次数较多，BULK+forall的方式效率较高;如果SQL执行次数较少，很可能使用TABLE ACCESS SQL执行FULL计划的效率接近BULK+forall，甚至更好。 (2) INDEXSQL使用INDEX RANGE SCAN的执行计划效率略高于BULK+forall。如果SQL执行次数较少，则使用INDEX RANGE SCAN执行计划的SQL效率更高;BULK+forall的SQL执行次数基本相同。 (3) 无论是否使用索引，BULK+forall的方式优于BULK+for。可以使用索引，使用游标和不使用游标，效率相对接近，从实验来看，不使用游标可能略高，这与使用游标需要一些分析消耗有关，但游标可以带来方便，如方便控制结果集，可以更灵活地编辑逻辑，因为效率相对接近，如果时间在可接受范围内，可以根据实际情况考虑，选择什么方式。无论如何，批量删除大表数据都是主要原则。