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 Monatstipp 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. Näheres zu analytischen Funktionen erfahren Sie in unserem SQL II Kurs.
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 (AUSSER 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 GROSSEN 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. Auch hier sind nur die Primärschlüssel noch unique.
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.*,