Der Vergleich und die Synchronisation von Tabellen waren schon einmal Gegenstand eines Monatstipps im <link blog-detailansicht vergleich-von-tabellen-in-10g-und-11g.html external-link-new-window internal link in current>Mai 2009. Damals beschränkte sich der Vergleich jedoch auf bis zu 4 Spalten. Die hier vorgestellten Packages für Oracle 10g und 11g ermöglichen den Vergleich aller Spalten.
Das Package vergleich_11g ist ein Wrapper für das in der Oracle Version 11g eingeführte DBMS_COMPARISON, das den Umgang mit diesem optionsreichen Tool etwas einfacher gestalten soll, das Package Vergleich_10g ist auch in der Oracle-Version 10g lauffähig und darüberhinaus schneller als DBMS_COMPARISON.
Voraussetzung
Die beiden Packages wurden auf einer 10gR2-Datenbank und einer 11gR2-Datenbank auf Windows7 64bit getestet mit je 2 unterschiedlichen Tabellen im gleichen Schema, in verschiedenen Schemata und verschiedenen Datenbanken getestet.
Beschreibung der Test-Skripte:
Die Parameter p_hash, p_diff und p_sync der zentralen Prozedur vergleichen ermöglichen verschiedene Durchführungsoptionen, je nachdem, ob man
Parameter | Default-Wert | Beschreibung |
---|---|---|
p_hashonly | FALSE | Wenn p_hash auf TRUE steht, werden nur die Hash-Werte der Tabellen verglichen, aber keine Unterschiede ermittelt. |
p_diff | FALSE | Wenn p_diff auf TRUE gesetzt wird, werden alle Datensätze, in denen Unterschiede auftauchen, in eine dynamisch erstellte Tabelle diff_10g eingetragen. Während man bei DBMS_COMPARISON nur die Primärschlüsselwerte der sich unterscheidenden Datensätze ermitteln kann, enthält die Diff-Tabelle die Werte aller Spalten. |
p_sync | FALSE | Wenn p_sync auf TRUE gesetzt wird, wird zunächst die Tabelle diff_10g befüllt und danach die Synchronisation angestoßen. Dabei wird die Zieltabelle mit den Werten der Basistabelle überschrieben. |
Die Ausführungsbeispiele hier beziehen sich nur auf den Vergleich von Tabellen über einen DB-Link:
-- Vergleich der Hash-Werte
conn local/local
set serveroutput on
BEGIN
vergleich_10g.vergleichen(
p_local => 'local', -- Name des lokalen Schemas
p_remote => 'remote', -- Name des Zielschemas
p_basistab => 'emp_local', -- Name der Originaltabelle
p_zieltab => 'emp_remote', -- Name der Zieltabelle
p_link => 'remote_link', -- Datenbank-Link zum Remote-Schema
p_hashonly => TRUE);
END;
/
=>
Hash-Wert der Basistabelle local1.emp_local1: 245049078305
Hash-Wert der Zieltabelle remote.emp_remote@remote_link: 239424418269
Die Tabellen stimmen nicht ueberein
-- Ermittlung der Unterschiede ohne Synchronisation
set serveroutput on
BEGIN
vergleich_10g.vergleichen(
p_local => 'local',
p_remote => 'remote',
p_basistab => 'emp_local',
p_zieltab => 'emp_remote',
p_link => 'remote_link'
p_diff => true);
END;
/
=>
Es wurden 97 abweichende Datensätze gefunden und in die Tabelle Diff_10g eingetragen
SELECT employee_id empno, first_name||' 'last_name name, hire_date, salary, in_basis, in_vergleich
FROM diff_10g;
=>
EMPNO Name HIRE_DATE SALARY IN_BASIS IN_VERGLEICH
499 Hannah Ahrends 13.01.00 3603 fehlt vorhanden
166 Sundar Ande 24.03.00 6400 fehlt vorhanden
204 Hermann Baer 24.05.94 10500 vorhanden fehlt
116 Shelli Baida 10.12.97 3045 vorhanden fehlt
116 Shelli Baida 31.12.97 2755 fehlt vorhanden
192 Sarah Bell 11.02.96 3800 fehlt vorhanden
192 Sarah Bell 21.01.96 4200 vorhanden fehlt
303 Centa Berger 07.06.07 6503 vorhanden fehlt
151 David Bernstein 24.03.97 9500 vorhanden fehlt
407 Wilhelm Busch 01.07.11 3203 fehlt vorhanden
148 Gerald Cambrault 22.10.99 10450 fehlt vorhanden
148 Gerald Cambrault 01.10.99 11550 vorhanden fehlt
403 Philipp der Gute 07.06.07 6503 fehlt vorhanden
402 Karl der Kahle 17.08.97 6003 fehlt vorhanden
401 Johanna die Wahnsinnige 17.02.08 13003 fehlt vorhanden
....
-- Ermittlung der Unterschiede mit Synchronisation
set serveroutput on
BEGIN
vergleich_10g.vergleichen(
p_local => 'local',
p_remote => 'remote',
p_basistab => 'emp_local',
p_zieltab => 'emp_remote',
p_link => 'remote_link'
p_sync => true);
END;
/
Es wurden 97 abweichende Datensätze gefunden und in die Tabelle Diff eingetragen
Beginn der Synchronisation
Synchronisation abgeschlossen
Das Vergleichspackage für die Version 11g bietet ähnliche Optionen wie vergleich_10g. Für die Befüllung der Differenzentabelle und die Synchronisation gibt es hier aber eigene Prozeduren, da DBMS_COMPARISON Vergleichstemplate erstellt und man die einzelnen Vergleiche über ihren Namen ansprechen kann.
Parameter | Default-Wert | Beschreibung |
---|---|---|
p_hashonly | FALSE | Wenn p_hashonly auf TRUE steht, werden nur die Hash-Werte der Tabellen verglichen (das geht am schnellsten, ermöglicht aber noch keinen Vergleich. |
p_diff | FALSE | Wenn p_diff auf TRUE steht, werden die Unterschiede festgehalten und alle Datensätze, in denen Unterschiede auftauchen, in die Tabelle diff_11g eingetragen. Die Tabelle basiert auf einem Join der DD-Views user_comparison_row_dif und user_comparison und liefert Informationen, für welche Index-Werte sich die Datensätze in den 2 Tabellen unterscheiden bzw. wo Datensätze fehlen. |
p_sync | 0 | zum Überschreiben der Zieltabelle wird p_sync auf 1 gesetzt, für das Überschreiben in umgekehrter Richtung auf 2. Nach der Synchronisation wird der Vergleich gelöscht. |
-- Vergleich der Hash-Werte
conn local/local
set serveroutput on
BEGIN
vergleich_11g.vergleichen(
p_local => 'local1',
p_basistab => 'emp_local1',
p_zieltab => 'emp_remote',
p_link => 'remote_link'
p_hashonly => TRUE);
END;
/
Die Objekte stimmen nicht ueberein
--Ermittlung der Unterschiede
BEGIN
vergleich_11g.vergleichen(
p_local => 'local1',
p_remote => 'remote',
p_basistab => 'emp_local1',
p_zieltab => 'emp_remote'
p_link => 'remote_link');
END;
/
Vergleichsname: local1_emp_local1_41, aktuelle Scan-Nr: 141
Es wurden 76 Unterschiede gefunden.
-- Füllen der Differenzen-Tabelle
BEGIN
vergleich_11g.diff_uebersicht (p_vergleichsname => 'local1_emp_local1_41');
END;
/
21 Zeilen haben unterschiedliche Werte
27 Zeilen fehlen in der Basistabelle
28 Zeilen fehlen in der zieltabelle
-- Dieser Select liefert die Pk-Werte der unterschiedlichen Datensätze
SELECT pk_wert, in_basis "in emp_local vorhanden", in_vergleich "in emp_remote vorhanden"
FROM diff_11g;
=>
PK_WERT in emp_local vorhanden in emp_remote vorhanden
---------- ---------------------- -----------------------
100 ja ja
104 ja ja
108 ja ja
111 nein ja
116 ja ja
120 ja ja
124 ja ja
128 ja ja
133 nein ja
136 ja ja
112 ja nein
132 ja nein
140 ja ja
144 nein ja
148 ja ja
150 nein ja
.....
-- Synchronisation
BEGIN
vergleich_11g.sync(
p_vergleichsname => 'local1_emp_local1_41',
p_richtung => 1);
END;
/
49 Zeilen in der Zieltabelle ueberschrieben
27 Zeilen in der Zieltabelle geloescht
Synchronisation abgeschlossen
Laufzeittestvergleiche mit einer 2-Mio-Datensatz-Tabelle auf einer Oracle 11g-DB auf Windows7 64Bit. Das Package vergleich_10g wurde auch auf 2 Oracle 10g-Datenbanken auf denselben Servern getestet. Die Zeiten lagen allgemein einige Sekunden über denen der Tests in Oracle 11g, die Trends waren aber die gleichen.
Für die Laufzeittests wurden Vergleichstabellen mit je 2 Mio. Datensätzen erstellt, bei denen zwischen 2000 und 3000 Datensätze verändert wurden. Zwischen den Tests wurde jeweils der Buffer Cache und der Shared Pool geleert.
vergleich_10g | vergleich_11g | ||
---|---|---|---|
Vergleich der Hash-Werte zweier Tabellen | im selben Schema | 13.05 sec | 1:14.58 |
in verschiedenen Schemata | 13.21 sec | 1:14.20 | |
über einen DB-Link | 14.33 sec | 1:22.12 | |
Ermittlung der Differenzen | im selben Schema: | 45.14 sec | 2:10.88 |
in verschiedenen Schemata | 35.12 sec | 2:18.16 | |
über einen DB-Link | 43.72 sec | 2:26.35 | |
Befüllung der Differenzentabelle (nur 11g) | im selben Schema | 03.74 sec | |
in verschiedenen Schemata | 04.47 sec | ||
über einen DB-Link | 04.27 sec | ||
Synchronisation | im selben Schema | 54.04 sec | 10.63 sec |
in verschiedenen Schemata | 41.31 sec | 09.96 sec | |
über einen DB-Link | 52.09 sec | 10.83 sec | |
Gesamtzeit für Synchronisation | 50 - 60 sec | 2,2 bis 2,5 min |
Das Package vergleich_10g ist durchaus eine Alternative für DBMS_COMPARISON. Probieren Sie die beiden Packages einfach mal in einer Testumgebung aus. Wenn Sie mehr über Oracle Built-in-Packages (und alternative Ansätze) wissen wollen, besuchen Sie doch unsere PL/SQL II - oder Packages-Kurse.
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.