一、前言

WHERE条件用不到索引的情况下如何删除大表记录?文章中只是列举出其中一种方式而已,但仍然存在很多不严谨的地方.只是

轻描淡写随着数据表越来越大,历史数据的处理将越来越困难.且过滤字段没有索引(如时间)字段,而创建索引是需要临时表空间排序的.有越来越大,创建的维护工作也随之越来越难.

除了本文介绍的方法以外,还可以通过以下几种方法来维护历史数据:

1. 创建历史表,将历史数据定期移至历史表,让源表保持"瘦小身材"(源表会有碎片,需要定期对表和索引进行重建)

2. ORACLE系统包DBMS_REDEFINITION实现表的在线重定义

3. 把源表做分区处理,历史数据的维护可以转化成表/索引分区的维护.

?

本文将对以下几种方法做比较:

1. 普通删除语句(delete from tabname where condition1...condition2...)

2. 通过存储过程(每次提取一条)

3. 通过存储过程(批量提取)

?

以一张近500M的表作为实验对象,该表没有任何索引.总条数400多万:

SQL> select bytes/1024/1024 from user_segments where segment_name='ROBO';

BYTES/1024/1024
---------------
472 ---

SQL> select count(*) from robo;

COUNT(*)
----------
4154240 ----约400万条数据

SQL> select count(*) from robo where owner='TEST';

COUNT(*)
----------
4992 ---近5000条数据

SQL> select count(*) from robo where owner='SYS';

COUNT(*)
----------
1909760 ---近200万条数据

二、实验过程

1）假设待删除数据量小:

1. 普通删除的方法:

SQL> delete from robo where owner='TEST';
delete from robo
where
owner='TEST'

性能数据:

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

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 1 1 0 0
Execute 1 0.65 0.64 59842 60092 5280 4992
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.65 0.64 59843 60093 5280 4992

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 52

Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE ROBO (cr=60092 pr=59842 pw=0 time=642682 us)
4992 TABLE ACCESS FULL ROBO (cr=60092 pr=59840 pw=0 time=485683 us)

只要全表扫描一次，删除效率很快

?

2. 游标处理（每次提取一条）

declare
v_rowid varchar2(25);
v_sqltext varchar2(200);
cursor c1 is select rowid from robo where owner='TEST';
begin
v_sqltext := 'delete from robo where rowid=:1';
open c1;
loop
fetch c1 into v_rowid;
execute immediate v_sqltext using v_rowid;
exit when c1%notfound;
end loop;
dbms_output.put_line(c1%rowcount||' row deleted.');
close c1;
end;
/

4992 row deleted.

PL/SQL procedure successfully completed.

?

性能数据:

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

delete from robo
where
rowid=:1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 4993 0.33 0.31 1 4993 5280 4992
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4994 0.33 0.31 1 4993 5280 4992

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 52 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.00 0.00
********************************************************************************

declare
v_rowid varchar2(25);
v_sqltext varchar2(200);
cursor c1 is select rowid from robo where owner='TEST';
begin
v_sqltext := 'delete from robo where rowid=:1';
open c1;
loop
fetch c1 into v_rowid;
execute immediate v_sqltext using v_rowid;
exit when c1%notfound;
end loop;
dbms_output.put_line(c1%rowcount||' row deleted.');
close c1;
end;