Partager via

  • Article

Création de vues partitionnées distribuées

Avant d'implémenter une vue partitionnée, vous devez d'abord partitionner une table horizontalement. Lorsque vous concevez un schéma de partitionnement, il est indispensable d'identifier clairement les données appartenant à chaque table membre. La table d'origine est remplacée par plusieurs tables membres d'une taille inférieure. Chaque table membre a le même nombre de colonnes que la table d'origine, et chaque colonne a les mêmes attributs que la colonne correspondante dans la table d'origine (tels que le type de données, la taille et le classement). Si vous créez une vue partitionnée distribuée, chaque table membre figure sur un serveur membre distinct. Pour assurer la plus grande clarté en matière d'emplacements, le nom des bases de données membres doit être le même sur chaque serveur membre, même si ce n'est pas obligatoire. Exemple : Server1.CustomerDB, Server2.CustomerDB, Server3.CustomerDB.

Création des tables membres

Vous devez concevoir les tables membres de telle sorte que chacune d'elles stocke une coupe horizontale de la table d'origine en fonction d'une plage de valeurs de clés. Ces plages sont basées sur les valeurs de données d'une colonne de partitionnement. La plage de valeurs figurant dans chaque table membre est imposée par une contrainte CHECK appliquée à la colonne de partitionnement ; par ailleurs, les plages ne peuvent pas se chevaucher. Par exemple, vous ne pouvez pas avoir une table avec une plage de valeurs de 1 à 200 000, et une autre avec une plage de 150 000 à 300 000, car il y aurait une ambiguïté quant à la table qui contient les valeurs de 150 000 à 200 000.

Par exemple, une table Customer est partitionnée en trois tables. La contrainte CHECK de ces tables est la suivante :

-- On Server1:
CREATE TABLE Customers_33
  (CustomerID   INTEGER PRIMARY KEY
                CHECK (CustomerID BETWEEN 1 AND 32999),
  ... -- Additional column definitions)

-- On Server2:
CREATE TABLE Customers_66
  (CustomerID   INTEGER PRIMARY KEY
                CHECK (CustomerID BETWEEN 33000 AND 65999),
  ... -- Additional column definitions)

-- On Server3:
CREATE TABLE Customers_99
  (CustomerID   INTEGER PRIMARY KEY
                CHECK (CustomerID BETWEEN 66000 AND 99999),
  ... -- Additional column definitions)

Définition de vues partitionnées distribuées

Après avoir créé les tables membres, vous définissez une vue partitionnée distribuée sur chaque serveur membre, en attribuant le même nom à chaque vue. Cela permet d'exécuter sur un ou plusieurs serveurs membres les requêtes qui référencent le nom de la vue partitionnée distribuée. Le système agit comme si une copie de la table d'origine figurait sur chaque serveur membre, alors que chaque serveur héberge uniquement une table membre et une vue partitionnée distribuée. L'emplacement des données est transparent pour l'application.

Vous créez les vues partitionnées distribuées en effectuant les tâches suivantes :

  • Ajoutez les définitions de serveurs liés sur chaque serveur membre détenant les informations de connexion requises pour l'exécution de requêtes distribuées sur les autres serveurs membres. De cette manière, une vue partitionnée distribuée peut accéder aux données situées sur les autres serveurs.

  • Définissez l'option lazy schema validation à l'aide de sp_serveroption pour chaque définition de serveur lié utilisée dans les vues partitionnées distribuées. Les performances sont ainsi optimisées, car le processeur de requêtes ne demande pas de métadonnées pour les tables liées tant que les données ne sont pas effectivement requises par la table membre distante.

  • Créez une vue partitionnée distribuée sur chaque serveur membre. Les vues utilisent des instructions SELECT distribuées pour accéder aux données des serveurs membres liés, puis fusionnent les lignes distribuées avec les lignes de la table membre locale.

Pour créer des vues partitionnées distribuées pour l'exemple précédent, procédez comme suit :

  • Ajoutez une définition de serveur lié nommée Server2 contenant les informations de connexion de Server2, et une définition de serveur lié nommée Server3 permettant d'accéder à Server3.

  • Créez la vue partitionnée distribuée suivante :

    CREATE VIEW Customers AS
   SELECT * FROM CompanyDatabase.TableOwner.Customers_33
UNION ALL
   SELECT * FROM Server2.CompanyDatabase.TableOwner.Customers_66
UNION ALL
   SELECT * FROM Server3.CompanyDatabase.TableOwner.Customers_99

  • Répétez la procédure pour Server2 et Server3.

Règles concernant les tables

Les tables membres sont définies dans la clause FROM de chaque instruction SELECT de la définition de la vue. Chaque table membre doit se conformer aux règles suivantes :

  • Les tables membres ne peuvent pas être référencées plus d'une fois dans la vue.

  • Les tables membres ne peuvent pas avoir d'index créés sur une colonne calculée.

  • Les tables membres doivent avoir toutes les contraintes PRIMARY KEY sur un nombre identique de colonnes.

  • Les tables membres doivent avoir le même paramètre de remplissage ANSI. Pour plus d'informations sur le paramètre de remplissage ANSI, consultez SET ANSI_PADDING.

Règles concernant les colonnes

Les colonnes sont définies dans la liste de sélection de chaque instruction SELECT de la définition de la vue. Chaque colonne doit se conformer aux règles suivantes :

  • Toutes les colonnes de chaque table membre doivent être incluses dans la liste de sélection. La syntaxe « SELECT * FROM <table membre> » est valide.

  • Les colonnes ne peuvent pas être référencées plus d'une fois dans la liste de sélection.

  • Les colonnes doivent se trouver dans la même position ordinale dans la liste de sélection.

  • Les colonnes de la liste de sélection de chaque instruction SELECT doivent être du même type. Cela concerne le type de données, la précision, l'échelle et le classement. Par exemple, la définition de vue suivante échoue, car les premières colonnes des deux instructions SELECT ne présentent pas le même type de données :

    CREATE VIEW NonUpdatable
AS
SELECT IntPrimaryKey, IntPartNmbr
FROM FirstTable
  UNION ALL
SELECT NumericPrimaryKey, IntPartNmbr
FROM SecondTable

Règles concernant les colonnes de partitionnement

Une seule colonne peut être utilisée pour le partitionnement et elle doit exister dans chaque table membre. Les contraintes CHECK identifient les données disponibles dans chaque table membre. Les règles suivantes supplémentaires s'appliquent :

  • Les groupes de clés des contraintes CHECK de chaque table ne peuvent pas chevaucher les groupes d'une autre table. Toute valeur spécifique de la colonne de partitionnement doit être mappée à une seule table. Les contraintes CHECK ne peuvent utiliser que les opérateurs suivants : BETWEEN, IN, AND, OR, <, <=, >, >=, =.

  • La colonne de partitionnement ne peut pas être une colonne d'identité, une colonne par défaut ou une colonne timestamp.

  • La colonne de partitionnement doit occuper la même position ordinale dans la liste de sélection de chaque instruction SELECT de la vue. Par exemple, elle est toujours la première colonne de chaque liste de sélection ou la deuxième de chaque liste de sélection, etc.

  • La colonne de partitionnement ne peut pas autoriser les valeurs Null.

  • La colonne de partitionnement doit faire partie de la clé primaire de la table.

  • La colonne de partitionnement ne peut pas être une colonne calculée.

  • Il ne doit exister qu'une seule contrainte sur la colonne de partitionnement. S'il existe plusieurs contraintes, SQL Server les ignore toutes et ne les prend pas en compte pour déterminer si la vue est partitionnée.

  • La possibilité de mettre à jour la colonne de partitionnement n'est soumise à aucune restriction.

Une colonne de partitionnement qui respecte toutes ces règles bénéficiera de toutes les optimisations prises en charge par l'optimiseur de requêtes. Pour plus d'informations, consultez Résolution de vues partitionnées distribuées.

Règles générales

[!REMARQUE]

Les conditions suivantes ne s'appliquent pas aux vues partitionnées localement créées sur le même serveur. Il s'agit d'une fonctionnalité incluse pour des raisons de compatibilité descendante.

Les règles suivantes doivent également être prises en compte :

  • Une vue partitionnée distribuée ne peut pas être formée à l'aide des opérateurs EXCEPT ou INTERSECT.

  • Une transaction distribuée doit être démarrée pour garantir l'atomicité sur tous les nœuds affectés par la mise à jour.

  • L'option XACT_ABORT SET doit avoir la valeur ON.

  • Les colonnes smallmoney et smalldatetime des tables distantes sont respectivement mappées en types money et datetime. Par conséquent, les colonnes correspondantes des tables locales doivent également être de type money et datetime.

  • Aucun serveur lié ne peut être un serveur lié en boucle. Il s'agit d'un serveur lié qui pointe vers la même instance de SQL Server.

Une vue qui référence des tables partitionnées sans respecter l'ensemble de ces règles peut néanmoins être mise à jour si elle a un déclencheur INSTEAD OF. Cependant, pour une vue dotée d'un déclencheur INSTEAD OF, l'optimiseur de requête ne sera pas à même d'élaborer systématiquement des plans d'exécution aussi efficaces que ceux conçus pour une vue partitionnée respectant toutes ces règles.