mars
2008
On continue sur notre lancée avec le partitionnement sous Sql Server 2k5.
Qu’est ce que le partitionnement ?
Et bien c’est tout simplement le fait de diviser, physiquement, les données d’une table, d’une vue ou d’un index. La division se fait horizontalement (par lignes) et permet ainsi de diviser les grosses tables en petits morceaux mieux gérables.
Pourquoi partitionner ?
Imaginez que vous ayez une table de commandes qui recoit chaque jour des milliers de lignes, au fil des années, cette table commencera a devenir très grosse et très peu gérable (dans le cas ou vous voudriez ajouter un index, ou modifier une colonne, etc.). En fait, la table deviendra en définitif un gros bloc monolythique intouchable…
Pourquoi ne pas avoir la possibilité de diviser cette table entre plusieurs disques durs ? ainsi, nous pourrions mettre les commandes du mois dans un disque dur à faible capacité mais à très grande vitesse et les données « archives » dans un enorme disque de plusieurs centaines de Go. Pourquoi ne pas mettre une grosse table dans plein de petits disques durs ? Nous pourrions ainsi bénéficier de parallèlisme dans la lécture et l’ecriture des données.
C’est pour ces raisons que le partitionnement existe !
Le partitionnement sous Sql Server 2005
Le partitionnement se fait en trois phases :
1- Création d’une fonction de partitionnement : une fonction de partitionnement est totalement indépendante de toute structure physique ou logique de la table de données. Son rôle est de définir des « points de partitionnement » ou les valeurs qui délimitent les partitions. Voici la structure de cette fonction :
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ]
Un exemple :
cette fonction à comme paramètre un entier et dit : je partitionne des que l’entier que tu m’a spécifié atteint 1000, 2000 ou 3000.
La clause AS RANGE LEFT|RIGHT montre juste à quelle partition va aller le point de partitionnement.
2- Création d’un schéma de partitionnement : un schéma de partitionnement spécifie les zones de partitionnement physiques (alors que la fonction de partitionnement désigne les points logiques) et les associent à la fonction de partitionnement, ces zones de partitionnement sont en fait les filegroups qui composent votre base de données. (si vous ne savez pas ce qu’est un filegroup, consultez : http://msdn2.microsoft.com/en-us/library/ms176061.aspx)
La syntaxe pour créer un schéma de partitionnement est la suivante :
AS PARTITION partition_function_name
TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )[ ; ]
Un exemple :
go
Cette commande effectue les opérations suivantes :
les données entre 0 et 1000 seront stockées dans [FileGroup1],
les données entre 1001 et 2000 seront stockées dans [FileGroup2],
les données entre 2001 et l’infini seront stockées dans [FileGroup3].
Si la proposition « les données entre x et y » n’est pas claire, c’est normal, c’est la troisième étape
3- Création de tables (index ou vues) partitionnées : dernière et ultime étape, nous allons comprendre pourquoi nous avons fait les étapes 1 et 2.
La syntaxe pour créer une table partitionnée est la suivante :
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ] [ ; ]
Un exemple :
PK int identity(1,1) primary key,
Nom nvarchar(50),
Prenom nvarchar(80)
)
on partSchema(PK)
go
Ce que nous venons de faire : créer une table toute bête et lui associer le shéma de partitionnement crée précédement avec la clé primaire (le int) comme paramètre de fonction de partitionnement. En clair : tous les enregistrement avec clé primaire inférieure ou égale à 1000 iront dans le FileGroup1, les enreg avec 1001 inf à PK et inf ou égl à 2000 dans FileGroup2, etc.
Imaginez juste ça à plus grande échelle avec des filegroups dans des disques séparés. Voila l’utilité du partitionnement !
SELECT sur des tables partitionnées
Autre truc sympa avec le partitionnement dans Sql Server 2005, c’est la possibilité de faire des requêtes sur des partitions !!! Vu que le partitionnement est un méchanisme tout à fait transparent pour les personnes utilisant les tables, il serait embêtant pour vous, DBA qui avez partitionné la table, de parcourir toute la table avec un SELECT en sachant pertinament que les données que vous cherchez sont dans une partition X.
Il est possible de cibler une partition (ou plusieurs) dans votre SELECT grace à l’instruction $PARTITION. Exemple :
SELECT * FROM MaTable WHERE $PARTITION.ma_fonction_de_partition(cle) = 3
Cette instruction nous retourne tous les enregistrements de la partition numéro 3. Sympa non !
Mais attendez une minute !!!! Comment connaitre le numéro de la partition ??? Et bien c’est tout simple, la requête suivante vous donne tous les numéros de partition de votre table :
FROM MaTable GROUP BY $partition.partfunc(id)
de plus, cette requête nous permet d’avoir le numéro de partition ou est stocké l’enregistrement avec PK = 1234 :
Voila voila ! Avec ça, vous devriez être blindés !
merci infiniment, je vous suis extrêmement reconnaissant
cet article m’a beaucoup aidé
encore merci mrygrim
Vous avez oublié de parler de ce que c’est : [FileGroup1],[FileGroup2],[FileGroup3] et comment les créer.
Parce que lorsqu’on exécute
create partition scheme partSchema as partition partFunc to ([FileGroup1],[FileGroup2],[FileGroup3]) ca génère une erreur : FileGroup1 inconnu.
Le lien dans l’article ne marche pas.