如何在PLSQL中更快删除大表大量数据？求优化方案

表结构与数据规模 #

• Table1（3000万行）：

  buck number(10), 
  sname varchar(20),
  ... 
  -- 共20列
  

• Table2（1000万行）：

  sname varchar(20), 
  sdiv varchar(10),
  ... 
  -- 共15列
  

需求 #

删除Table1中满足 table1.sname = table2.sname AND table2.sdiv = 'tre' 的记录，此类目标记录共330万行。

当前实现代码 #

现有PLSQL代码按buck分批次删除（每个buck约9.2万条记录），但运行42分钟仍未完成且数据库卡顿：

declare 
  cursor cur is 
    select distinct t.buck from table1 t, table2 s 
    where t.sname = s.sname and s.sdiv = 'tre'; 
begin 
  for i in cur 
  loop 
    delete table1 t 
    where t.buck = i.buck and t.sname in (select s.sname from table2 s where s.sdiv = 'tre'); 
    dbms_output.put_line(i.buck || ' buck records deleted'); 
  end loop; 
end; 
/

优化方案 #

1. 优化索引，降低查询开销 #

原代码的关联查询和删除子查询频繁扫描无索引字段，需给关键字段创建复合索引：

• 给Table2创建复合索引：CREATE INDEX idx_t2_sdiv_sname ON table2(sdiv, sname);
  过滤sdiv='tre'并匹配sname时可直接定位，避免全表扫描。
• 给Table1创建复合索引：CREATE INDEX idx_t1_buck_sname ON table1(buck, sname);
  按buck分批次删除时，能快速定位当前批次下符合条件的记录。

2. 缓存待匹配的sname集合，避免重复查询Table2 #

原代码每次循环都重新查询Table2的sname，可提前一次性缓存到集合中：

declare 
  type sname_list is table of varchar2(20);
  v_snames sname_list;
  cursor cur is select distinct buck from table1 t where exists (
    select 1 from table2 s where t.sname = s.sname and s.sdiv = 'tre'
  );
begin 
  -- 提前获取所有需匹配的sname
  select distinct s.sname bulk collect into v_snames 
  from table2 s where s.sdiv = 'tre';

  for i in cur 
  loop 
    delete table1 t 
    where t.buck = i.buck and t.sname member of v_snames;
    commit; -- 每批次提交，减少undo日志压力
    dbms_output.put_line(i.buck || ' buck records deleted'); 
  end loop; 
end; 
/

3. 增大单次批量删除规模，减少循环次数 #

若数据库资源允许，可跳过buck分组，直接按固定行数批量删除：

declare
  type sname_list is table of varchar2(20);
  v_snames sname_list;
  v_deleted_count number := 0;
begin
  select distinct s.sname bulk collect into v_snames from table2 s where s.sdiv = 'tre';
  
  loop
    delete from table1 t
    where t.sname member of v_snames
    and rownum <= 100000; -- 每次删10万条
    
    v_deleted_count := sql%rowcount;
    dbms_output.put_line(v_deleted_count || ' records deleted');
    commit;
    
    exit when v_deleted_count = 0;
  end loop;
end;
/

4. 用临时表预存待删除记录标识 #

若Table1的buck+sname可唯一标识记录，先把待删除的标识存入临时表，再分批次删除：

-- 创建临时表
create global temporary table tmp_del_ids (
  buck number(10),
  sname varchar(20)
) on commit preserve rows;

-- 插入待删除标识
insert into tmp_del_ids
select t.buck, t.sname
from table1 t
join table2 s on t.sname = s.sname
where s.sdiv = 'tre';

-- 分批次删除
declare
  v_batch_size number := 100000;
begin
  loop
    delete table1 t
    where (t.buck, t.sname) in (
      select buck, sname from tmp_del_ids where rownum <= v_batch_size
    );
    
    commit;
    exit when sql%rowcount = 0;
  end loop;
end;
/

5. 开启并行删除（Oracle 12c+适用） #

在业务低峰期，可利用并行操作加速删除：

delete /*+ parallel(8) */ table1 t
where exists (
  select 1 from table2 s where t.sname = s.sname and s.sdiv = 'tre'
);
commit;

注：并行操作会占用更多资源，需确认数据库负载允许。

6. 采用"新表插入+替换原表"方案（超大规模数据场景） #

若删除记录占比不高，这种方式远快于直接删除：

1. 创建与Table1结构完全一致的新表table1_new；
2. 插入无需删除的记录：

   insert /*+ parallel(8) */ into table1_new
   select * from table1 t
   where not exists (
     select 1 from table2 s where t.sname = s.sname and s.sdiv = 'tre'
   );
   

3. 重命名原表，将新表改为原表名；
4. 删除原表完成替换。

内容的提问来源于stack exchange，提问作者Dark3963