APSQL

Partitionner une table

Par défaut les données d'une table sont stockées sur un seul et même groupe de fichier. Cette situation est tout à fait acceptable tant que le volume de données contenues dans la table reste raisonnable. Dans le cas où une table va contenir de très nombreuses informations, il est préférable de répartir les données sur plusieurs groupes de fichiers afin d'améliorer les opérations qui ont lieu sur cette table (aussi bien manipulation des données que les tâches administratives).

Pour être efficace ce partitionnement doit être fait de façon optimum et pour cela il convient de bien comprendre comment fonctionne le partionnement avec SQL Server.

SQL server utilise une clé de partitionnement, une fonction de partitionnement et un schéma de partionnement.

La clé de partitonnement correspond à la colonne qui détiens l'information à partir de laquelle on sera en mesure de choisir la partition à utiliser.

La fonction de partitionnement permet de connaitre à partir de la clé passée en paramètre le n° de la partition à utiliser.

Le schéma de partionnement indique pour chaque n° de partition le groupe de fichier à utiliser.

La fonction de partionnement

SQL Server utilise l'instruction CREATE PARTITION FUNCTION pour définir cette fonction. Le paramètre d'entrée de cette fonction correspond au type de données de la clé de partitionnement. Cette clé de partition peut être de n'importe quel type scalaire (ie ne peut pas être de type varchar(max), nvarchar(max), xml, varbinary(max), text, ntext, image)

CREATE PARTITION FUNCTION nomFonction ( nomParametre type )
AS RANGE [ LEFT | RIGHT ] 
FOR VALUES ( [ limite [ ,...n ] ] ) ;

Chaque plage de valeur de la clé va correspondre à un numéro de partition. Les limites de chaque intervalle sont définies avec la clause FOR VALUES. Pour savoir si la limite est incluse dans l'intervalle inférieur ou bien supérieur, on utilisera les termes LEFT ou RIGHT.

Par exemple si une fonction est définie de la sorte

Create partition function p1(int) AS RANGE LEFT FOR VALUES (1000, 2000, 3000);

Alors le tableau suivant indique le numéro de la partition:

Valeur de la clé10100015002000250030003500
Partition1122334

Au contraire si la fonction est définie avec l'option RIGHT

Create partition function p1(int) AS RANGE RIGHT FOR VALUES (1000, 2000, 3000);

Alors le tableau suivant indique le numéro de la partition:

Valeur de la clé10100015002000250030003500
Partition1223344

Le schema de partionnement

Ce schéma permet de définir le groupe de fichier attribué à chaque partition, il est défini à l’aide de l’instruction CREATE PARTITION SCHEME

CREATE PARTITION SCHEME nomSchemaPartition 
AS PARTITION nomfonctionPartition 
[ ALL ] TO ( { groupeDeFichiers [ ,...n ] ) ;

L’option ALL est utilisée lorsque toutes les partitions sont définies sur le même groupe de fichier.

Il est important que le nombre de groupes de fichiers correspondent au nombre de partitions. Si le même groupe de fichier est utilisé pour plusieurs partitions alors il est nécessaire de le préciser plusieurs fois dans la liste des groupes de fichiers.

Par exemple:

CREATE PARTITION SCHEME s1 AS PARITION p1 TO (groupe1, groupe2, groupe3, groupe4) ;

La table partitionnée

Lors de la création de la table il est nécessaire de préciser la partition à utiliser à la place de préciser le groupe de fichier. Sinon la syntaxe de création de la table est en tout point comparable à celle d’une table classique.

Syntaxe

CREATE TABLE test(id int, c1 nvarchar(500)) ON s1(id);

Dans la cas ou un index ordonnées (CLUSTERED) est défini il est nécessaire que la clé de partitionnement soit incluse dans la définition de cet index car l'un comme l'autres organisent physiquement la table.

Exemple complet de réalisation

Etape 1 : Définir les groupes de fichiers

ALTER DATABASE test ADD FILEGROUP groupe1;
ALTER DATABASE test ADD FILEGROUP groupe2;
ALTER DATABASE test ADD FILEGROUP groupe3;
ALTER DATABASE test ADD FILEGROUP groupe4;

Etape 2 : Ajouter des fichiers dans les groupes de fichiers

ALTER DATABASE test ADD FILE(NAME=fichier1, 
	FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Fichier1.ndf',
	SIZE=50MB) TO FILEGROUP groupe1;
ALTER DATABASE test ADD FILE(NAME=fichier2, 
	FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Fichier2.ndf',
	SIZE=50MB) TO FILEGROUP groupe2;
ALTER DATABASE test ADD FILE(NAME=fichier3, 
	FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Fichier3.ndf',
	SIZE=50MB) TO FILEGROUP groupe3;
ALTER DATABASE test ADD FILE(NAME=fichier4, 
	FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Fichier4.ndf',
	SIZE=50MB) TO FILEGROUP groupe4;

Etape 3 : Définir la fonction de partitionnement

CREATE PARTITION FUNCTION pfclient(int) 
  AS RANGE LEFT FOR VALUES (1000, 2000, 3000);

Etape 4 : Définir le schéma de partitionnement

CREATE PARTITION SCHEME schemaClients
  AS PARTITION pfclient
  TO (groupe1, groupe2, groupe3, groupe4);

Etape 5 : Créer la table

CREATE TABLE Clients(
  id int not null,
  nom nvarchar(80),
  prenom nvarchar(80),
  email nvarchar(120),
  descriptif xml,
  CONSTRAINT pk_clients PRIMARY KEY(id))
ON schemaClients(id);

Plus d'informations: