Oracle 19c识别删除审计表指定字段连续重复冗余行方案

使用LAG函数出现跨widgetID取值问题，本质是调用分析函数时未指定分区规则，只要给LAG加上PARTITION BY widgetID分区子句，就能把计算范围严格限定在同一个widget的记录组内，单条SQL即可完成全量冗余识别，不需要逐行循环遍历，千万级数据量下也有稳定性能。

操作步骤 #

1. 先校验待删除数据，避免误操作 #

先执行查询语句核对所有待删除的冗余行，确认符合预期后再做删数操作：

WITH audit_prev_compare AS (
    SELECT
        dataRowID,
        widgetID,
        widgetDesc,
        timestampOfTableUpdate,
        -- 同widgetID内按更新时间升序，取上一条记录的widgetDesc值
        LAG(widgetDesc) OVER(
            PARTITION BY widgetID
            ORDER BY timestampOfTableUpdate, dataRowID
        ) AS last_widget_desc
    FROM auditWidget
)
-- 与上一条记录描述完全一致的即为冗余行
SELECT * FROM audit_prev_compare
WHERE widgetDesc = last_widget_desc;

排序字段追加dataRowID是为了避免同一widgetID下存在相同时间戳的记录时，排序顺序不稳定导致判断错误。
用提供的样例数据执行上述查询，返回的待删除行是dataRowID=3、4、5，完全匹配冗余判定规则，剩余记录均为每组连续相同值的首条有效记录。

2. 执行冗余数据清理 #

核对查询结果无误后，执行删除语句即可：

DELETE FROM auditWidget
WHERE dataRowID IN (
    WITH audit_prev_compare AS (
        SELECT
            dataRowID,
            widgetDesc,
            LAG(widgetDesc) OVER(
                PARTITION BY widgetID
                ORDER BY timestampOfTableUpdate, dataRowID
            ) AS last_widget_desc
        FROM auditWidget
    )
    SELECT dataRowID FROM audit_prev_compare
    WHERE widgetDesc = last_widget_desc
);

实操注意事项 #

• 如果表存量数据超过千万级，建议按widgetID分批次删除，每次删除1万~10万行就提交一次，避免长事务持锁影响线上业务
• 删除完成后建议执行表空间回收操作（如ALTER TABLE auditWidget SHRINK SPACE），真正释放磁盘空间，降低全表扫描开销
• 后续建议在业务低峰期修改触发器逻辑：插入审计记录前先查询同widgetID最新一条审计记录的字段值，与当前变更值完全一致则跳过插入，从根源避免冗余数据生成

附测试用表及样例数据初始化脚本：

CREATE TABLE AUDITWIDGET
(
"DATAROWID" NUMBER(*,0),
"WIDGETID" NUMBER(*,0),
"WIDGETDESC" VARCHAR2(30),
"TIMESTAMPOFTABLEUPDATE" DATE);

Insert into AUDITWIDGET (datarowID,widgetID,widgetDesc,timeStampOfTableUpdate) values (1,1,'A',sysdate - interval '100' minute);
Insert into AUDITWIDGET (datarowID,widgetID,widgetDesc,timeStampOfTableUpdate) values (2,2,'test',sysdate - interval '99' minute);
Insert into AUDITWIDGET (datarowID,widgetID,widgetDesc,timeStampOfTableUpdate) values (3,1,'A',sysdate - interval '98' minute);
Insert into AUDITWIDGET (datarowID,widgetID,widgetDesc,timeStampOfTableUpdate) values (4,1,'A',sysdate - interval '97' minute);
Insert into AUDITWIDGET (datarowID,widgetID,widgetDesc,timeStampOfTableUpdate) values (5,2,'test',sysdate - interval '96' minute);
Insert into AUDITWIDGET (datarowID,widgetID,widgetDesc,timeStampOfTableUpdate) values (6,1,'c',sysdate - interval '95' minute);
Insert into AUDITWIDGET (datarowID,widgetID,widgetDesc,timeStampOfTableUpdate) values (7,1,'b',sysdate - interval '94' minute);
Insert into AUDITWIDGET (datarowID,widgetID,widgetDesc,timeStampOfTableUpdate) values (8,1,'d',sysdate - interval '93' minute);
Insert into AUDITWIDGET (datarowID,widgetID,widgetDesc,timeStampOfTableUpdate) values (9,1,'A',sysdate - interval '92' minute);

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