Поиск идентичных строк

После массированной загрузки данных в БД из внешних источников часто возникает ситуация, когда необходимо найти дубликаты строк, которые препятствуют созданию уникального или первичного ключа на таблице. Существует несколько вариантов решения этой задачи.

SQL> desc docs
 Name                        Null?    Type
 --------------------------- -------- --------------
 DOC_ID                      NOT NULL NUMBER(10)
 BILL_ID                     NOT NULL NUMBER(10)
 STATUS                      NOT NULL CHAR(1)
 DOC_TYPE                    NOT NULL CHAR(2)
 INVOICE_NUM                          VARCHAR2(20)

SQL>
SQL> select count(*) from docs;

 COUNT(*)
---------
  2625004

SQL>
SQL> set timing on
SQL> alter table docs add constraint pk_docs primary key (doc_id);
alter table docs add constraint pk_docs primary key (doc_id)
                                *
ERROR at line 1:
ORA-02437: cannot validate (EDWARD.PK_DOCS) - primary key violated


 real: 56150
SQL>

Вариант 1

Наверное наиболее логичный и интуитивно понятный способ - сделать группировку по столбцу.

SQL> select doc_id, count(*)
  2  from docs
  3  group by doc_id
  4  having count(*) > 1;

   DOC_ID  COUNT(*)
--------- ---------
    53470         2
  1170094         2
  2153448         2
  2845200         2

 real: 62049

OPERATION         OPTIONS   OBJECT_NAME ID PARENT_ID   COST CARDINALITY     BYTES
----------------- --------- ----------- -- --------- ------ ----------- ---------
SELECT STATEMENT                         0            10274      131250    656250
FILTER                                   1         0
SORT              GROUP BY               2         1  10274      131250    656250
TABLE ACCESS      FULL      DOCS         3         2   1701     2625004  13125020

Недостаток этого метода в том, что он не дает значения ROWID строк, а значит нужен дополнительный full table scan для последующего удаления дубликатов.

Вариант 2

Этот вариант использует тот факт, что ROWID уникален для любой строки в пределах таблицы.

SQL> select doc_id, rowid
  2  from docs d1
  3  where rowid > (select min(rowid)
  4                 from docs d2
  5                 where d2.doc_id = d1.doc_id)

OPERATION         OPTIONS    OBJECT_NAME   ID PARENT_ID  COST CARDINALITY     BYTES
----------------- ---------- ------------ --- --------- ----- ----------- ---------
SELECT STATEMENT                            0            1701      131251   1575012
FILTER                                      1         0
TABLE ACCESS      FULL       DOCS           2         1  1701      131251   1575012
SORT              AGGREGATE                 3         1                 1        12
TABLE ACCESS      FULL       DOCS           4         3  1701           2        24

Для такой большой таблицы (2625004 строк) этот запрос будет отрабатывать часы. Я не советую применять его на больших таблицах.

Вариант 3

Использует возможности, предоставляемые Oracle для проверки уникальности ключа.

1. Создание вспомогательной таблицы exceptions через скрипт $ORACLE_HOME/rdbms/admin/utlexcpt.sql.
Скрипт создает таблицу exceptions следующей структуры:

SQL> desc exceptions
 Name           Null?    Type
 -------------- -------- --------------
 ROW_ID                  ROWID
 OWNER                   VARCHAR2(30)
 TABLE_NAME              VARCHAR2(30)
 CONSTRAINT              VARCHAR2(30)

Вы можете переименовать эту таблицу по своему желанию

2. Получение ROWID строк, вызывающих нарушение уникальности ключа.

SQL> alter table DOCS add constraint PK_DOCS primary key (DOC_ID)
  2  exceptions into EXCEPTIONS;
alter table DOCS add constraint PK_DOCS primary key (DOC_ID)
                                *
ERROR at line 1:
ORA-02437: cannot validate (EDWARD.PK_DOCS) - primary key violated

 real: 204695
SQL>
SQL> select d.doc_id
  2  from docs d, exceptions e
  3  where d.rowid = e.row_id and
  4        e.constraint = 'PK_DOCS'
  5  order by d.doc_id;

   DOC_ID
---------
    53470
    53470
  1170094
  1170094
  2153448
  2153448
  2845200
  2845200

8 rows selected.

 real: 491

Заключение

Вариант 1 и Вариант 2 пригодны для поиска дупликатов строк в небольших таблицах. Вариант 3 - универсален.