Ghid de migrare Oracle la SQL Server și SQL Server la Oracle - Pt. 2

Prima parte a acestei serii a discutat despre diferențele dintre Oracle Database și Microsoft SQL Server în implementarea tranzacțiilor, cu accent pe capcanele pe care le puteți întâlni în timpul migrărilor Oracle la SQL Server și viceversa. Această tranșă următoare va acoperi o serie de elemente de sintaxă SQL utilizate în mod obișnuit, care nu se potrivesc sau au un înțeles sau o utilizare complet diferită în diviziunea Oracle-SQL Server.

Secvențe în Oracle și coloane de identitate în SQL Server

Există o diviziune de lungă durată în comunitatea bazei de date între două tabere: patrioții cheilor naturale și susținătorii cheilor artificiale (sau „surogat”).

Eu însumi apăr cheile naturale, dar adesea mă trezesc să creez surogate dintr-un motiv sau altul. Dar, lăsând deoparte substanța acestei dezbateri, să ne uităm la mecanismele standard de generare a cheilor artificiale: secvențe Oracle și coloane de identitate SQL Server.

O secvență Oracle este un obiect de primă clasă la nivel de bază de date. În schimb, o coloană de identitate SQL Server este un tip de coloană, nu un obiect.

Atunci când o secvență Oracle este utilizată pentru a genera o cheie de tabel - de obicei o cheie primară - este garantat să se crească și, prin urmare, să fie unică. Dar nu este garantat să fie consecutiv. De fapt, chiar și în implementările bine concepute, este cel mai probabil să aibă unele lacune. Deci, nicio implementare Oracle nu ar trebui să se bazeze vreodată pe valorile generate de secvențe pentru a fi consecutive.

De asemenea, o secvență este gestionată prin dicționarul de date al bazei de date Oracle, așa că ar fi prea consumator de resurse (și greoi) pentru a crea o secvență dedicată pentru a suporta fiecare cheie surogat. Un singur obiect secvență poate suporta mai multe sau chiar toate cheile surogat.

Pe de altă parte, atunci când mai multe procese trebuie să acceseze NEXTVAL (următoarea valoare incrementală) dintr-o secvență, secvența va deveni o resursă critică, cu acces unic. Va face ca toate procesele care îl accesează să fie strict secvenţiale, transformând orice implementare cu mai multe fire (single sau multi-server) într-un proces cu un singur thread, cu timpi lungi de aşteptare şi memorie mare/utilizare redusă a CPU.

Astfel de implementări chiar se întâmplă. Soluția pentru această problemă este de a defini obiectul secvență în cauză cu o valoare de cache rezonabilă - ceea ce înseamnă că o gamă definită de valori (fie 100 sau 100 de mii) sunt selectate într-un cache pentru un proces de apelare, înregistrate în dicționarul de date așa cum este utilizat. , și devin disponibile pentru acest proces particular, fără a fi nevoie să accesați dicționarul de date de fiecare dată când este apelat NEXTVAL .

Dar tocmai acesta este motivul pentru care vor fi create goluri, deoarece nu toate valorile stocate în cache sunt probabil folosite. De asemenea, înseamnă că în mai multe procese în sesiuni paralele, unele valori ale secvenței înregistrate pot fi inversate cronologic. Această inversare nu poate avea loc într-un singur proces decât dacă o valoare a secvenței a fost resetată sau inversată. Dar acest ultim scenariu echivalează cu căutarea problemelor: ar trebui să fie inutil și, dacă este implementat incorect, poate duce la generarea de valori duplicate.

Așadar, singura modalitate corectă de a utiliza secvențele Oracle este generarea de chei surogat: chei care sunt unice, dar nu se presupune că conțin alte informații utilizabile în mod fiabil.

Coloane de identitate în SQL Server

Dar SQL Server? În timp ce secvențele cu funcționalitate și implementare foarte asemănătoare cu omologul lor Oracle au fost introduse în SQL Server 2012, acestea nu sunt o tehnică de primă clasă. La fel ca și alte completări de caracteristici, au sens pentru conversia din Oracle, dar atunci când implementați chei surogat de la zero pe SQL Server, IDENTITY este o opțiune mult mai bună.

IDENTITY este un obiect „copil” al unui tabel. Nu accesează resurse în afara unui tabel și este garantat să fie secvenţial dacă nu este manipulat în mod deliberat. Și este conceput special pentru această sarcină, mai degrabă decât pentru compatibilitatea semantică cu Oracle.

Întrucât Oracle a implementat funcționalitatea IDENTITY în versiunea 12.1, este firesc să ne întrebăm cum s-a descurcat fără ea înainte, de ce a implementat-o ​​acum și de ce SQL Server avea nevoie de ea de la bun început (de la originile sale Sybase SQL Server).

Motivul este că Oracle a avut întotdeauna o caracteristică cheie de identitate: pseudocoloana ROWID , având un tip de date ROWID sau UROWID . Această valoare nu este numerică ( ROWID și UROWID sunt tipuri de date proprietare Oracle) și identifică în mod unic o înregistrare de date.

Spre deosebire de IDENTITY lui SQL Server, ROWID -ul Oracle nu poate fi manipulat cu ușurință (poate fi interogat, dar nu inserat sau modificat) și este creat în fundal pentru fiecare rând din fiecare tabel Oracle. De asemenea, cea mai eficientă modalitate de a accesa orice rând de date dintr-o bază de date Oracle este prin ROWID , deci este folosit ca tehnică de optimizare a performanței. În cele din urmă, definește ordinea implicită de sortare a ieșirii interogării, deoarece indexează efectiv stocarea de nivel scăzut a datelor de rând.

Dacă ROWID -ul Oracle este atât de important, cum a supraviețuit SQL Server toți acești ani și lansări fără el? Prin utilizarea coloanelor IDENTITY ca chei primare (surogat).

Este important de remarcat diferența în implementarea structurii indexului dintre Oracle și SQL Server.

În SQL Server, primul index — cheia primară, de cele mai multe ori — este grupat; aceasta înseamnă că cel mai frecvent, datele din fișierul de date primar sunt ordonate după această cheie. Pe partea Oracle, echivalentul unui index grupat este un tabel organizat pe index. Acesta este o construcție opțională în Oracle, care este utilizată sporadic, numai după cum este necesar, de exemplu, pentru tabelele de căutare numai pentru citire.

Toate modelele de proiectare din Oracle care se bazează pe utilizarea ROWID (cum ar fi deduplicarea datelor) ar trebui implementate pe baza coloanelor IDENTITY la migrarea la SQL Server.

În timp ce migrarea de la utilizarea IDENTITY pe SQL Server la utilizarea IDENTITY pe Oracle ar putea produce cod funcțional corect, nu este optim, deoarece din partea Oracle, ROWID va funcționa mult mai eficient.

Același lucru este valabil și atunci când faceți o conversie simplă de sintaxă SQL pentru a muta secvențele Oracle în SQL Server: Codul va rula, dar utilizarea IDENTITY este opțiunea mult preferată atât în ​​ceea ce privește simplitatea codului, cât și performanța.

Indexuri filtrate în Microsoft SQL Server

Cu ani în urmă, Microsoft SQL Server 2008 a introdus o serie de caracteristici semnificative care l-au transformat într-o bază de date de întreprindere cu adevărat de prim rang. Unul care mi-a salvat ziua de mai multe ori a fost indexuri filtrate.

Un index filtrat este un index non-cluster (adică unul care există ca fișier de date propriu) care are o clauză WHERE . Înseamnă că fișierul index conține doar înregistrări de date relevante pentru clauză. Pentru a profita din plin de indecșii filtrați, ar trebui să aibă și o clauză INCLUDE care listează toate coloanele care sunt necesare la returnarea unui set de date. Când interogarea dvs. este optimizată pentru a utiliza un index filtrat specific care include toate punctele de date necesare, motorul de bază de date trebuie să acceseze doar un fișier index (mic) fără să se uite măcar la fișierul de date din tabelul primar.

Acest lucru a fost deosebit de valoros pentru mine acum câțiva ani, când lucram cu un tabel de dimensiunea unui terabyte. Clientul în cauză trebuia frecvent să acceseze doar o fracțiune dintr-un procent din înregistrările active la un moment dat. Implementarea inițială a acestui acces (declanșată de acțiunile UI utilizatorului final) nu a fost doar dureros de lentă, ci a fost pur și simplu inutilizabilă. Când am adăugat un index filtrat cu INCLUDE necesare, a devenit o căutare sub milisecundă. Timpul pe care l-am petrecut cu această sarcină de optimizare a fost de doar o oră.

Sigur, indecșii filtrați au unele limitări. Ele nu pot include coloane LOB, există limite privind condițiile clauzelor WHERE pe care indicii înșiși le pot include și se adaugă la amprenta de stocare a bazei de date. Dar cu condiția ca un caz de utilizare să se încadreze în acești parametri, compromisurile de stocare sunt de obicei destul de minore în comparație cu creșterea semnificativă a performanței pe care o pot oferi indicii filtrați.

Ce zici de indecșii filtrati în baza de date Oracle?

Mai târziu, m-am trezit într-o echipă mare la o companie Fortune 500 ca dezvoltator/DBA într-un proiect de migrare SQL Server-la-Oracle. Codul din jurul bazei de date sursă — SQL Server 2008 — a fost implementat prost, cu performanțe slabe care au făcut ca conversia să fie imperativă: sarcina zilnică de sincronizare back-end rula mai mult de 23 de ore. Nu avea indici filtrați, dar în noul sistem — Oracle 11g — am văzut mai multe cazuri în care indecșii filtrați ar fi foarte folositori. Dar Oracle 11g nu are indici filtrați!

Nici indecșii filtrați nu sunt implementați în cel mai recent Oracle 18c.

Dar sarcina noastră ca profesioniști tehnici este să folosim cât mai bine ceea ce avem. Așa că am implementat echivalentul indicilor filtrați în sistemul meu Oracle 11g (și aceeași tehnică pe care am folosit-o mai târziu în 12c). Ideea se bazează pe modul în care Oracle gestionează NULL -urile și poate fi folosită în orice versiune de Oracle.

Oracle nu tratează o valoare NULL în același mod ca datele obișnuite. Un NULL în Oracle nu este nimic - nu există. Ca rezultat, dacă definiți coloana indexată ca NULLABLE și căutați după valori non- NULL , fișierul dvs. de date index va conține numai înregistrări de interes. Deoarece o definiție de index Oracle nu are o clauză INCLUDE , va trebui să creați un index compus cu toate coloanele care trebuie incluse într-un set de rezultate. (Această tehnică are o suprasarcină în comparație cu clauza INCLUDE a SQL Server, dar este relativ nesemnificativă.)

O astfel de implementare a soluției adaugă o limitare: coloana indexului principal trebuie să permită NULL și, prin urmare, nu poate fi cheia primară a tabelului. Cu toate acestea, poate fi o coloană derivată sau calculată creată special pentru a sprijini această metodă de optimizare a performanței. Într-un anumit sens, coloana de început a indexului este logic binară: valori non- NULL pentru datele incluse în căutarea dvs. și NULL pentru orice date care ar trebui să fie „invizibile”.

Cealaltă opțiune posibilă în migrarea logicii indexului filtrat de SQL Server la Oracle este implementarea unui index (sau a tabelului în întregime) ca partiționat. În acest caz, motorul bazei de date va accesa doar partiția de index relevantă – cu condiția ca interogările să fie implementate corect utilizând condiția exactă de partiționare din clauzele WHERE ale acestora.

Acest lucru va funcționa bine, chiar și la scară, pe date relativ statice, dar poate pune o sarcină mare de întreținere pentru o echipă DBA dacă este aplicat datelor care se schimbă frecvent. Un exemplu ar fi atunci când optimizați accesul la datele de astăzi într-o aplicație centrată pe timp: echipa DBA va trebui să redefiniți partițiile în fiecare zi. Deși această redefinire poate fi scriptată într-o lucrare de întreținere nocturnă, face sistemul dvs. mai complex și introduce noi puncte potențiale de defecțiune sistemică.

Deci, trebuie să fii foarte specific și atent ori de câte ori logica de index filtrată SQL Server trebuie migrată la Oracle.

Cum să gestionați conversiile

Cu o migrare Oracle la SQL Server, căutați oportunități de optimizare folosind indecși filtrați. Nu veți vedea indecși filtrați în Oracle, dar este posibil să vedeți indecși care includ valori NULL . Nu le copiați așa cum sunt: ​​poate fi cel mai bun loc unde puteți obține o creștere a performanței și îmbunătățirea designului în conversia dvs.

Pentru migrarea SQL Server la Oracle, dacă vedeți indecși filtrați, căutați cum să evitați un blocaj de performanță în codul dvs. Oracle corespunzător. Vedeți cum puteți reproiecta fluxul de date pentru a compensa creșterea lipsă de performanță pe care indicii filtrați au dat-o în implementarea sursă.

Provocări de migrare de la SQL Server la Oracle / Oracle la SQL Server Demistificat

Pentru proiectele de migrare între Oracle și SQL Server în ambele direcții, este important să aveți o înțelegere mai profundă a mecanicilor implicate. Când versiunile curente ale bazelor de date respective (Oracle 18c și Microsoft SQL Server 2017*) conțin echivalente lexicale ale funcționalității celeilalte — de exemplu, în secvențe și identitate — ar putea părea o victorie ușoară. Dar copierea unui design bun pe un RDBMS direct pe celălalt poate duce la un cod inutil de complicat și cu performanțe slabe.

În următoarea și ultima parte a acestei serii, voi acoperi consecvența citirii și utilizarea instrumentelor de migrare. Rămâneți aproape!

* SQL Server 2019 (sau „15.x”) nu a fost disponibil suficient de mult pentru adoptarea pe scară largă a întreprinderilor.