Oracle 19c中无需指定所有列即可定位表中完全重复行的方法

我太懂这种手动敲一堆列名的痛苦了——尤其是表有几十列的时候，不仅容易漏写，后期列有变更还得改查询，简直是重复劳动！好在Oracle 19c里有几个实用的小技巧，能让你不用逐个列名去写，轻松定位完全重复的行。

方法一：利用SYS_OP_HASH_ALL函数（性能优先） #

Oracle的SYS_OP_HASH_ALL函数可以快速计算整行所有列的哈希值，完全重复的行哈希值肯定一致。我们可以基于这个哈希值来分组，就能避开写所有列名的麻烦：

方式1：关联子查询获取重复行 #

-- 先找出所有重复行的哈希值，再关联原表获取完整数据
SELECT t.*
FROM your_table t
JOIN (
    SELECT SYS_OP_HASH_ALL(t_inner.*) AS row_hash
    FROM your_table t_inner
    WHERE col3 IN ('x','y')
    GROUP BY SYS_OP_HASH_ALL(t_inner.*)
    HAVING COUNT(*) > 1
) dup_hashes ON SYS_OP_HASH_ALL(t.*) = dup_hashes.row_hash
WHERE t.col3 IN ('x','y');

方式2：窗口函数标记重复行（推荐用于后续删除） #

更简洁的写法是用窗口函数直接给重复行打标记，这样不仅能定位重复行，还能方便后续通过rowid删除副本：

SELECT *
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY SYS_OP_HASH_ALL(t.*) 
            ORDER BY t.rowid
        ) AS duplicate_rank
    FROM your_table t
    WHERE col3 IN ('x','y')
) ranked_rows
WHERE duplicate_rank > 1;

这里PARTITION BY SYS_OP_HASH_ALL(t.*)会把所有完全重复的行分到同一个分组，duplicate_rank大于1的就是需要处理的重复副本，完美匹配你后续删除的需求。

方法二：利用XML序列化（兼容性优先） #

如果你担心内部函数的版本兼容性（虽然19c里SYS_OP_HASH_ALL完全稳定），还可以把整行转换成XML格式，再基于这个XML分组。虽然性能比哈希函数稍差，但胜在逻辑直观，不用记Oracle专属的内部函数：

SELECT t.*
FROM your_table t
JOIN (
    SELECT XMLSERIALIZE(CONTENT XMLTYPE(t_inner.*) AS VARCHAR2(4000)) AS row_xml
    FROM your_table t_inner
    WHERE col3 IN ('x','y')
    GROUP BY XMLSERIALIZE(CONTENT XMLTYPE(t_inner.*) AS VARCHAR2(4000))
    HAVING COUNT(*) > 1
) dup_xmls ON XMLSERIALIZE(CONTENT XMLTYPE(t.*) AS VARCHAR2(4000)) = dup_xmls.row_xml
WHERE t.col3 IN ('x','y');

小提示 #

• 优先选SYS_OP_HASH_ALL的方案，哈希计算的性能比XML转换高出不少，大表场景下差异更明显。
• 后续删除重复行时，直接用窗口函数的结果集，删除duplicate_rank > 1的行即可，全程不用改动列名，列再多也不怕。

内容来源于stack exchange