Als PDF Downloaden!

Tipps & Tricks: September 2011
Bereich:
SQL
Erstellung:
09/2011 EF
Versionsinfo:
10.2, 11.1, 11.2
Letzte Überarbeitung:
09/2011 EF
Löschen von doppelten Datensätzen
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 7
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.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 7
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 SQL-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:
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 7
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 (create_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 duplikate_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 hier.
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,
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 4 von 7
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 5 von 7
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.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 6 von 7
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 7 von 7