另一种可能运行得更快的方法是使用字符串连接：

SELECT 1col || 2col || 3col AS string
FROM tab
GROUP BY 1col || 2col || 3col
HAVING COUNT(*) > 1;

在Oracle 12中，可以使用MATCH_RECOGNIZE执行逐行模式匹配：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY col1, col2, col3  -- all the columns
  ORDER BY col3                  -- can be anything as PARTITION BY contains all the
                                 -- columns
  PATTERN (^ any_row{2} )        -- look for the first 2 rows in the partition
  DEFINE
    any_row AS 1 = 1             -- does not matter as the PARTITION BY clause is
                                 -- separating the rows into unique groups
)

其中，对于示例数据：

CREATE TABLE table_name ( col1, col2, col3 ) AS
SELECT 1, 1, 1 FROM DUAL UNION ALL
SELECT 1, 1, 1 FROM DUAL UNION ALL
SELECT 1, 1, 1 FROM DUAL UNION ALL
SELECT 1, 2, 3 FROM DUAL UNION ALL
SELECT 1, 2, 3 FROM DUAL UNION ALL
SELECT 2, 3, 4 FROM DUAL;

输出：
| COL1|COL2|COL3|
| - ------|- ------|- ------|
| 1个|1个|1个|
| 1个|第二章|三个|
fiddle

可以使用分析函数或内联视图查找重复行。

CREATE TABLE fruits (
        fruit_id   NUMBER generated BY DEFAULT AS IDENTITY,
        fruit_name VARCHAR2(100),
        color VARCHAR2(20)
);

INSERT INTO fruits(fruit_name,color) VALUES('Apple','Red');
INSERT INTO fruits(fruit_name,color) VALUES('Apple','Red');
INSERT INTO fruits(fruit_name,color) VALUES('Orange','Orange');
INSERT INTO fruits(fruit_name,color) VALUES('Orange','Orange');
INSERT INTO fruits(fruit_name,color) VALUES('Orange','Orange');
INSERT INTO fruits(fruit_name,color) VALUES('Banana','Yellow');
INSERT INTO fruits(fruit_name,color) VALUES('Banana','Green');

/* analytic function */

WITH fruit_counts AS (
    SELECT f.*,
        COUNT(*) OVER (PARTITION BY fruit_name, color) c
    FROM fruits f
)
SELECT *
FROM fruit_counts
WHERE c > 1 ;

/* inline view */

SELECT 
    *
FROM
        (SELECT f.*,
            COUNT(*) OVER (PARTITION BY fruit_name, color) c
        FROM fruits f
        )
WHERE c > 1;

FRUIT_ID    FRUIT_NAME  COLOR   C
2   Apple   Red 2
1   Apple   Red 2
5   Orange  Orange  3
3   Orange  Orange  3
4   Orange  Orange  3