Oracle to SQL Server i SQL Server to Oracle Migration Guide - Pt. 2

W pierwszej części tej serii omówiono różnice między Oracle Database i Microsoft SQL Server w implementacji transakcji, skupiając się na pułapkach, jakie można napotkać podczas migracji z Oracle do SQL Server i odwrotnie. Ta następna część obejmie szereg powszechnie używanych elementów składni SQL, które nie pasują do siebie lub mają zupełnie inne znaczenie lub zastosowanie w podziale Oracle–SQL Server.

Sekwencje w Oracle i kolumnach tożsamości w SQL Server

W społeczności baz danych od dawna istnieje podział na dwa obozy: patriotów kluczy naturalnych i zwolenników kluczy sztucznych (lub „zastępczych”).

Sam bronię naturalnych kluczy, ale często z tego czy innego powodu tworzę zastępcze. Pozostawiając jednak na boku istotę tej debaty, spójrzmy na standardowe mechanizmy generowania sztucznych kluczy: sekwencje Oracle i kolumny tożsamości SQL Server.

Sekwencja Oracle jest pierwszorzędnym obiektem na poziomie bazy danych. Natomiast kolumna tożsamości SQL Server jest typem kolumny, a nie obiektem.

Gdy sekwencja Oracle jest używana do generowania klucza tabeli — zwykle klucza podstawowego — jest gwarantowana inkrementacja, a zatem jest unikalna. Ale nie ma gwarancji, że będzie następować po sobie. W rzeczywistości nawet w dobrze zaprojektowanych implementacjach najprawdopodobniej będzie zawierał pewne luki. Dlatego żadna implementacja Oracle nie powinna nigdy opierać się na kolejnych wartościach generowanych przez sekwencję.

Ponadto sekwencja jest zarządzana za pomocą słownika danych bazy danych Oracle, więc utworzenie dedykowanej sekwencji obsługującej każdy klucz zastępczy byłoby zbyt pracochłonne (i kłopotliwe). Pojedynczy obiekt sekwencji może obsługiwać wiele lub nawet wszystkie klucze zastępcze.

Z drugiej strony, gdy wiele procesów potrzebuje dostępu do NEXTVAL (następnej wartości przyrostowej) z sekwencji, sekwencja stanie się krytycznym zasobem o jednym dostępie. Skutecznie sprawi, że wszystkie procesy uzyskujące do niego dostęp będą ściśle sekwencyjne, zmieniając dowolną implementację wielowątkową (pojedynczą lub wieloserwerową) w proces jednowątkowy, z długimi czasami oczekiwania i wysokim zużyciem pamięci/niskim procesorem.

Takie wdrożenia rzeczywiście się zdarzają. Rozwiązaniem tego problemu jest zdefiniowanie danego obiektu sekwencji z rozsądną wartością pamięci podręcznej — co oznacza, że ​​określony zakres wartości (może to być 100 lub 100 tysięcy) jest wybierany do pamięci podręcznej dla procesu wywołującego, zapisywany w słowniku danych jako używany i stają się dostępne dla tego konkretnego procesu bez konieczności dostępu do słownika danych za każdym razem, gdy wywoływana jest NEXTVAL .

Ale właśnie dlatego będą tworzone luki, ponieważ prawdopodobnie nie wszystkie wartości z pamięci podręcznej zostaną użyte. Oznacza to również, że w wielu procesach w sesjach równoległych niektóre zarejestrowane wartości sekwencji można odwrócić chronologicznie. To odwrócenie nie może nastąpić w ramach jednego procesu, chyba że wartość sekwencji została zresetowana lub przesunięta w tył. Ale ten ostatni scenariusz sprowadza się do szukania kłopotów: powinien być niepotrzebny, a jeśli zostanie niepoprawnie zaimplementowany, może skutkować generowaniem zduplikowanych wartości.

Tak więc jedynym prawidłowym sposobem użycia sekwencji Oracle jest generowanie kluczy zastępczych: kluczy, które są unikalne, ale nie zakłada się, że przechowują jakiekolwiek inne wiarygodne informacje.

Kolumny tożsamości w SQL Server

A co z SQL Serverem? Chociaż sekwencje o bardzo podobnej funkcjonalności i implementacji do ich odpowiedników Oracle zostały wprowadzone w SQL Server 2012, nie są to pierwszorzędna technika, do której można przejść. Podobnie jak inne dodatki do funkcji, mają sens w przypadku konwersji z Oracle, ale przy wdrażaniu kluczy zastępczych od podstaw w SQL Server, IDENTITY jest znacznie lepszą opcją.

IDENTITY to obiekt „dzieci” tabeli. Nie ma dostępu do zasobów poza tabelą i gwarantuje sekwencyjne, chyba że celowo manipuluje. I jest specjalnie zaprojektowany do tego właśnie zadania, a nie do semantycznej zgodności z Oracle.

Ponieważ firma Oracle zaimplementowała funkcję IDENTITY w wersji 12.1, naturalne jest zastanawianie się, jak radziła sobie bez niej wcześniej, dlaczego zaimplementowała ją teraz i dlaczego SQL Server potrzebował jej od samego początku (od początków Sybase SQL Server).

Powodem jest to, że Oracle zawsze posiadał kluczową cechę tożsamości: pseudokolumnę ROWID o typie danych ROWID lub UROWID . Ta wartość nie jest liczbowa ( ROWID i UROWID to zastrzeżone typy danych Oracle) i jednoznacznie identyfikuje rekord danych.

W przeciwieństwie do IDENTITY SQL Server, ROWID Oracle nie można łatwo manipulować (można go odpytywać, ale nie można go wstawiać ani modyfikować) i jest tworzony w tle dla każdego wiersza w każdej tabeli Oracle. Ponadto najbardziej wydajnym sposobem uzyskania dostępu do dowolnego wiersza danych w bazie danych Oracle jest jego ROWID , więc jest on używany jako technika optymalizacji wydajności. Na koniec definiuje domyślną kolejność sortowania danych wyjściowych zapytania, ponieważ skutecznie indeksuje przechowywanie danych wierszy na niskim poziomie.

Jeśli ROWID Oracle jest tak ważny, jak SQL Server przetrwał te wszystkie lata i wydania bez niego? Używając kolumn IDENTITY jako kluczy podstawowych (zastępczych).

Należy zwrócić uwagę na różnicę w implementacji struktury indeksu między Oracle a SQL Server.

W SQL Server pierwszy indeks — najczęściej klucz podstawowy — jest klastrowany; oznacza to, że najczęściej dane w podstawowym pliku danych są uporządkowane według tego klucza. Po stronie Oracle odpowiednikiem indeksu klastrowego jest tabela zorganizowana według indeksu. Jest to opcjonalna konstrukcja w Oracle, która jest używana sporadycznie, tylko w razie potrzeby — na przykład w przypadku tabel przeglądowych tylko do odczytu.

Wszystkie wzorce projektowe w Oracle, które są oparte na użyciu ROWID (takie jak deduplikacja danych), powinny być zaimplementowane w oparciu o kolumny IDENTITY podczas migracji do SQL Server.

Chociaż migracja z używania IDENTITY na SQL Server do używania IDENTITY na Oracle może generować funkcjonalnie poprawny kod, nie jest to optymalne, ponieważ po stronie Oracle ROWID będzie działał znacznie wydajniej.

To samo dotyczy prostej konwersji składni SQL w celu przeniesienia sekwencji Oracle do SQL Server: kod zostanie uruchomiony, ale użycie IDENTITY jest preferowaną opcją zarówno pod względem prostoty kodu, jak i wydajności.

Filtrowane indeksy w Microsoft SQL Server

Wiele lat temu Microsoft SQL Server 2008 wprowadził szereg istotnych funkcji, które przekształciły go w naprawdę pierwszorzędną bazę danych dla przedsiębiorstw. Ten, który uratował mi dzień więcej niż raz, został przefiltrowany indeksami.

Indeks filtrowany to indeks nieklastrowy (tzn. taki, który istnieje jako własny plik danych), który zawiera klauzulę WHERE . Oznacza to, że plik indeksu zawiera tylko rekordy danych istotne dla klauzuli. Aby w pełni wykorzystać filtrowane indeksy, powinien on również zawierać klauzulę INCLUDE , która zawiera listę wszystkich kolumn, które są potrzebne podczas zwracania zestawu danych. Gdy zapytanie jest zoptymalizowane pod kątem użycia określonego filtrowanego indeksu, który zawiera wszystkie potrzebne punkty danych, aparat bazy danych musi tylko uzyskać dostęp do (małego) pliku indeksu, nawet nie zaglądając do pliku danych tabeli podstawowej.

Było to dla mnie szczególnie cenne kilka lat temu podczas pracy z tabelą wielkości terabajtów. Klient, o którym mowa, często potrzebował dostępu tylko do ułamka procenta rekordów aktywnych w danym momencie. Początkowa implementacja tego dostępu (wywoływana przez działania interfejsu użytkownika końcowego) była nie tylko boleśnie powolna — była po prostu bezużyteczna. Kiedy dodałem filtrowany indeks z potrzebnymi INCLUDE s, stało się to wyszukiwaniem poniżej milisekundy. Czas, który spędziłem na tym zadaniu optymalizacyjnym, to tylko godzina.

Jasne, filtrowane indeksy mają pewne ograniczenia. Nie mogą zawierać kolumn LOB, istnieją ograniczenia dotyczące warunków klauzul WHERE , które mogą zawierać same indeksy, i dodają one do miejsca przechowywania bazy danych. Ale pod warunkiem, że przypadek użycia mieści się w tych parametrach, kompromisy w zakresie pamięci masowej są zwykle dość niewielkie w porównaniu do znacznego zwiększenia wydajności, jakie mogą zapewnić filtrowane indeksy.

A co z indeksami filtrowanymi w bazie danych Oracle?

Później znalazłem się w dużym zespole w firmie z listy Fortune 500 jako programista/DBA w projekcie migracji z SQL Server do Oracle. Kod otaczający źródłową bazę danych — SQL Server 2008 — był słabo zaimplementowany, a jego wydajność była słaba, co spowodowało konieczność konwersji: codzienne zadanie synchronizacji zaplecza trwało dłużej niż 23 godziny. Nie miał filtrowanych indeksów, ale w nowym systemie — Oracle 11g — widziałem wiele przypadków, w których filtrowane indeksy byłyby bardzo korzystne. Ale Oracle 11g nie ma filtrowanych indeksów!

Indeksy filtrowane nie są również zaimplementowane w najnowszej wersji Oracle 18c.

Ale naszym zadaniem jako profesjonalistów technicznych jest jak najlepsze wykorzystanie tego, co mamy. Zaimplementowałem więc odpowiednik filtrowanych indeksów w moim systemie Oracle 11g (i tę samą technikę, której użyłem później w 12c). Pomysł opiera się na tym, jak Oracle obsługuje wartości NULL i może być używany w dowolnej wersji Oracle.

Oracle nie traktuje wartości NULL tak samo jak zwykłych danych. NULL w Oracle jest niczym — nie istnieje. W rezultacie, jeśli zdefiniujesz swoją indeksowaną kolumnę jako NULLABLE i wyszukujesz według wartości innych niż NULL , plik danych indeksu będzie zawierał tylko interesujące rekordy. Ponieważ definicja indeksu Oracle nie zawiera klauzuli INCLUDE , konieczne będzie utworzenie indeksu złożonego ze wszystkimi kolumnami, które należy uwzględnić w zestawie wyników. (Ta technika wiąże się z pewnym obciążeniem w porównaniu z klauzulą INCLUDE SQL Server, ale jest dość nieistotna).

Taka implementacja obejściowa wprowadza ograniczenie: wiodąca kolumna indeksu musi dopuszczać wartości NULL i dlatego nie może być kluczem podstawowym tabeli. Może to być jednak kolumna pochodna lub obliczeniowa utworzona specjalnie do obsługi tej metody optymalizacji wydajności. W pewnym sensie wiodąca kolumna indeksu jest logicznie binarna: wartości inne niż NULL dla danych uwzględnionych w wyszukiwaniu i NULL dla wszelkich danych, które powinny być „niewidoczne”.

Inną możliwą opcją migracji logiki filtrowanego indeksu SQL Server do Oracle jest zaimplementowanie indeksu (lub całej tabeli) jako podzielonego na partycje. W takim przypadku aparat bazy danych będzie miał dostęp tylko do odpowiedniej partycji indeksu — pod warunkiem, że zapytania są implementowane poprawnie przy użyciu dokładnego warunku partycjonowania w klauzulach WHERE .

Będzie to działać dobrze, nawet na dużą skalę, na stosunkowo statycznych danych, ale może spowodować duże obciążenie konserwacyjne zespołu administratorów baz danych, jeśli zostanie zastosowane do często zmieniających się danych. Przykładem może być optymalizacja dostępu do dzisiejszych danych w aplikacji zorientowanej na czas: zespół DBA będzie musiał codziennie przedefiniowywać partycje. Chociaż ta redefinicja może być zapisana w skrypcie podczas nocnej konserwacji, to czyni system bardziej złożonym i wprowadza nowe potencjalne punkty awarii systemowych.

Tak więc trzeba być bardzo konkretnym i ostrożnym, gdy logika filtrowanego indeksu SQL Server musi być migrowana do Oracle.

Jak obsługiwać konwersje

W przypadku migracji z Oracle do SQL Server poszukaj możliwości optymalizacji przy użyciu filtrowanych indeksów. Nie zobaczysz filtrowanych indeksów w Oracle, ale możesz zobaczyć indeksy zawierające wartości NULL . Nie kopiuj ich tak, jak są: może to być najlepsze miejsce, w którym możesz uzyskać wzrost wydajności i poprawić projekt konwersji.

W przypadku migracji SQL Server do Oracle, jeśli widzisz filtrowane indeksy, poszukaj sposobu uniknięcia wąskiego gardła wydajności w odpowiednim kodzie Oracle. Zobacz, jak przeprojektować przepływ danych, aby zrekompensować brakujący wzrost wydajności, który przyniosły filtrowane indeksy w implementacji źródłowej.

Wyzwania związane z migracją SQL Server do Oracle / Oracle do SQL Server Demystified

W przypadku projektów migracji między Oracle i SQL Server w obu kierunkach ważne jest głębsze zrozumienie zaangażowanych mechanizmów. Gdy obecne wersje odpowiednich baz danych (Oracle 18c i Microsoft SQL Server 2017*) zawierają leksykalne odpowiedniki swoich wzajemnych funkcji — np. w sekwencjach i tożsamości — może się to wydawać łatwe. Jednak kopiowanie dobrego projektu z jednego RDBMS bezpośrednio do drugiego może skutkować niepotrzebnie skomplikowanym i mało wydajnym kodem.

W kolejnej i ostatniej części tej serii zajmę się spójnością odczytu i wykorzystaniem narzędzi migracyjnych. Bądźcie czujni!

* SQL Server 2019 (lub „15.x”) nie był dostępny wystarczająco długo, aby można było go powszechnie stosować w przedsiębiorstwach.