Löschen von doppelten Datensätzen

06.
September
2011
Veröffentlicht von: Elke Fritsch

Zum Löschen von doppelten Datensätzen gibt es eine ganze Reihe von Ansätzen. Die Suche nach der Schlagwort-Kombination duplicate delete und Oracle ergibt bei Google ca. 451.000 Treffer. Viele der Methoden funktionieren hervorragend bei den 10 bis 30 Beispieldatensätzen, die meist auch keine NULL-Werte enthalten. Wenn die Tabellen aber größer werden, stellt sich die Frage nach der performantesten Methode.

Zum Löschen von doppelten Datensätzen gibt es eine ganze Reihe von Ansätzen. Die Suche nach der Schlagwort-Kombination duplicate delete und Oracle ergibt bei Google ca. 451.000 Treffer. Viele der Methoden funktionieren hervorragend bei den 10 bis 30 Beispieldatensätzen, die meist auch keine NULL-Werte enthalten. Wenn die Tabellen aber größer werden, stellt sich die Frage nach der performantesten Methode.

Dieser Monatstip stellt die gängigsten Methoden am Beispiel einer manipulierten emp-Tabelle vor und vergleicht danach deren Performance anhand einer Tabelle mit 2 Mio. Datensätzen.

Vorbereitung der emp-Tabelle:
Ein Datensatz wird verdoppelt, ein anderer verdreifacht, nur die Primärschlüssel bleiben unangetastet.

UPDATE emp SET (ename, job, mgr, hiredate, sal, comm, deptno)
    = (SELECT ename, job, mgr, hiredate, sal, comm, deptno
       FROM emp WHERE empno = 7369)
WHERE empno = 7566;
UPDATE emp SET (ename, job, mgr, hiredate, sal, comm, deptno)
     = (SELECT ename, job, mgr, hiredate, sal, comm, deptno
        FROM emp WHERE empno = 7499)
 WHERE empno in (7839, 7902);
COMMIT;


Wie findet man die Duplikate

Auch hier gibt es mehrere Ansätze, der gebräuchlichste ist sicher:

SELECT spaltenliste, COUNT(*)
FROM tabelle
GROUP BY spaltenliste
HAVING COUNT(*) > 1;

Mit Spaltenliste ist hier die Kombination der Spalten gemeint, die keine Duplikate aufweisen soll.

SELECT ename, job, mgr, hiredate, sal, comm, deptno, COUNT(*)
FROM emp
GROUP BY ename, job, mgr, hiredate, sal, comm, deptno
HAVING COUNT(*) > 1;

ENAME   JOB          MGR HIREDATE    SAL  COMM DEPTNO   COUNT(*)
------- ---------- ----- -------- ------ ----- ------ ----------
ALLEN   SALESMAN    7698 20.02.81   1600   300     30          3
SMITH   CLERK       7902 17.12.80    800           20          2

Dieser alternative Select mit einer korrelierten Unterabfrage liefert die kompletten Datensätze der Duplikate, sofern man NULL-Spalten mit NVL entschärft (sonst wird hier z.B. das Duplikat von Smith nicht angezeigt)

SELECT * FROM emp e1
WHERE EXISTS (SELECT 1 FROM emp e2
               WHERE e2.ename = e1.ename
                   AND e1.job = e2.job
                   AND e1.mgr = e1.mgr
              AND e1.hiredate = e2.hiredate
                   AND e1.sal = e2.sal
           AND NVL(e1.comm,0) = NVL(e2.comm,0)
                AND e1.deptno = e1.deptno
                 AND e2.rowid < e1.rowid);

EMPNO ENAME   JOB          MGR HIREDATE    SAL  COMM DEPTNO
----- ------- ---------- ----- -------- ------ ----- ------
 7566 SMITH   CLERK       7902 17.12.80    800           20
 7839 ALLEN   SALESMAN    7698 20.02.81   1600   300     30
 7902 ALLEN   SALESMAN    7698 20.02.81   1600   300     30

Eine dritte, eher exotische Methode besteht darin, einen Unique-Constraint auf die Spaltenkombination zu setzen, die eindeutig sein soll und die Fehler (die Duplikate) in die von Oracle über das Skript utlexcpt.sql zur Verfügung gestellte Exceptions-Tabelle zu schreiben.

@ ?\rdbms\admin\utlexcpt
ALTER TABLE emp ADD CONSTRAINT emp_uq
UNIQUE (ename, job, mgr, hiredate, sal, comm, deptno)
EXCEPTIONS INTO EXCEPTIONS;
SELECT e.* FROM exceptions x JOIN emp e ON e.rowid = x.row_id;
=>
EMPNO ENAME   JOB          MGR HIREDATE    SAL  COMM DEPTNO
----- ------- ---------- ----- -------- ------ ----- ------
 7369 SMITH   CLERK       7902 17.12.80    800           20
 7499 ALLEN   SALESMAN    7698 20.02.81   1600   300     30
 7566 SMITH   CLERK       7902 17.12.80    800           20
 7839 ALLEN   SALESMAN    7698 20.02.81   1600   300     30
 7902 ALLEN   SALESMAN    7698 20.02.81   1600   300     30


Methode 1: Löschen der Duplikate über eine nicht-korrelierte Unterabfrage

Dies ist sicher der bekannteste Ansatz:

DELETE FROM tabelle
WHERE rowid NOT IN (SELECT MIN(rowid) FROM tabelle
                    GROUP BY spaltenliste);

In unserem Beispiel also:

DELETE FROM emp
WHERE rowid NOT IN (SELECT MIN(rowid) FROM emp
                    GROUP BY ename, job, mgr, hiredate, sal, comm, deptno);

3 rows deleted.
ROLLBACK;


Methode 2: Löschen der Duplikate über eine korrelierte Unterabfrage.

Allgemeine Syntax:

DELETE FROM tabelle t1
     WHERE rowid < (SELECT MAX(rowid) FROM tabelle t2
                    WHERE t1.col1 = t2.col1
                    AND   t1.col2 = t2.col2
                    AND   t1.col3 = t2.col3 ....);

Statt rowid < (SELECT MAX(rowid)... kann man natürlich auch rowid > (SELECT MIN(rowid)... oder rowid < ANY (SELECT rowid... verwenden

Nachteile:
•    Ohne die Behandlung von NULL-Spalten mit NVL erwischt man nur einen Teil der Datensätze !!!!
•    Korrelierte Update- und Delete-Statements sind als besonders unperformant berüchtigt
•    Bei Tabellen mit vielen Spalten wird das Statement sehr lang

Das Statement sieht bei der manipulierten emp-Tabelle dann so aus:

DELETE FROM emp e1
     WHERE rowid < (SELECT MAX(rowid) FROM emp e2
                    WHERE e1.ename     = e2.ename
                    AND  e1.job        = e2.job
                    AND  e1.mgr        = e2.mgr
                    AND  e1.hiredate   = e2.hiredate
                    AND  e1.sal        = e2.sal
                    AND NVL(e1.comm,0) = NVL(e2.comm,0)
                    AND  e1.deptno     = e2.deptno);
3 rows deleted.
ROLLBACK;

 

Methode 3: Löschen der Duplikate über analytische Funktionen

Von Tom Kyte empfohlen (s. http://www.oracle.com/technetwork/issue-archive/o44asktom-089519.html). Näheres zu analytischen Funktionen erfahren Sie in unserem Opens internal link in current windowSQL-II-Kurs oder ab dem nächsten Frühjahr in einem eintägigen Spezial-Kurs zu diesem Thema.

Allgemeine Syntax:

DELETE FROM tabelle
WHERE rowid IN
   (SELECT rid FROM
      (SELECT rowid rid,
             ROW_NUMBER() OVER(PARTITION BY spaltenliste ORDER BY rowid) rn
       FROM tabelle)
WHERE rn <> 1);

In unserem Beispiel also:

DELETE FROM emp
WHERE rowid IN
   (SELECT rid FROM
      (SELECT rowid rid,
             ROW_NUMBER()
             OVER(PARTITION BY ename, job, mgr, hiredate, sal, comm, deptno
             ORDER BY rowid) rn
       FROM emp)
    WHERE rn <> 1);

3 rows deleted.
ROLLBACK;


Methode 4 (außer Konkurrenz): Erstellen einer neuen Tabelle ohne Duplikate

Auch diesen Ansatz findet man in dem oben angeführten Artikel von Tom Kyte. Er ist besonders schnell, aber in Produktivumgebungen kaum umzusetzen. Man erstellt aus den gewünschten Daten eine neue Tabelle, löscht die alte, erstellt die Indizes neu und benennt die neue Tabelle um.

CREATE TABLE emp2 AS
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM (SELECT b.*,
          ROW_NUMBER()
          OVER(PARTITION BY ename, job, mgr, hiredate, sal, comm, deptno
          ORDER BY rowid) rn
      FROM emp b)
WHERE rn = 1;
DROP TABLE emp PURGE;
ALTER TABLE emp2 ADD CONSTRAINT emp_pk PRIMARY KEY(empno);
RENAME emp2 TO emp;


Performance-Tests mit großen Tabellen

Für die Performance-Tests wurde eine Tabelle mit 2 Mio. Datensätzen auf Basis der dba_objects (Tom Kytes big_tab) im Schema Scott verwendet (Initiates file downloadcreate_bigtab_2M.sql). Auch hier sind nur die Primärschlüssel noch unique.
Damit die Tests auch auf einer 10g-DB laufen, wurden die Spalten edition_name und namespace weggelassen.
Das Skript Initiates file downloadduplikate_loeschen.sql testet die oben erwähnten Methoden nacheinander durch und schreibt die Ergebnisse samt Timing und Ausführungsplan in ein Spool-File duplikate_loeschen_test.txt. Zwischen den einzelnen Durchgängen wird die Tabelle jeweils neu erstellt sowie Buffer cache und shared pool geflusht.
Lassen Sie das Skript auf keinen Fall in einer Produktivumgebung laufen !!
Hier werden nur die Ausführungszeiten wiedergegeben. Die kompletten Ergebnisse für die Tests auf 3 Servern, jeweils mit einer 10g und einer 11g-DB finden Sie Initiates file downloadhier.

Duplikate ermitteln:

conn sys/sys as sysdba
set timing on
SELECT COUNT(*), zahl
FROM (SELECT owner, object_name, subobject_name, object_id, data_object_id,
             object_type, created, last_ddl_time, timestamp, status,
             temporary, generated, secondary, COUNT(*) zahl
     FROM scott.big_tab
     GROUP BY owner, object_name, subobject_name, object_id, data_object_id,
              object_type, created, last_ddl_time, timestamp, status,
             temporary, generated, secondary
     HAVING COUNT(*) > 1)
GROUP BY zahl;
=>
-- für 10g
  COUNT(*)       ZAHL
---------- ----------
     48518         40
      1520         39

-- für 11g
COUNT(*)       ZAHL
---------- ----------
     36128         28
     36608         27

 Löschen der Duplikate über eine nicht-korrelierte Unterabfrage

DELETE FROM scott.big_tab
WHERE rowid NOT IN (SELECT MIN(rowid) FROM scott.big_tab
                    GROUP BY owner, object_name, subobject_name, object_id,
                         data_object_id, object_type, created, last_ddl_time,
                         timestamp, status, temporary, generated, secondary);

-- Laufzeiten für 10g: zwischen 1:29,76 und 1:35.00 Minuten
-- Laufzeiten für 11g: zwischen 1:30.18 und 2:00.85 Minuten

Löschen der Duplikate über eine korrelierte Unterabfrage.

Trotz der prinzipiell gleichen Hard- und Software-Ausstattung waren die Laufzeiten hier sehr unterschiedlich. 2 Server lagen reproduzierbar zwischen 16 und 18 Minuten, einer brachte es auf 5-6 Minuten.

DELETE FROM scott.big_tab b1
WHERE rowid <
  (SELECT MAX(rowid) FROM scott.big_tab b2
   WHERE b1.owner                      = b2.owner
   AND b1.object_name                  = b2.object_name
   AND NVL(b1.subobject_name, 'nn')    = NVL(b2.subobject_name, 'nn')
   AND b1.object_id                    = b2.object_id
   AND NVL(b1.data_object_id,0)        = NVL(b2.data_object_id,0)
   AND b1.object_type                  = b2.object_type
   AND b1.created                      = b2.created
   AND NVL(b1.last_ddl_time,sysdate)   = NVL(b2.last_ddl_time,sysdate)
   AND NVL(b1.timestamp, systimestamp) = NVL(b2.timestamp, systimestamp)
   AND b1.status                       = b2.status
   AND b1.temporary                    = b2.temporary
   AND b1.generated                    = b2.generated
   AND b1.secondary                    = b2.secondary);

-- Laufzeiten für 10g: zwischen 5:44,96 und 16:39,74 Minuten
-- Laufzeiten für 11g: zwischen 6:31,56 und 18:22.90 Minuten

Löschen der Duplikate über analytische Funktionen

DELETE FROM scott.big_tab
WHERE rowid IN
   (SELECT rid FROM
      (SELECT rowid rid,
             ROW_NUMBER()
             OVER(PARTITION BY owner, object_name, subobject_name, object_id,
                         data_object_id, object_type, created,
                         last_ddl_time, timestamp,
                         status, temporary, generated, secondary
             ORDER BY rowid) rn
       FROM scott.big_tab)
    WHERE rn <> 1);

-- Laufzeiten für 10g: zwischen 3:11,59 und 3:41,30 Minuten
-- Laufzeiten für 11g: zwischen 4:40,64 und 5:06.32 Minuten

Duplikate eliminieren über eine neue Tabelle

--Tabelle ohne Duplikate erstellen
CREATE TABLE big_tab2 AS
SELECT id, owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary
FROM (SELECT b.*,
      ROW_NUMBER()
      OVER(PARTITION BY owner, object_name, subobject_name, object_id,
                       data_object_id, object_type, created, last_ddl_time,
                      timestamp, status, temporary, generated, secondary
          ORDER BY rowid) rn
      FROM big_tab b)
WHERE rn = 1;

--alte Tabelle löschen
DROP TABLE big_tab PURGE;

--Primärschlüssel auf neuer Tabelle erstellen
ALTER TABLE big_tab2 ADD CONSTRAINT big_tab_pk PRIMARY KEY(id);

--neue Tabelle umbenennen
RENAME big_tab2 TO big_tab;

-- Gesamtlaufzeiten für 10g und 11g unter 20 Sekunden!!


Fazit

Der bekannte Ansatz zum Löschen von Duplikaten über eine nicht korrelierte Unterabfrage ist zumindest bei unseren Tests sehr schnell. Die Verwendung von analytischen Funktionen ist dann vorteilhaft, wenn man Duplikate nur in einer Spalte entfernen will und nicht über eine Spaltenkombination partionieren muss. Von Löschvorgängen über korrelierte Unterabfragen sollte man lieber absehen, vor allem, wenn man nicht genau weiß, in welchen Spalten NULL-Werte zu erwarten sind.

Die Erstellung einer neuen Tabelle ohne Duplikate ist zwar gerade bei großen Tabellen der einfachste und schnellste Weg, aber in Produktivumgebungen meist nicht einsetzbar.

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.