如何借助索引加速Oracle超大表（2亿行）的重复数据删除

原SQL执行缓慢的核心原因：全表扫描2亿行数据，且对20列做GROUP BY的计算开销极大；同时NOT IN语法在Oracle中优化效率较低，尤其面对大数据集时，会导致大量不必要的关联操作。以下是几种可行的加速方案：

一、创建组合覆盖索引 #

针对用于判定重复的20列创建组合索引，让子查询可以直接通过索引完成分组和ROWID提取，无需回表查询：

CREATE INDEX idx_table1_dup ON sch.table1(col1, col2, col3, ..., col20);

Oracle的索引默认包含ROWID，因此该索引可以直接支持子查询中的MIN(rowid)计算，避免全表扫描，大幅提升子查询的执行速度。

二、替换低效的NOT IN为NOT EXISTS #

NOT EXISTS在Oracle中的执行计划优化更高效，且不会因字段含NULL值出现逻辑异常，改写后的SQL如下：

DELETE sch.table1 t1
 WHERE EXISTS (
    SELECT 1
      FROM sch.table1 t2
     WHERE t2.col1 = t1.col1
       AND t2.col2 = t1.col2
       -- 依次添加剩余18列的等值判断
       AND t2.col20 = t1.col20
       AND t2.rowid < t1.rowid
)

该语句逻辑为：保留每组重复数据中ROWID最小的条目，删除其余重复行，配合上述组合索引可进一步提升执行效率。

三、分批删除避免锁表与日志溢出 #

直接删除大量数据会占用大量UNDO日志，导致锁表时间过长，影响业务。可通过PL/SQL循环分批删除，每次提交少量数据：

DECLARE
  v_del_count NUMBER;
BEGIN
  LOOP
    DELETE sch.table1 t1
     WHERE EXISTS (
        SELECT 1
          FROM sch.table1 t2
         WHERE t2.col1 = t1.col1
           AND t2.col2 = t1.col2
           -- 依次添加剩余18列的等值判断
           AND t2.col20 = t1.col20
           AND t2.rowid < t1.rowid
     ) AND ROWNUM <= 10000; -- 每次删除1万条，可根据服务器性能调整

    v_del_count := SQL%ROWCOUNT;
    COMMIT;
    EXIT WHEN v_del_count = 0;
  END LOOP;
END;
/

此方法适合生产环境低峰期操作，减少对业务的影响。

四、CTAS+表切换（最快方案） #

若允许短时间内将表置为只读或停写，创建新表替换原表是效率最高的方式，避免删除操作的UNDO/REDO开销：

-- 1. 创建无重复数据的新表
CREATE TABLE sch.table1_new AS
SELECT * FROM sch.table1
 WHERE rowid IN (
    SELECT MIN(rowid)
      FROM sch.table1
     GROUP BY col1, col2, ..., col20
);

-- 2. 重建新表的索引、约束、触发器等（需与原表一致）
ALTER TABLE sch.table1_new ADD PRIMARY KEY (your_pk_col);
CREATE INDEX idx_table1_new_colx ON sch.table1_new(colx);
-- 其他约束/触发器按需重建

-- 3. 切换表（需确保无业务写入）
RENAME sch.table1 TO sch.table1_old;
RENAME sch.table1_new TO sch.table1;

-- 4. 验证数据无误后删除旧表
DROP TABLE sch.table1_old;

该方法的执行速度远快于直接删除，适合数据量极大的场景。

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