在大型表中查找重复项 Oracle SQL

Question

我想展示如何在 Oracle 数据库中包含 40 多列的非常大的表中多次出现的行，并最终清理重复项。我知道最简单的方法是使用这样的GROUP BY和HAVING COUNT(*) > 1：

SELECT field1, field2, ...
FROM schema.tablename
GROUP BY field1, field2, ...
HAVING COUNT(*) > 1

但是我想知道是否有更简单的方法在 ORACLE 中列出重复项，因为我需要使用的表有大量字段，因此将它们列在 SELECT 和 GROUP BY 子句中将非常低效且很长。有没有办法像这样列出 SELECT 和 GROUP BY 子句中表中的所有列？

SELECT all_columns(tablename)
FROM schema.tablename
GROUP BY all_columns(tablename)
HAVING COUNT(*) > 1

或者，是否有更好的方法来查找 ORACLE 表中的重复项？感谢您的帮助！

Answer 1

这是接近的东西。注意 - 如果您的表只有 40 列，这将起作用;如果它有 400 列，例如，每列的名称长度平均为 10 个字符，则可能无法正常工作。(瓶颈是整个字符串的 4000 个字符限制，尤其是对listagg的限制;显然这在您的情况下不是问题，所以我不会讨论解决方法。

您可以针对all_tab_columns编写查询，这将生成您实际需要的查询作为其输出。复制输出并将其粘贴回运行查询的编辑器中，然后运行查询。

我在架构SCOTT中为表EMP显示了这一点 - 根据需要进行调整。

select 'select * from SCOTT.EMP group by ' ||
listagg(column_name, ',') within group (order by column_id) ||
' having count(*) > 1;' as sql_str
from   all_tab_columns
where  owner = 'SCOTT'
and  table_name = 'EMP'
;
SQL_STR                                                                                           
--------------------------------------------------------------------------------------------------
select * from scott.emp group by EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO having count(*) > 1;

如果在检查重复行后决定需要删除重复项，则可以执行以下操作：

delete from [table]
where  rowid not in ( select min(rowid) from [table] group by ...  )

其中分组是按所有列的同一组。也就是说 - 修改 FIRST 查询，即生成您实际想要运行的查询的查询，改为采用此形式。