Migrationshandbuch von Oracle zu SQL Server und von SQL Server zu Oracle – Pt. 2

Im ersten Teil dieser Serie wurden die Unterschiede zwischen Oracle Database und Microsoft SQL Server bei der Implementierung von Transaktionen erörtert, wobei der Schwerpunkt auf Fallstricken lag, die bei Migrationen von Oracle zu SQL Server und umgekehrt auftreten können. Diese nächste Ausgabe behandelt eine Reihe häufig verwendeter SQL-Syntaxelemente, die keine Übereinstimmung haben oder eine völlig andere Bedeutung oder Verwendung in der Oracle-SQL-Server-Kluft haben.

Sequenzen in Oracle und Identitätsspalten in SQL Server

In der Datenbank-Community besteht seit langem eine Kluft zwischen zwei Lagern: den Patrioten natürlicher Schlüssel und den Befürwortern künstlicher (oder „Ersatz-“) Schlüssel.

Ich selbst verteidige natürliche Schlüssel, erfinde mich aber oft dabei, aus dem einen oder anderen Grund Surrogate zu erstellen. Aber lassen wir den Inhalt dieser Debatte beiseite und schauen wir uns die Standardmechanismen zum Generieren künstlicher Schlüssel an: Oracle-Sequenzen und SQL Server-Identitätsspalten.

Eine Oracle-Sequenz ist ein erstklassiges Objekt auf Datenbankebene. Im Gegensatz dazu ist eine SQL Server-Identitätsspalte ein Spaltentyp und kein Objekt.

Wenn eine Oracle-Sequenz verwendet wird, um einen Tabellenschlüssel – normalerweise einen Primärschlüssel – zu generieren, wird er garantiert inkrementiert und ist daher eindeutig. Aber es ist nicht garantiert, dass es fortlaufend ist. Tatsächlich ist es selbst in gut gestalteten Implementierungen höchstwahrscheinlich, dass es einige Lücken gibt. Daher sollte sich keine Oracle-Implementierung jemals darauf verlassen, dass sequenzgenerierte Werte fortlaufend sind.

Außerdem wird eine Sequenz über das Data Dictionary einer Oracle-Datenbank verwaltet, sodass es zu ressourcenintensiv (und umständlich) wäre, eine dedizierte Sequenz zur Unterstützung jedes einzelnen Ersatzschlüssels zu erstellen. Ein einzelnes Sequenzobjekt kann mehrere oder sogar alle Ersatzschlüssel unterstützen.

Wenn andererseits mehrere Prozesse auf NEXTVAL (den nächsten inkrementellen Wert) aus einer Sequenz zugreifen müssen, wird die Sequenz zu einer kritischen Einzelzugriffsressource. Es wird alle Prozesse, die darauf zugreifen, streng sequentiell machen und jede Multithread-Implementierung (Einzel- oder Multi-Server) in einen Singlethread-Prozess mit langen Wartezeiten und hoher Speicher-/niedriger CPU-Auslastung verwandeln.

Solche Implementierungen kommen tatsächlich vor. Die Lösung für dieses Problem besteht darin, das betreffende Sequenzobjekt mit einem angemessenen Cache-Wert zu definieren – was bedeutet, dass ein definierter Wertebereich (sei es 100 oder 100.000) für einen aufrufenden Prozess in einen Cache ausgewählt und im verwendeten Data Dictionary aufgezeichnet wird , und werden für diesen bestimmten Prozess verfügbar, ohne dass bei jedem Aufruf von NEXTVAL auf das Datenwörterbuch zugegriffen werden muss.

Aber genau aus diesem Grund werden Lücken entstehen, da wahrscheinlich nicht alle zwischengespeicherten Werte verwendet werden. Dies bedeutet auch, dass über mehrere Prozesse in parallelen Sitzungen hinweg einige aufgezeichnete Sequenzwerte chronologisch invertiert werden können. Diese Umkehrung kann nicht innerhalb eines einzelnen Prozesses erfolgen, es sei denn, ein Sequenzwert wurde zurückgesetzt oder zurückversetzt. Aber dieses letzte Szenario läuft darauf hinaus, nach Ärger zu suchen: Es sollte unnötig sein, und wenn es falsch implementiert wird, kann es dazu führen, dass doppelte Werte generiert werden.

Der einzig richtige Weg, Oracle-Sequenzen zu verwenden, ist die Generierung von Ersatzschlüsseln: Schlüssel, die eindeutig sind, von denen jedoch nicht angenommen wird, dass sie andere zuverlässig nutzbare Informationen enthalten.

Identitätsspalten in SQL Server

Was ist mit SQL-Server? Obwohl in SQL Server 2012 Sequenzen mit sehr ähnlicher Funktionalität und Implementierung wie ihr Oracle-Pendant eingeführt wurden, sind sie keine erstklassige Go-to-Technik. Wie andere Feature-Ergänzungen sind sie für die Konvertierung von Oracle sinnvoll, aber wenn Ersatzschlüssel von Grund auf auf SQL Server implementiert werden, ist IDENTITY eine viel bessere Option.

IDENTITY ist ein „untergeordnetes“ Objekt einer Tabelle. Es greift auf keine Ressourcen außerhalb einer Tabelle zu und ist garantiert sequentiell, es sei denn, es wird absichtlich manipuliert. Und es wurde speziell für genau diese Aufgabe entwickelt und nicht für die semantische Kompatibilität mit Oracle.

Da Oracle die IDENTITY Funktionalität in Version 12.1 implementiert hat, ist es natürlich, sich zu fragen, wie es früher darauf verzichtet hat, warum es sie jetzt implementiert hat und warum SQL Server sie von Anfang an benötigt hat (seit seinen Sybase SQL Server-Ursprüngen).

Der Grund dafür ist, dass Oracle immer ein Identitätsschlüsselmerkmal hatte: die ROWID mit einem Datentyp von ROWID oder UROWID . Dieser Wert ist nicht numerisch ( ROWID und UROWID sind proprietäre Oracle-Datentypen) und identifiziert einen Datensatz eindeutig.

Im Gegensatz zu ROWID von SQL Server kann die IDENTITY von Oracle nicht einfach manipuliert werden (sie kann abgefragt, aber nicht eingefügt oder geändert werden), und sie wird im Hintergrund für jede Zeile in jeder Oracle-Tabelle erstellt. Außerdem ist der effizienteste Weg, um auf eine Datenzeile in einer Oracle-Datenbank zuzugreifen, über ihre ROWID , daher wird sie als Technik zur Leistungsoptimierung verwendet. Schließlich definiert es die standardmäßige Sortierreihenfolge der Abfrageausgabe, da es die Low-Level-Speicherung von Zeilendaten effektiv indiziert.

Wenn die ROWID von Oracle so wichtig ist, wie hat SQL Server dann all die Jahre und Veröffentlichungen ohne sie überstanden? Durch die Verwendung von IDENTITY Spalten als primäre (Ersatz-)Schlüssel.

Es ist wichtig, den Unterschied in der Implementierung der Indexstruktur zwischen Oracle und SQL Server zu beachten.

In SQL Server ist der erste Index – meistens der Primärschlüssel – geclustert; Dies bedeutet, dass Daten in der primären Datendatei am häufigsten nach diesem Schlüssel geordnet werden. Auf der Oracle-Seite ist das Äquivalent zu einem Clustered-Index eine indexorganisierte Tabelle. Dies ist ein optionales Konstrukt in Oracle, das sporadisch nur bei Bedarf verwendet wird – beispielsweise für schreibgeschützte Nachschlagetabellen.

Alle Entwurfsmuster in Oracle, die auf der Verwendung von ROWID (z. B. Datendeduplizierung), sollten bei der Migration zu SQL Server basierend auf IDENTITY Spalten implementiert werden.

Während die Migration von der Verwendung von IDENTITY auf SQL Server zur Verwendung von IDENTITY auf Oracle funktional korrekten Code erzeugen könnte, ist dies nicht optimal, da ROWID auf der Oracle-Seite viel effizienter ausgeführt wird.

Das Gleiche gilt, wenn Sie eine einfache SQL-Syntaxkonvertierung durchführen, um Oracle-Sequenzen in SQL Server zu verschieben: Der Code wird ausgeführt, aber die Verwendung von IDENTITY ist die bevorzugte Option sowohl im Hinblick auf die Einfachheit des Codes als auch auf die Leistung.

Gefilterte Indizes in Microsoft SQL Server

Vor Jahren führte Microsoft SQL Server 2008 eine Reihe bedeutender Funktionen ein, die es zu einer wirklich erstklassigen Unternehmensdatenbank machten. Eine, die mir mehr als einmal den Tag gerettet hat, waren gefilterte Indizes.

Ein gefilterter Index ist ein nicht geclusterter Index (dh einer, der als eigene Datendatei existiert), der eine WHERE -Klausel hat. Das bedeutet, dass die Indexdatei nur Datensätze enthält, die für die Klausel relevant sind. Um gefilterte Indizes voll auszunutzen, sollte es auch eine INCLUDE -Klausel haben, die alle Spalten auflistet, die beim Zurückgeben eines Datensatzes benötigt werden. Wenn Ihre Abfrage so optimiert ist, dass sie einen bestimmten gefilterten Index verwendet, der alle erforderlichen Datenpunkte enthält, muss die Datenbank-Engine nur auf eine (kleine) Indexdatei zugreifen, ohne sich die primäre Tabellendatendatei anzusehen.

Das war vor ein paar Jahren besonders wertvoll für mich, als ich mit einer Terabyte-großen Tabelle arbeitete. Der betreffende Client musste häufig nur auf einen Bruchteil eines Prozents der zu einem bestimmten Zeitpunkt aktiven Datensätze zugreifen. Die anfängliche Implementierung dieses Zugriffs (ausgelöst durch UI-Aktionen des Endbenutzers) war nicht nur quälend langsam, sondern einfach unbrauchbar. Als ich einen gefilterten Index mit den benötigten INCLUDE s hinzufügte, wurde es zu einer Sub-Millisekunden-Suche. Die Zeit, die ich für diese Optimierungsaufgabe aufgewendet habe, betrug nur eine Stunde.

Sicher, gefilterte Indizes haben einige Einschränkungen. Sie können keine LOB-Spalten enthalten, es gibt Einschränkungen hinsichtlich der Bedingungen, die die WHERE -Klauseln enthalten können, die die Indizes selbst enthalten können, und sie erhöhen den Speicherbedarf einer Datenbank. Aber vorausgesetzt, ein Anwendungsfall passt in diese Parameter, sind die Speicherkompromisse im Vergleich zu der erheblichen Leistungssteigerung, die gefilterte Indizes bieten können, in der Regel recht gering.

Was ist mit gefilterten Indizes in der Oracle-Datenbank?

Später fand ich mich in einem großen Team bei einem Fortune-500-Unternehmen als Entwickler/DBA bei einem Migrationsprojekt von SQL Server zu Oracle wieder. Der Code rund um die Quelldatenbank – SQL Server 2008 – war schlecht implementiert, mit magerer Leistung, die eine Konvertierung zwingend erforderlich machte: Der tägliche Back-End-Synchronisierungsjob lief länger als 23 Stunden. Es hatte keine gefilterten Indizes, aber im neuen Oracle 11g-System habe ich mehrere Fälle gesehen, in denen gefilterte Indizes sehr vorteilhaft wären. Aber Oracle 11g hat keine gefilterten Indizes!

Auch im neuesten Oracle 18c sind gefilterte Indizes nicht implementiert.

Aber unsere Aufgabe als technische Profis ist es, das Beste aus dem zu machen, was wir haben. Also habe ich das Äquivalent gefilterter Indizes in meinem Oracle 11g-System implementiert (und die gleiche Technik, die ich später in 12c verwendet habe). Die Idee basiert darauf, wie Oracle mit NULL s umgeht, und kann in jeder Version von Oracle verwendet werden.

Oracle behandelt einen NULL -Wert nicht wie normale Daten. Eine NULL ist in Oracle nichts – sie existiert nicht. Wenn Sie also Ihre indizierte Spalte als NULLABLE definieren und nach Nicht- NULL -Werten suchen, enthält Ihre Indexdatendatei nur Datensätze von Interesse. Da eine Oracle-Indexdefinition keine INCLUDE Klausel hat, müssen Sie einen zusammengesetzten Index mit allen Spalten erstellen, die in einer Ergebnismenge enthalten sein müssen. (Diese Technik hat im Vergleich zur INCLUDE -Klausel von SQL Server einen gewissen Overhead, ist aber relativ unbedeutend.)

Eine solche Workaround-Implementierung fügt eine Einschränkung hinzu: Die führende Indexspalte muss NULL s zulassen und kann daher nicht der Primärschlüssel der Tabelle sein. Es kann sich jedoch um eine abgeleitete oder berechnete Spalte handeln, die speziell zur Unterstützung dieser Methode zur Leistungsoptimierung erstellt wurde. In gewissem Sinne ist die führende Spalte des Index logisch binär: Nicht- NULL -Werte für Daten, die in Ihrer Suche enthalten sind, und NULL für alle Daten, die „unsichtbar“ sein sollten.

Die andere mögliche Option bei der Migration der gefilterten Indexlogik von SQL Server zu Oracle besteht darin, einen Index (oder die vollständige Tabelle) als partitioniert zu implementieren. In diesem Fall greift die Datenbank-Engine nur auf die relevante Indexpartition zu – vorausgesetzt, Abfragen werden korrekt implementiert, indem die exakte Partitionierungsbedingung in ihren WHERE -Klauseln verwendet wird.

Dies funktioniert selbst im großen Maßstab bei relativ statischen Daten gut, kann jedoch eine hohe Wartungslast für ein DBA-Team bedeuten, wenn es auf sich häufig ändernde Daten angewendet wird. Ein Beispiel wäre die Optimierung des Zugriffs auf die heutigen Daten in einer zeitorientierten Anwendung: Das DBA-Team muss Partitionen täglich neu definieren. Diese Neudefinition kann zwar in einem nächtlichen Wartungsjob per Skript ausgeführt werden, macht Ihr System jedoch komplexer und führt zu neuen potenziellen systemischen Fehlerpunkten.

Daher muss man sehr spezifisch und vorsichtig sein, wenn die gefilterte Indexlogik von SQL Server zu Oracle migriert werden muss.

Umgang mit Konvertierungen

Suchen Sie bei einer Migration von Oracle zu SQL Server mithilfe von gefilterten Indizes nach Optimierungsmöglichkeiten. In Oracle werden keine gefilterten Indizes angezeigt, aber möglicherweise Indizes, die NULL Werte enthalten. Kopieren Sie sie nicht so, wie sie sind: Es ist möglicherweise der beste Ort, an dem Sie eine Leistungssteigerung und Designverbesserung in Ihrer Konvertierung erzielen können.

Wenn Sie bei Migrationen von SQL Server zu Oracle gefilterte Indizes sehen, suchen Sie nach Möglichkeiten, einen Leistungsengpass in Ihrem entsprechenden Oracle-Code zu vermeiden. Erfahren Sie, wie Sie den Datenfluss umgestalten können, um die fehlende Leistungssteigerung auszugleichen, die gefilterte Indizes in der Quellimplementierung bewirkt hatten.

Migrationsherausforderungen von SQL Server zu Oracle / Oracle zu SQL Server entmystifiziert

Für Migrationsprojekte zwischen Oracle und SQL Server in beide Richtungen ist es wichtig, ein tieferes Verständnis der beteiligten Mechanismen zu haben. Wenn die aktuellen Versionen der jeweiligen Datenbanken (Oracle 18c und Microsoft SQL Server 2017*) lexikalische Äquivalente der Funktionalität des jeweils anderen enthalten – z. B. in Sequenzen und Identität –, mag es wie ein einfacher Gewinn erscheinen. Aber das Kopieren eines guten Designs von einem RDBMS direkt auf das andere kann zu einem unnötig komplizierten und schlecht funktionierenden Code führen.

Im nächsten und letzten Teil dieser Serie behandle ich die Lesekonsistenz und die Verwendung von Migrationstools. Bleib dran!

* SQL Server 2019 (oder „15.x“) ist noch nicht lange genug für eine breite Einführung in Unternehmen erhältlich.