Z Oracle do PostgreSQL: Krótki przewodnik techniczny

Migracja struktur danych

Pierwszą rzeczą do zapoznania się jest to, że PostgreSQL ma o wiele więcej typów danych niż Oracle. Dlatego ważne jest, aby dokonywać właściwych wyborów. Wybór odpowiedniego typu danych może mieć ogromny wpływ na wydajność.

Jeśli nie jesteście pewni, jak mapować typy danych Oracle do PostgreSQL, przygotowaliśmy macierz konwersji. Macierz pokazuje, które typy można zmapować do odpowiedniego odpowiednika.

Najlepszą decyzją byłoby przejście na typ danych, który zapewnia najmniejszy narzut. Co to znaczy? Oto przykład: number(10, 0) można zmapować do numeric(10, 0) ale możecie też go zmapować do int8. Używanie int8 jest zwykle dużo bardziej wydajne. Int8 z pewnością zjada 8 bajtów – jednak obliczenia są znacznie szybsze, jeśli używacie int8 rather zamiast liczb.

varchar2 można mapować bezpośrednio do varchar lub do typu danych text. Jednak uznaliśmy za przydatne rozważenie rozszerzenia „citext” w PostgreSQL. Ideą citext jest posiadanie typu danych tekstowych niewrażliwych na wielkość liter, co sprawia, że wyszukiwanie jest o wiele wygodniejsze. W aplikacjach internetowych wyszukiwanie bez rozróżniania wielkości liter jest zwykle tym, czego szukacie. Jeśli generalnie interesuje Państwa wyszukiwanie rozmyte, zapraszamy do przeczytania naszego wpisu na blogu poświęconego temu wyjątkowo ważnemu tematowi. Migracja jest zwykle dobrą okazją do uporządkowania i poprawy sytuacji

BLOBs są również problemem, o który wiele osób pyta. Ogólnie PostgreSQL oferuje typ danych bytea (“byte array”). Wygląda jak kolumna tekstowa, ale w rzeczywistości może przechowywać dane binarne. Jeśli nie chcecie używać kolumny do przechowywania obiektów BLOB, można również bezpośrednio użyć interfejsu PostgreSQL BLOB.

UWAGA: Należy tutaj wspomnieć, że Oracle Spatial można odwzorować na PostGIS oraz PostgreSQL. Więc jeśli używasz danych GIS, istnieje również sposób na migrację do PostgreSQL. W tym przypadku również nie ma praktycznych ograniczeń.

Aby automatycznie mapować typy danych, zachęcamy rozważenie skorzystania z CYBERTEC Migrator. To narzędzie posiada zaawansowane możliwości mapowania typów danych i oferuje łatwy sposób przewidywania najlepszego możliwego typu danych do przechowywania dużych ilości danych w PostgreSQL.

CREATE SYNONYM: Brakujące piękno

Oracle zapewnia polecenie CREATE SYNONYM, które nie jest dostępne w PostgreSQL. Jakiś czas temu w CYBERTEC zaimplementowaliśmyCREATE SYNONYM dla PostgreSQL, ale ta modyfikacja została odrzucona, ponieważ nie pasuje do ogólnej koncepcji PostgreSQL.

W rezultacie trzeba zastosować obejście:

• Synonimy tabel: zamiast tego użyć widoku (SELECT * …)
• Synonimy widoków: użyć widoku u góry widoku (SELECT * …)
• Synonimy funkcji/procedur: użyć funkcji opakowujących
• Synonimy dla widoków zmaterializowanych: użyć widoków

Ogólnie rzecz biorąc, lepiej pozbyć się tych synonimów podczas migracji. To znacznie ułatwia przyszły rozwój. Ustawienie ścieżki search_path okazało się ogólnie przydatne i często ogranicza potrzebę używania synonimów w pierwszej kolejności.

Nieprawidłowe widoki w Oracle – prawidłowe widoki w PostgreSQL

Podczas migracji struktur danych z Oracle do PostgreSQL można zauważyć, że PostgreSQL używa ścisłego śledzenia zależności. Pozwólcie, że podam przykład: jeśli tabela zostanie usunięta, PostgreSQL automatycznie usunie wszystkie zależne od niej obiekty. W Oracle tak nie jest. Być może trzeba będzie poradzić sobie z nieprawidłowymi widokami, co jest problemem, o którym warto wiedzieć podczas projektu migracji.

Konwersja typowych instrukcji SQL

Oracle ma pewne specjalizacje w swoim smaku SQL, które należy wziąć pod uwagę podczas migracji kodu z Oracle do PostgreSQL. W tej sekcji przedstawiono niektóre z najczęstszych problemów napotykanych podczas migracji.

Przepisywanie złączeń dla PostgreSQL

Oracle używa innej składni dla sprzężeń zewnętrznych niż reszta świata. Należy o tym pamiętać podczas konwersji kodu z Oracle do PostgreSQL. Poniższy przykład pokazuje, jak to działa.

Oracle

SELECT b.col1, a.col2
FROM base_table b, attributes a
WHERE b.id=a.b_id(+);

musi zostać przetłumaczone na

PostgreSQL

SELECT b.col1, a.col2
FROM base_table b
LEFT JOIN attributes a ON b.id = a.b_id;

Zmiana jest właściwie łatwa, ale dość denerwująca.

Podselekcje i aliasy

W Oracle podselekcja nie potrzebuje nazwy. Może być „anonimowa”:

SELECT * FROM (SELECT * FROM some_table)

Można to łatwo przepisać w PostgreSQL. Poniższy kod pokazuje, jak faktycznie wyglądałby odpowiednik w pracy Open Source:

SELECT * FROM (SELECT * FROM some_table) AS your_fancy_name

Obsługa NULL i pustych ciągów

Oracle traktuje NULL jako pusty ciąg. Ma to kilka konsekwencji:
'hello' || NULL nie jest NULL w Oracle. Dlatego należy go przetłumaczyć na następujący fragment SQL, aby mieć taką samą semantykę w PostgreSQL:

concat('hello', NULL)
albo użyć
coalesce(strcol, '')

Migracja instrukcji null może prowadzić do subtelnych błędów, które mogą być trudne do wyśledzenia. Należy zwracać baczną uwagę na takie szczegóły i wyraźnie je sprawdzać.

Funkcje czasowe w Oracle i PostgreSQL

Większość kodu Oracle wykorzystuje zastrzeżone funkcje, takie jak…

• SYSDATE
• SYSTIMESTAMP

Konieczne jest przetłumaczenie tych wywołań, aby odpowiadały PostgreSQL:

• clock_timestamp()
• current_date or current_timestamp

Na szczęście tę zmianę można łatwo zrobić. Wyszukiwania i zamiany zwykle wystarczy.

Migracja sekwencji do PostgreSQL

Bazy danych Oracle obsługują sekwencje zupełnie inaczej. Można zobaczyć następującą metodę tworzenia następnej wartości:

asequence.NEXTVAL

Odpowiednik PostgreSQL to:

nextval('asequence')

BObie bazy danych nie używają metody ANSI SQL do pobierania wartości z sekwencji, która wyglądałaby następująco: NEXT VALUE FOR asequence

Nie ma potrzeby DUAL

W PostgreSQL zapytanie niekoniecznie wymaga klauzuli FROM. Dobrze jest jej nie używać. Dlatego nie ma potrzeby stosowania DUAL.

SELECT * FROM dual;

… Nie jest konieczne w PostgreSQL. orafce może symulować DUAL, ale lepiej unikać tego po stronie PostgreSQL. Po prostu nie ma takiej potrzeby.

DECODE: Wszechmogący

DECODE to rzecz specyficzna dla Oracle:

decode(expr, search, result [, search, result...] [, default])

Sposobem na zmianę rzeczy w PostgreSQL jest…

• Zastąpić CASE / WHEN
• Zastąp funkcją “decode” udostępnianą przez orafce

Zwykły PostgreSQL działa w następujący sposób:

CASE WHEN expr THEN expr [...] ELSE expr END

Ogólnie, DECODE można łatwo zastąpić. Nie zawiera złożonej logiki.

Obsługa rekurencji: CONNECT BY

W Oracle istnieją dwa sposoby obsługi rekursji:

WITH RECURSIVE: Nowa składnia
CONNECT BY: Stara składnia

PostgreSQL ma pełną implementację WITH RECURSIVE. Stara CONNECT BY składnia jest wysoce specyficzna dla Oracle i nie może być używana w PostgreSQL (ani w żadnym innym silniku bazy danych SQL, o którym wiemy).

Oto przykład pokazujący, jak CONNECT BY można przetłumaczyć na WITH RECURSIVE:


SELECT empno, ename, level
FROM emp
START WITH empno = 7788
CONNECT BY PRIOR mgr = empno;

… kontra…

WITH hierarchy (empno, ename, mgr, "level") AS (
SELECT empno, ename, mgr, 1 AS "level"
FROM emp
WHERE empno = 7788
UNION ALL
SELECT e.empno, e.ename, e.mgr, h."level" + 1
FROM hierarchy h
JOIN emp e ON h.mgr = e.empno
) SELECT empno, ename, "level" FROM hierarchy;