Vergleich von Tabellen in 10g und 11g II

02.
August
2011
Veröffentlicht von: Elke Fritsch

Der Vergleich und die Synchronisation von Tabellen waren schon einmal Gegenstand eines Monatstipps im 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.

Der Vergleich und die Synchronisation von Tabellen waren schon einmal Gegenstand eines Monatstipps im Opens external link in new windowMai 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 Tabellen müssen dieselbe Spaltenstruktur und die gleichen Spaltennamen haben
  • Der Primärschlüssel muss für die 10g-Version einspaltig sein (für den Umgang mit mehrspaltigen Primärschlüssel müsste das Package entsprechend erweitert werden)
  • In der 10g-Version braucht der Ersteller des Packages ein direkt verliehenes CREATE TABLE-Recht, in der 11g-Version das EXECUTE-Recht an DBMS_COMPARISON und in beiden Packages natürlich die Select-Rechte an den beteiligten Objekten für den Vergleich bzw. Insert-, Update und Delete-Rechte für die Synchronisation
  • Der Vergleich von Spalten vom Typ CLOB, BLOB, BFILE, LONG, XMLType oder Spalten, die auf benutzerdefinierten Typen basieren, ist nicht möglich. Für den Vergleich von Tabellen, die derartige Spalten enthalten, muss man auf die älteren Versionen aus dem Monatstipp vom Mai 2009 ausweichen und diese Spalten vom Vergleich ausschließen.
  • Ein paralleler Vergleich von mehreren Tabellen ist (noch) nicht möglich, da nicht für jeden Vergleich eine eigene Differenzentabelle erstellt wird.

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:

  • Initiates file downloaduser_anlegen.sql erstellt die User local1 und local2 auf der lokalen DB sowie einen User remote auf der remote-DB und die dazugehörigen DB-Links (für letztere müssen vorher die TNSNAMES.ORA-Dateien mit entsprechenden TNS-Alias-Namen ergänzt werden)
  • Initiates file downloadtabellen_anlegen.sql erzeugt
    • im Schema local1 auf dem lokalen Server die Tabellen emp_local1 und emp_local2 sowie big_tab1 und big_tab2 für Vergleiche im selben Schema
    • im Schema local2 auf dem lokalen Server die Tabellen emp_local2 und big_tab2 für Vergleiche zwischen verschiedenen Schemata
    • im Schema remote auf dem remote-Server die Tabellen emp_remote und big_tab2 für Vergleiche zwischen verschiedenen Datenbanken
  • Initiates file downloadVergleich_10g_alle_Spalten.pkg und Initiates file downloadVergleich_11g_alle_Spalten.pkg enthalten die Vergleichspackages


Vergleich und Synchronisation mit dem Package vergleich_10g

Die Parameter p_hash, p_diff und p_sync der zentralen Prozedur vergleichen ermöglichen verschiedene Durchführungsoptionen, je nachdem, ob man

  •  nur wissen will, ob sich die Tabellen unterscheiden,
  •  die Unterschiede im Detail ermitteln will
  •  die Tabellen synchronisieren will

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.
Da die Sychronisation über einen Merge der Diff-Tabelle mit der Zieltabelle geschieht, kann man sie nicht isoliert ablaufen lassen. (Es wäre zwar möglich, aber dann müßte man wie bei DBMS_COMPARISON ein Logging der einzelnen Vergleiche über Vergleichsnamen implementieren oder der diff-Tabelle eindeutige Namen geben und den Code entsprechend erweitern).

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
 


Vergleich und Synchronisation mit dem Package vergleich_11g

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.
Per default (p_hashonly = FALSE) werden die Unterschiede ermittelt. Das ist der geschwindigkeitsbestimmende Schritt.

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


Laufzeittests

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


Fazit

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 Opens internal link in current windowPL/SQL II - oder Packages-Kurse.

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.