如何编写高效Oracle SQL按MACHINE_ID保留最新3条记录删除旧数据

假设你的业务表名为machine_data，实际使用时请替换为你的真实表名。

仅查询需保留的记录（不修改数据） #

如果你只需要查询得到每个MACHINE_ID最新3条、且USERFILE不为0的记录，可直接使用窗口函数实现：

SELECT *
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER(PARTITION BY MACHINE_ID ORDER BY TIMESTAMP DESC) AS rn
    FROM machine_data t
    WHERE t.USERFILE != 0 -- 过滤掉USERFILE=0的记录
)
WHERE rn <= 3;

物理删除冗余记录（最高效实现） #

如果需要直接删除表中不符合要求的旧记录，推荐基于ROWID实现，Oracle中ROWID是记录的物理地址，定位速度最快：

DELETE FROM machine_data
WHERE ROWID IN (
    SELECT rid
    FROM (
        SELECT 
            ROWID AS rid,
            ROW_NUMBER() OVER(PARTITION BY MACHINE_ID ORDER BY TIMESTAMP DESC) AS rn
        FROM machine_data
        WHERE USERFILE != 0 -- 仅处理USERFILE不为0的记录，USERFILE=0的记录不纳入本次删除逻辑
    )
    WHERE rn > 3
);
COMMIT; -- 确认删除后提交事务，可根据需要调整提交时机

注：如果需求要求USERFILE=0的记录也一并删除，可在DELETE的WHERE条件后补充OR USERFILE = 0即可。

性能优化建议 #

• 建议创建联合索引 INDEX idx_machine_time_userfile(MACHINE_ID, TIMESTAMP DESC, USERFILE)，可避免全表扫描和排序操作，性能提升效果非常明显
• 若表数据量超过百万级，建议分批执行删除，避免长时间锁表和UNDO空间占用过高，分批示例如下：

BEGIN
    LOOP
        DELETE FROM machine_data
        WHERE ROWID IN (
            SELECT rid
            FROM (
                SELECT 
                    ROWID AS rid,
                    ROW_NUMBER() OVER(PARTITION BY MACHINE_ID ORDER BY TIMESTAMP DESC) AS rn
                FROM machine_data
                WHERE USERFILE != 0
            )
            WHERE rn > 3
        ) AND ROWNUM <= 1000; -- 每次删除1000条，可根据实际情况调整批次大小
        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;
    END LOOP;
    COMMIT;
END;
/

• 如果是定期清理的业务场景，可将表设置为按TIMESTAMP字段分区的分区表，清理旧数据时直接DROP对应分区，效率比DELETE高数十倍。

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