Guida alla migrazione da Oracle a SQL Server e da SQL Server a Oracle - Pt. 2

La prima parte di questa serie ha discusso le differenze tra Oracle Database e Microsoft SQL Server nell'implementazione delle transazioni, con particolare attenzione alle insidie ​​che si possono incontrare durante le migrazioni da Oracle a SQL Server e viceversa. La prossima puntata tratterà una serie di elementi della sintassi SQL comunemente usati che non hanno corrispondenze o hanno un significato o un utilizzo completamente diversi nella divisione Oracle-SQL Server.

Sequenze in Oracle e colonne Identity in SQL Server

C'è una divisione di vecchia data nella comunità dei database tra due campi: i patrioti delle chiavi naturali ei sostenitori delle chiavi artificiali (o "surrogate").

Io stesso difendo le chiavi naturali ma spesso mi ritrovo a creare surrogati per un motivo o per l'altro. Ma lasciando da parte la sostanza di questo dibattito, diamo un'occhiata ai meccanismi standard per la generazione di chiavi artificiali: sequenze Oracle e colonne di identità di SQL Server.

Una sequenza Oracle è un oggetto a livello di database di prima classe. Al contrario, una colonna Identity di SQL Server è un tipo di colonna, non un oggetto.

Quando una sequenza Oracle viene utilizzata per generare una chiave di tabella, in genere una chiave primaria, è garantito che venga incrementata e quindi sia univoca. Ma non è garantito che sia consecutivo. In effetti, anche in implementazioni ben progettate, è molto probabile che presenti delle lacune. Quindi nessuna implementazione Oracle dovrebbe mai basarsi su valori generati dalla sequenza per essere consecutivi.

Inoltre, una sequenza viene gestita tramite il dizionario dei dati di un database Oracle, quindi sarebbe troppo dispendioso in termini di risorse (e ingombrante) creare una sequenza dedicata per supportare ogni chiave surrogata. Un singolo oggetto sequenza può supportare più o anche tutte le chiavi surrogate.

D'altra parte, quando più processi devono accedere a NEXTVAL (il valore incrementale successivo) da una sequenza, la sequenza diventerà una risorsa critica ad accesso singolo. Renderà in modo efficace tutti i processi che vi accedono rigorosamente sequenziali, trasformando qualsiasi implementazione multi-thread (singolo o multi-server) in un processo a thread singolo, con lunghi tempi di attesa e memoria elevata/utilizzo ridotto della CPU.

Tali implementazioni accadono effettivamente. La soluzione a questo problema è definire l'oggetto sequenza in questione con un valore di cache ragionevole, il che significa che un intervallo definito di valori (che sia 100 o 100 mila) viene selezionato in una cache per un processo di chiamata, registrato nel dizionario dei dati come utilizzato e diventano disponibili per questo particolare processo senza la necessità di accedere al dizionario dei dati ogni volta che viene chiamato NEXTVAL .

Ma questo è esattamente il motivo per cui verranno creati degli spazi vuoti poiché è probabile che non tutti i valori memorizzati nella cache vengano utilizzati. Significa anche che in più processi in sessioni parallele, alcuni valori di sequenza registrati possono essere invertiti cronologicamente. Questa inversione non può verificarsi all'interno di un singolo processo a meno che un valore di sequenza non sia stato reimpostato o spostato all'indietro. Ma quest'ultimo scenario equivale a cercare problemi: non dovrebbe essere necessario e, se implementato in modo errato, può comportare la generazione di valori duplicati.

Quindi, l'unico modo corretto per utilizzare le sequenze Oracle è per la generazione di chiavi surrogate: chiavi che sono univoche ma non si presume contengano altre informazioni utilizzabili in modo affidabile.

Colonne identità in SQL Server

E SQL Server? Sebbene le sequenze con funzionalità e implementazione molto simili alla loro controparte Oracle siano state introdotte in SQL Server 2012, non sono una tecnica di prim'ordine. Come altre aggiunte di funzionalità, hanno senso per la conversione da Oracle, ma quando si implementano chiavi surrogate da zero su SQL Server, IDENTITY è un'opzione molto migliore.

IDENTITY è un oggetto “figlio” di una tabella. Non accede a risorse al di fuori di una tabella ed è garantito per essere sequenziale a meno che non venga manipolato deliberatamente. Ed è progettato specificamente per questo compito, piuttosto che per la compatibilità semantica con Oracle.

Con Oracle che ha implementato la funzionalità IDENTITY nella versione 12.1, è naturale chiedersi come ha fatto prima senza di essa, perché l'ha implementata ora e perché SQL Server ne aveva bisogno sin dall'inizio (dalle origini di Sybase SQL Server).

Il motivo è che Oracle ha sempre avuto una caratteristica chiave di identità: la pseudocolonna ROWID , con un tipo di dati ROWID o UROWID . Questo valore non è numerico ( ROWID e UROWID sono tipi di dati Oracle proprietari) e identifica in modo univoco un record di dati.

A differenza di IDENTITY di SQL Server, ROWID di Oracle non può essere facilmente manipolato (può essere interrogato, ma non inserito o modificato) e viene creato in background per ogni riga in ogni tabella Oracle. Inoltre, il modo più efficiente per accedere a qualsiasi riga di dati in un database Oracle è tramite il relativo ROWID , quindi viene utilizzato come tecnica di ottimizzazione delle prestazioni. Infine, definisce l'ordinamento predefinito dell'output della query, poiché indicizza efficacemente l'archiviazione di basso livello dei dati delle righe.

Se il ROWID di Oracle è così importante, come ha fatto SQL Server a sopravvivere a tutti quegli anni e ai rilasci senza di esso? Utilizzando le colonne IDENTITY come chiavi primarie (surrogate).

È importante notare la differenza nell'implementazione della struttura dell'indice tra Oracle e SQL Server.

In SQL Server, il primo indice, la chiave primaria, il più delle volte, è raggruppato; ciò significa che più comunemente, i dati nel file di dati primario sono ordinati da questa chiave. Sul lato Oracle, l'equivalente di un indice cluster è una tabella organizzata per indice. Questo è un costrutto opzionale in Oracle che viene utilizzato sporadicamente, solo se necessario, ad esempio per tabelle di ricerca di sola lettura.

Tutti i modelli di progettazione in Oracle basati sull'uso di ROWID (come la deduplicazione dei dati) devono essere implementati in base alle colonne IDENTITY durante la migrazione a SQL Server.

Sebbene la migrazione dall'utilizzo di IDENTITY su SQL Server all'utilizzo di IDENTITY su Oracle possa produrre codice funzionalmente corretto, non è ottimale, perché sul lato Oracle, ROWID funzionerà in modo molto più efficiente.

Lo stesso vale quando si esegue una semplice conversione della sintassi SQL per spostare le sequenze Oracle in SQL Server: il codice verrà eseguito, ma l'utilizzo di IDENTITY è l'opzione molto preferita in termini di semplicità e prestazioni del codice.

Indici filtrati in Microsoft SQL Server

Anni fa, Microsoft SQL Server 2008 ha introdotto una serie di funzionalità significative che lo hanno trasformato in un database aziendale davvero di prim'ordine. Uno che mi ha salvato la giornata più di una volta è stato filtrato indici.

Un indice filtrato è un indice non cluster (cioè uno che esiste come file di dati proprio) che ha una clausola WHERE . Significa che il file di indice contiene solo record di dati rilevanti per la clausola. Per sfruttare appieno gli indici filtrati, dovrebbe anche avere una clausola INCLUDE che elenca tutte le colonne necessarie quando si restituisce un set di dati. Quando la query è ottimizzata per utilizzare un indice filtrato specifico che include tutti i punti dati necessari, il motore di database deve solo accedere a un file di indice (piccolo) senza nemmeno guardare il file di dati della tabella principale.

Questo è stato particolarmente prezioso per me alcuni anni fa quando lavoravo con una tabella delle dimensioni di un terabyte. Il client in questione aveva spesso bisogno di accedere solo a una frazione di percentuale dei record attivi in ​​un dato momento. L'implementazione iniziale di questo accesso (attivato dalle azioni dell'interfaccia utente finale) non era solo dolorosamente lenta, ma era semplicemente inutilizzabile. Quando ho aggiunto un indice filtrato con i necessari INCLUDE s, è diventata una ricerca inferiore al millisecondo. Il tempo che ho dedicato a questa attività di ottimizzazione è stato solo un'ora.

Certo, gli indici filtrati hanno alcune limitazioni. Non possono includere colonne LOB, ci sono limiti alle condizioni che le clausole WHERE possono includere gli indici stessi e si aggiungono al footprint di archiviazione di un database. Ma a condizione che un caso d'uso rientri in questi parametri, i compromessi di archiviazione sono generalmente piuttosto minori rispetto al significativo aumento delle prestazioni che gli indici filtrati possono fornire.

Che dire degli indici filtrati nel database Oracle?

Successivamente mi sono ritrovato in un grande team presso un'azienda Fortune 500 come sviluppatore/DBA su un progetto di migrazione da SQL Server a Oracle. Il codice che circonda il database di origine, SQL Server 2008, è stato implementato in modo inadeguato, con scarse prestazioni che hanno reso indispensabile la conversione: il processo di sincronizzazione back-end giornaliero è stato eseguito per più di 23 ore. Non aveva indici filtrati, ma nel nuovo sistema Oracle 11g ho riscontrato diversi casi in cui gli indici filtrati sarebbero stati molto utili. Ma Oracle 11g non ha indici filtrati!

Né gli indici filtrati sono implementati nell'ultimo Oracle 18c.

Ma il nostro compito di professionisti tecnici è quello di utilizzare al meglio ciò che abbiamo. Quindi ho implementato l'equivalente degli indici filtrati nel mio sistema Oracle 11g (e la stessa tecnica che ho usato più avanti in 12c). L'idea si basa su come Oracle gestisce i NULL e può essere utilizzata in qualsiasi versione di Oracle.

Oracle non tratta un valore NULL allo stesso modo dei dati normali. Un NULL in Oracle non è nulla, non esiste. Di conseguenza, se si definisce la colonna indicizzata come NULLABLE e si esegue la ricerca in base a valori non NULL , il file di dati dell'indice conterrà solo record di interesse. Poiché una definizione di indice Oracle non ha una clausola INCLUDE , dovrai creare un indice composto con tutte le colonne che devono essere incluse in un set di risultati. (Questa tecnica ha un sovraccarico rispetto alla clausola INCLUDE di SQL Server, ma è ragionevolmente insignificante.)

Tale implementazione di una soluzione alternativa aggiunge una limitazione: la colonna dell'indice iniziale deve consentire s NULL e, pertanto, non può essere la chiave primaria della tabella. Tuttavia, può essere una colonna derivata o calcolata creata appositamente per supportare questo metodo di ottimizzazione delle prestazioni. In un certo senso, la colonna iniziale dell'indice è logicamente binaria: valori non NULL per i dati inclusi nella ricerca e NULL per tutti i dati che dovrebbero essere "invisibili".

L'altra opzione possibile nella migrazione della logica dell'indice filtrato di SQL Server in Oracle consiste nell'implementare un indice (o la tabella per intero) come partizionato. In questo caso, il motore di database accederà solo alla partizione di indice pertinente, a condizione che le query vengano implementate correttamente utilizzando l'esatta condizione di partizionamento nelle rispettive clausole WHERE .

Funzionerà bene, anche su larga scala, su dati relativamente statici, ma può comportare un carico di manutenzione elevato su un team DBA se applicato a dati che cambiano frequentemente. Un esempio potrebbe essere l'ottimizzazione dell'accesso ai dati odierni in un'applicazione incentrata sul tempo: il team DBA dovrà ridefinire le partizioni su base giornaliera. Sebbene questa ridefinizione possa essere eseguita tramite script in un lavoro di manutenzione notturno, rende il sistema più complesso e introduce nuovi potenziali punti di errore sistemico.

Pertanto, è necessario essere molto specifici e attenti ogni volta che è necessario migrare la logica dell'indice filtrato di SQL Server a Oracle.

Come gestire le conversioni

Con una migrazione da Oracle a SQL Server, cerca opportunità di ottimizzazione utilizzando indici filtrati. Non vedrai indici filtrati in Oracle, ma potresti vedere indici che includono valori NULL . Non copiarli così come sono: potrebbe essere il posto migliore in cui puoi ottenere un aumento delle prestazioni e un miglioramento del design nella tua conversione.

Per le migrazioni da SQL Server a Oracle, se vedi indici filtrati, cerca come evitare un collo di bottiglia delle prestazioni nel codice Oracle corrispondente. Scopri come riprogettare il flusso di dati per compensare l'aumento delle prestazioni mancante fornito dagli indici filtrati nell'implementazione di origine.

Sfide di migrazione da SQL Server a Oracle / Oracle a SQL Server demistificate

Per i progetti di migrazione tra Oracle e SQL Server in entrambe le direzioni, è importante avere una comprensione più approfondita dei meccanismi coinvolti. Quando le versioni attuali dei rispettivi database (Oracle 18c e Microsoft SQL Server 2017*) contengono equivalenti lessicali delle rispettive funzionalità, ad esempio in sequenze e identità, potrebbe sembrare una vittoria facile. Ma copiare un buon progetto su un RDBMS direttamente sull'altro può comportare un codice inutilmente complicato e con prestazioni scadenti.

Nella parte successiva e finale di questa serie, tratterò la consistenza della lettura e l'uso degli strumenti di migrazione. Rimani sintonizzato!

* SQL Server 2019 (o "15.x") non è disponibile da abbastanza tempo per un'adozione diffusa a livello aziendale.