Guide de migration Oracle vers SQL Server et SQL Server vers Oracle - Pt. 2

La première partie de cette série a abordé les différences entre Oracle Database et Microsoft SQL Server dans leur implémentation des transactions, en mettant l'accent sur les pièges que l'on peut rencontrer lors des migrations d'Oracle vers SQL Server et vice-versa. Ce prochain article couvrira un certain nombre d'éléments de syntaxe SQL couramment utilisés qui n'ont pas de correspondance ou qui ont une signification ou une utilisation complètement différente dans la division Oracle-SQL Server.

Séquences dans Oracle et colonnes d'identité dans SQL Server

Il y a depuis longtemps un clivage dans la communauté des bases de données entre deux camps : les patriotes des clés naturelles et les partisans des clés artificielles (ou « de substitution »).

Je défends moi-même les clés naturelles mais me retrouve souvent à créer des substituts pour une raison ou une autre. Mais laissant de côté le fond de ce débat, intéressons-nous aux mécanismes standards de génération de clés artificielles : séquences Oracle et colonnes d'identité SQL Server.

Une séquence Oracle est un objet de niveau base de données de première classe. En revanche, une colonne d'identité SQL Server est un type de colonne, pas un objet.

Lorsqu'une séquence Oracle est utilisée pour générer une clé de table, généralement une clé primaire, elle est garantie d'être incrémentée et donc unique. Mais ce n'est pas garanti d'être consécutif. En fait, même dans des implémentations bien conçues, il y a très probablement des lacunes. Ainsi, aucune implémentation Oracle ne devrait jamais compter sur des valeurs générées par séquence pour être consécutives.

De plus, une séquence est gérée via le dictionnaire de données d'une base de données Oracle, il serait donc trop consommateur de ressources (et fastidieux) de créer une séquence dédiée pour prendre en charge chaque clé de substitution. Un seul objet de séquence peut prendre en charge plusieurs ou même toutes les clés de substitution.

D'autre part, lorsque plusieurs processus doivent accéder à NEXTVAL (la valeur incrémentielle suivante) à partir d'une séquence, la séquence deviendra une ressource critique à accès unique. Il rendra effectivement tous les processus y accédant strictement séquentiels, transformant toute implémentation multi-thread (mono ou multi-serveur) en un processus mono-thread, avec de longs temps d'attente et une utilisation élevée de la mémoire/du processeur.

De telles implémentations se produisent réellement. La solution à ce problème consiste à définir l'objet séquence en question avec une valeur de cache raisonnable, ce qui signifie qu'une plage définie de valeurs (que ce soit 100 ou 100 000) est sélectionnée dans un cache pour un processus appelant, enregistrée dans le dictionnaire de données tel qu'utilisé. , et deviennent disponibles pour ce processus particulier sans avoir besoin d'accéder au dictionnaire de données à chaque fois que NEXTVAL est appelé.

Mais c'est exactement la raison pour laquelle des écarts seront créés car toutes les valeurs mises en cache ne seront probablement pas utilisées. Cela signifie également que sur plusieurs processus dans des sessions parallèles, certaines valeurs de séquence enregistrées peuvent être inversées chronologiquement. Cette inversion ne peut pas se produire dans un seul processus à moins qu'une valeur de séquence n'ait été réinitialisée ou rétrogradée. Mais ce dernier scénario revient à chercher des problèmes : il devrait être inutile et, s'il est mal implémenté, il peut entraîner la génération de valeurs en double.

Ainsi, la seule manière correcte d'utiliser les séquences Oracle consiste à générer des clés de substitution : des clés qui sont uniques mais qui ne sont pas censées contenir d'autres informations utilisables de manière fiable.

Colonnes d'identité dans SQL Server

Qu'en est-il de SQL Server ? Bien que des séquences avec des fonctionnalités et une implémentation très similaires à leur homologue Oracle aient été introduites dans SQL Server 2012, elles ne constituent pas une technique incontournable de premier ordre. Comme d'autres ajouts de fonctionnalités, ils sont logiques pour la conversion à partir d'Oracle, mais lors de la mise en œuvre de clés de substitution à partir de zéro sur SQL Server, IDENTITY est une bien meilleure option.

IDENTITY est un objet "enfant" d'une table. Il n'accède à aucune ressource en dehors d'une table et est garanti séquentiel à moins d'être délibérément manipulé. Et il est spécifiquement conçu pour cette tâche même, plutôt que pour la compatibilité sémantique avec Oracle.

Oracle ayant implémenté la fonctionnalité IDENTITY dans la version 12.1, il est naturel de se demander comment il s'en était passé auparavant, pourquoi il l'a implémentée maintenant et pourquoi SQL Server en avait besoin dès le début (depuis ses origines Sybase SQL Server).

La raison en est qu'Oracle a toujours eu une fonction de clé d'identité : la pseudo- ROWID , ayant un type de données ROWID ou UROWID . Cette valeur n'est pas numérique ( ROWID et UROWID sont des types de données Oracle propriétaires) et identifie de manière unique un enregistrement de données.

Contrairement à IDENTITY de SQL Server, le ROWID d'Oracle ne peut pas être facilement manipulé (il peut être interrogé, mais pas inséré ou modifié), et il est créé en arrière-plan pour chaque ligne de chaque table Oracle. De plus, le moyen le plus efficace d'accéder à n'importe quelle ligne de données dans une base de données Oracle est par son ROWID , il est donc utilisé comme technique d'optimisation des performances. Enfin, il définit l'ordre de tri de sortie de requête par défaut, car il indexe efficacement le stockage de bas niveau des données de ligne.

Si le ROWID d'Oracle est si important, comment SQL Server a-t-il survécu à toutes ces années et à toutes ces versions sans lui ? En utilisant les colonnes IDENTITY comme clés primaires (de substitution).

Il est important de noter la différence d'implémentation de la structure d'index entre Oracle et SQL Server.

Dans SQL Server, le premier index (la clé primaire, le plus souvent) est mis en cluster ; cela signifie que le plus souvent, les données du fichier de données principal sont triées par cette clé. Côté Oracle, l'équivalent d'un index clusterisé est une table organisée en index. Il s'agit d'une construction facultative dans Oracle qui est utilisée sporadiquement, uniquement en cas de besoin, pour les tables de recherche en lecture seule, par exemple.

Tous les modèles de conception dans Oracle basés sur l'utilisation de ROWID (comme la déduplication des données) doivent être implémentés en fonction des colonnes IDENTITY lors de la migration vers SQL Server.

Bien que la migration de l'utilisation d' IDENTITY sur SQL Server vers l'utilisation d' IDENTITY sur Oracle puisse produire un code fonctionnellement correct, ce n'est pas optimal, car du côté Oracle, ROWID fonctionnera beaucoup plus efficacement.

Il en va de même lors d'une simple conversion de syntaxe SQL pour déplacer des séquences Oracle dans SQL Server : le code s'exécutera, mais l'utilisation d' IDENTITY est l'option préférée en termes de simplicité de code et de performances.

Index filtrés dans Microsoft SQL Server

Il y a des années, Microsoft SQL Server 2008 a introduit un certain nombre de fonctionnalités importantes qui en ont fait une véritable base de données d'entreprise de premier ordre. Celui qui m'a sauvé la vie plus d'une fois a été les index filtrés.

Un index filtré est un index non clusterisé (c'est-à-dire un index qui existe en tant que son propre fichier de données) qui a une clause WHERE . Cela signifie que le fichier d'index ne contient que des enregistrements de données pertinents pour la clause. Pour tirer pleinement parti des index filtrés, il doit également avoir une clause INCLUDE qui répertorie toutes les colonnes nécessaires lors du retour d'un ensemble de données. Lorsque votre requête est optimisée pour utiliser un index filtré spécifique qui inclut tous les points de données nécessaires, le moteur de base de données n'a besoin d'accéder qu'à un (petit) fichier d'index sans même consulter le fichier de données de la table principale.

Cela m'a été particulièrement précieux il y a quelques années lorsque je travaillais avec une table de la taille d'un téraoctet. Le client en question n'avait souvent besoin d'accéder qu'à une fraction de pour cent des enregistrements actifs à un moment donné. La mise en œuvre initiale de cet accès (déclenchée par les actions de l'interface utilisateur de l'utilisateur final) n'était pas simplement extrêmement lente, elle était tout simplement inutilisable. Lorsque j'ai ajouté un index filtré avec les INCLUDE nécessaires, cela est devenu une recherche inférieure à la milliseconde. Le temps que j'ai consacré à cette tâche d'optimisation n'a été qu'une heure.

Bien sûr, les index filtrés ont certaines limites. Ils ne peuvent pas inclure de colonnes LOB, il y a des limites sur les conditions que les clauses WHERE que les index eux-mêmes peuvent inclure, et ils s'ajoutent à l'empreinte de stockage d'une base de données. Mais à condition qu'un cas d'utilisation corresponde à ces paramètres, les compromis de stockage sont généralement assez mineurs par rapport à l'amélioration significative des performances que les index filtrés peuvent fournir.

Qu'en est-il des index filtrés dans Oracle Database ?

Plus tard, je me suis retrouvé dans une grande équipe d'une entreprise Fortune 500 en tant que développeur/DBA sur un projet de migration SQL Server vers Oracle. Le code entourant la base de données source (SQL Server 2008) était mal implémenté, avec des performances médiocres qui rendaient la conversion impérative : la tâche quotidienne de synchronisation principale durait plus de 23 heures. Il n'y avait pas d'index filtrés, mais dans le nouveau système (Oracle 11g), j'ai vu plusieurs cas où les index filtrés seraient très bénéfiques. Mais Oracle 11g n'a pas d'index filtrés !

Les index filtrés ne sont pas non plus implémentés dans le dernier Oracle 18c.

Mais notre tâche en tant que professionnels techniques est de tirer le meilleur parti de ce que nous avons. J'ai donc implémenté l'équivalent des index filtrés dans mon système Oracle 11g (et la même technique que j'ai utilisée plus tard dans 12c). L'idée est basée sur la façon dont Oracle gère les NULL et peut être utilisée dans n'importe quelle version d'Oracle.

Oracle ne traite pas une valeur NULL de la même manière que des données normales. Un NULL dans Oracle n'est rien - il n'existe pas. Par conséquent, si vous définissez votre colonne indexée comme NULLABLE et que vous recherchez des valeurs non NULL , votre fichier de données d'index ne contiendra que les enregistrements qui vous intéressent. Comme une définition d'index Oracle n'a pas de clause INCLUDE , vous devrez créer un index composite avec toutes les colonnes qui doivent être incluses dans un jeu de résultats. (Cette technique a une certaine surcharge par rapport à la clause INCLUDE de SQL Server, mais elle est raisonnablement insignifiante.)

Une telle implémentation de solution de contournement ajoute une limitation : la colonne d'index de tête doit autoriser les NULL s et, par conséquent, ne peut pas être la clé primaire de la table. Cependant, il peut s'agir d'une colonne dérivée ou calculée créée spécifiquement pour prendre en charge cette méthode d'optimisation des performances. Dans un certain sens, la première colonne de l'index est logiquement binaire : valeurs non NULL pour les données incluses dans votre recherche, et NULL pour toutes les données qui devraient être « invisibles ».

L'autre option possible lors de la migration de la logique d'index filtré SQL Server vers Oracle consiste à implémenter un index (ou la table dans son intégralité) comme partitionné. Dans ce cas, seule la partition d'index pertinente sera accessible par le moteur de base de données, à condition que les requêtes soient implémentées correctement en utilisant la condition de partitionnement exacte dans leurs clauses WHERE .

Cela fonctionnera bien, même à grande échelle, sur des données relativement statiques, mais peut imposer une charge de maintenance élevée à une équipe DBA si elle est appliquée à des données qui changent fréquemment. Un exemple serait lors de l'optimisation de l'accès aux données d'aujourd'hui dans une application centrée sur le temps : l'équipe DBA devra redéfinir les partitions quotidiennement. Bien que cette redéfinition puisse être scénarisée dans une tâche de maintenance nocturne, elle rend votre système plus complexe et introduit de nouveaux points de défaillance systémique potentiels.

Il faut donc être très précis et prudent chaque fois que la logique d'indexation filtrée de SQL Server doit être migrée vers Oracle.

Comment gérer les conversions

Avec une migration d'Oracle vers SQL Server, recherchez des opportunités d'optimisation à l'aide d'index filtrés. Vous ne verrez pas d'index filtrés dans Oracle, mais vous pouvez voir des index qui incluent des valeurs NULL . Ne les copiez pas tels quels : c'est peut-être le meilleur endroit où vous pouvez obtenir une amélioration des performances et de la conception de votre conversion.

Pour les migrations SQL Server vers Oracle, si vous voyez des index filtrés, recherchez comment éviter un goulot d'étranglement des performances dans votre code Oracle correspondant. Découvrez comment vous pouvez reconcevoir le flux de données pour compenser l'amélioration des performances manquante que les index filtrés avaient donnée dans l'implémentation source.

Les défis de la migration SQL Server vers Oracle / Oracle vers SQL Server démystifiés

Pour les projets de migration entre Oracle et SQL Server dans les deux sens, il est important de mieux comprendre les mécanismes impliqués. Lorsque les versions actuelles des bases de données respectives (Oracle 18c et Microsoft SQL Server 2017*) contiennent des équivalents lexicaux des fonctionnalités de l'autre, par exemple, dans les séquences et l'identité, cela peut sembler être une victoire facile. Mais copier une bonne conception sur un SGBDR directement sur l'autre peut entraîner un code inutilement compliqué et peu performant.

Dans la prochaine et dernière partie de cette série, je traite de la cohérence de lecture et de l'utilisation des outils de migration. Restez à l'écoute!

* SQL Server 2019 (ou "15.x") n'est pas sorti depuis assez longtemps pour une adoption généralisée par les entreprises.