Vous pouvez créer une table ou un index partitionné dans SQL Server 2016 à l'aide de SQL Server Management Studio ou de Transact-SQL. Les données d'une table et d'index partitionnés sont divisées horizontalement en blocs qui peuvent être répartis sur plusieurs groupes de fichiers de la base de données. Le partitionnement peut améliorer la gérabilité et l'évolutivité des grandes tables et index.

Or, un index comporte généralement quatre étapes :

Créez un ou plusieurs groupes de fichiers et les fichiers correspondants qui contiendront des partitions selon le schéma de partitionnement.

Créez une fonction de partition qui mappe les lignes de table ou d'index aux partitions en fonction des valeurs des éléments d'une colonne donnée.

Créez un schéma de partition qui mappe les partitions d'une table ou d'un index partitionné à de nouveaux groupes de fichiers.

Créez ou modifiez une table ou un index et spécifiez un schéma de partition comme emplacement de stockage.

Dans cette section

Avant de commencer, effectuez les étapes suivantes.

Restrictions

Sécurité

Créez une table ou un index partitionné à l'aide des outils suivants :

Studio de gestion de serveur SQL

Restrictions

La portée d'un schéma de fonction et de partition est limitée à la base de données dans laquelle elle a été créée. Les fonctions de partitionnement résident dans un espace de noms distinct des autres fonctions de la base de données.

Si des lignes de la fonction de partition ont des colonnes de partition nulles, ces lignes sont placées dans la partition la plus à gauche. Cependant, si NULL est spécifié comme valeur limite et que RIGHT est spécifié, la section la plus à gauche reste vide et les valeurs NULL sont placées dans la deuxième section.

Sécurité

Autorisations

La création d'une table partitionnée nécessite l'autorisation CREATE TABLE sur la base de données et l'autorisation ALTER sur le schéma dans lequel la table est créée. La création d'un index partitionné nécessite l'autorisation ALTER sur la table ou la vue sur laquelle l'index est créé. La création d'une table ou d'un index partitionné nécessite l'une des autorisations supplémentaires suivantes :

MODIFIER TOUTE autorisation DATASPACE. Cette autorisation est attribuée par défaut aux membres du rôle serveur fixe administrateur système et rôles de base de données fixes db_owner Et db_ddladmin.

Autorisation CONTROL ou ALTER sur la base de données dans laquelle la fonction et le schéma de partitionnement sont créés.

Autorisation CONTROL SERVER ou ALTER ANY DATABASE sur le serveur de base de données dans lequel la fonction et le schéma de partitionnement sont créés.

Exécuter instructions étape par étape dans cette procédure pour créer un ou plusieurs groupes de fichiers, les fichiers associés et une table. L'exemple suivant fera référence à ces objets lors de la création d'une table partitionnée.

Créer de nouveaux groupes de fichiers pour une table partitionnée

Création d'une table partitionnée

Cliquez avec le bouton droit sur la table à partitionner, sélectionnez Stockage et cliquez Créer une rubrique...

DANS Assistant de création de section Sur la page Salutations du créateur de la section Cliquez sur Plus loin.

Sur la page Sélection d'une colonne de partition dans la grille, sélectionnez la colonne selon laquelle vous souhaitez partitionner la table. Sur la grille Colonnes de partitionnement disponibles Seules les colonnes comportant des types de données que vous pouvez partitionner sont affichées. Si vous sélectionnez une colonne calculée comme colonne de partitionnement, vous devez la rendre persistante.

Le choix de la colonne de partitionnement et de la plage de valeurs est principalement déterminé par le degré de regroupement logique des données. Par exemple, vous pouvez diviser les données en groupes logiques par mois ou trimestre de l'année. Les requêtes de données planifiées déterminent si un tel regroupement logique est adéquat pour gérer les partitions de table. Tout type de données peut être utilisé comme colonnes de partitionnement, sauf texte, ntexte, image, XML, horodatage, varchar(max), nvarchar(max), varbinaire (max), les alias de type de données et les types de données CLR définis par l'utilisateur.

Co-localisation de cette table avec la table partitionnée sélectionnée
Vous permet de sélectionner une table partitionnée contenant des données associées à joindre à cette table à l'aide de la colonne de partition. Les tables partitionnées jointes par des colonnes de partitionnement sont généralement plus efficaces dans les requêtes.

Aligner le stockage des données non uniques et index uniques avec colonne de partitionnement indexée
Aligne tous les index d'une table partitionnés à l'aide du même schéma. En alignant une table et ses index, les partitions peuvent être déplacées plus efficacement vers et depuis des tables partitionnées, car les données sont partitionnées à l'aide du même algorithme.

Après avoir sélectionné la colonne de partitionnement et les autres colonnes, cliquez sur Plus loin.

Sur la page Sélection d'une fonction de partitionnement Au chapitre Sélectionnez la fonction de partitionnement cliquez sur ou . Au moment de choisir Créer une fonction de partitionnement entrez le nom de la fonction. Si l'option est sélectionnée Fonctionnalité existante sectionnement, puis sélectionnez dans la liste le nom de la fonction qui sera utilisée pour le partitionnement. Notez que s'il n'y a pas d'autres fonctions de partitionnement dans la base de données, le paramètre Fonction de partitionnement existante sera indisponible.

Plus loin.

Sur la page Sélection d'un schéma de partition Au chapitre Sélectionnez le schéma de partition cliquez sur ou . Au moment de choisir Créer un schéma de partition entrez un nom de schéma. Si l'option est sélectionnée Schéma de partitionnement existant, puis sélectionnez le nom du schéma qui sera utilisé dans la liste. S'il n'existe aucun autre schéma de partitionnement dans la base de données, l'option Schéma de partitionnement existant sera indisponible.

Lorsque vous avez terminé avec cette page, cliquez sur le bouton Plus loin.

Sur la page Cartographie des sections Au chapitre Gamme sélectionner Bordure gauche ou Bordure droite pour sélectionner la valeur seuil la plus élevée ou la plus basse à inclure dans tous les groupes de fichiers créés. En plus du nombre de groupes de fichiers spécifié comme limite lors de la création de partitions, vous devez toujours saisir un groupe de fichiers supplémentaire.

Sur la grille Sélection de groupes de fichiers et spécification de valeurs limites dans le champ Groupe de fichiers sélectionnez le groupe de fichiers dans lequel les données seront partitionnées. Au chapitre Frontière entrez une valeur limite pour chaque groupe de fichiers. Si aucune valeur limite n'est spécifiée, la fonction de partitionnement mappe la totalité de la table ou de l'index sur une seule partition à l'aide du nom de la fonction de partitionnement.

Les options supplémentaires suivantes sont disponibles sur cette page :

Fixer des limites...
Ouvrir une boîte de dialogue Définition des valeurs limites, où vous pouvez sélectionner des valeurs limites et des plages de dates pour les sections. Cette option est disponible uniquement si vous sélectionnez une colonne de partitionnement contenant l'un des types de données suivants : date, dateheure, petite dateheure, dateheure2 ou décalage date/heure.

Évaluation du stockage
Estimer le nombre de lignes nécessaires et espace disponible pour stocker chaque groupe de fichiers spécifié pour les partitions. Ces valeurs sont en lecture seule dans la grille.

Dans la boîte de dialogue Définition des valeurs limites Vous pouvez définir les paramètres supplémentaires suivants :

date de début
Sélectionnez une date de début pour les valeurs de plage de partition.

date d'expiration
Sélectionnez une date de fin pour les valeurs de plage de partition. Au moment de choisir Bordure gauche Sur la page Cartographie des sections cette date sera la dernière valeur pour chacun des groupes de fichiers et partitions. Au moment de choisir Bordure droite Sur la page Cartographie des sections cette date sera la première valeur de l'avant-dernière groupe de fichiers.

Plage de dates
Sélectionnez la granularité de la date ou l’étape de valeur de plage pour chaque section.

Lorsque vous avez terminé avec cette page, cliquez sur le bouton Plus loin.

Sur la page Sélection des paramètres de sortie spécifiez comment remplir la table partitionnée. Sélectionner Créer un script pour créer un script SQL basé sur les données des pages précédentes de l'assistant. Sélectionner Lancez-vous immédiatement pour créer une nouvelle table partitionnée après avoir terminé toutes les pages restantes de l'assistant. Sélectionner Calendrier pour créer une nouvelle table partitionnée à l'avance temps spécifié dans le futur.

Au moment de choisir Créer un script V Paramètres des scripts Les options suivantes seront disponibles :

Script de sortie dans un fichier
Création d'un script sous forme de fichier SQL. Entrez le nom du fichier et l'emplacement dans le champ Nom de fichier ou cliquez sur Revoir pour ouvrir la boîte de dialogue Emplacement du fichier de script. Au chapitre Enregistrer sous sélectionner Texte Unicode ou Texte ANSI.

Script de sortie dans le presse-papiers
Enregistrement du script dans le presse-papiers.

Afficher le script dans une nouvelle fenêtre de requête
Le script est créé dans une nouvelle fenêtre d'éditeur de requêtes. Cette option est sélectionnée par défaut.

Au moment de choisir Calendrier Cliquez sur Modifier l'horaire.

1. Dans la boîte de dialogue Créer un planning de travail dans le champ Nom Entrez un nom pour le planning de travail.

   Sur la liste Type d'horaire sélectionnez le type d'horaire :

   • Démarrer automatiquement au démarrage de l'Agent SQL Server

     Exécuté lorsque les processeurs sont inactifs

     Répétitif. Sélectionnez cette option si la nouvelle table partitionnée est régulièrement mise à jour avec de nouvelles données.

     une fois. Cette option est sélectionnée par défaut.

Cocher ou décocher Inclus pour activer ou désactiver la programmation.

Au moment de choisir Répétitif:

1. Au chapitre Fréquence sur la liste Effectué indiquer la fréquence d'exécution :

   • Au moment de choisir Tous les jours dans le champ Exécuté tous les Spécifiez la fréquence à laquelle le planning de travail sera répété en jours.

     Au moment de choisir Hebdomadaire dans le champ Exécuté tous les Spécifiez la fréquence à laquelle le planning de travail sera répété en semaines. Sélectionnez le ou les jours de la semaine pendant lesquels la planification du travail s'exécute.

     Au moment de choisir Mensuel Cliquez sur Jour ou Précis.

     • Au moment de choisir Jour Entrez la date du mois à laquelle la planification des tâches doit être exécutée et spécifiez la fréquence à laquelle la planification des tâches sera réexécutée en mois. Par exemple, si vous souhaitez que le travail soit exécuté le 15 de tous les deux mois, sélectionnez Jour et entrez « 15 » dans le premier champ et « 2 » dans le deuxième champ. Veuillez noter que le nombre inscrit dans le deuxième champ ne doit pas dépasser « 99 ».

       Au moment de choisir Précis Sélectionnez un jour spécifique de la semaine du mois au cours duquel la planification des tâches doit être exécutée et spécifiez la fréquence à laquelle la planification des tâches sera réexécutée en mois. Par exemple, si vous souhaitez que la tâche soit exécutée le dernier jour de la semaine tous les deux mois, sélectionnez Jour, sélectionner dernier dans la première liste et jour ouvrable dans la deuxième liste, puis entrez « 2 » dans le deuxième champ. Vous pouvez également choisir d'abord, deuxième, troisième ou quatrième, ainsi que des jours spécifiques de la semaine (par exemple, dimanche ou mercredi) dans les deux premières listes. Veuillez noter que le nombre inscrit dans le dernier champ ne doit pas dépasser « 99 ».

Sur le terrain Combien de fois par jour Spécifiez la fréquence de réexécution de la planification de travail le jour de l'exécution de la planification de travail :

• Au moment de choisir Exécuter une fois tous les spécifier une heure spécifique de la journée pour exécuter la planification du travail sur le terrain Exécuter une fois tous les. Entrez l'heure de la journée : heure, minute et seconde.

  Au moment de choisir Exécuté tous les indiquer la fréquence de la tâche le jour sélectionné sur le terrain Fréquence. Par exemple, si vous souhaitez que la planification du travail s'exécute toutes les 2 heures le jour de l'exécution de la planification du travail, sélectionnez Exécuté tous les, saisissez « 2 » dans le premier champ, puis sélectionnez dans la liste montre. Dans cette liste, vous pouvez également sélectionner minutes Et secondes. Veuillez noter que le nombre inscrit dans le premier champ ne doit pas dépasser « 100 ».

  Sur le terrain Commencer à Entrez l'heure à laquelle la planification du travail commence à s'exécuter. Sur le terrain Terminer à Entrez l'heure à laquelle terminer la réexécution du planning de travail. Entrez l'heure de la journée : heure, minute et seconde.

Au chapitre Durée, dans la zone date de début Entrez la date de début d'exécution de la planification du travail. Sélectionner date d'expiration ou Pas de date de fin pour spécifier la date d'achèvement du planning de travail. Au moment de choisir date d'expiration Entrez la date de fin d'exécution de la planification du travail.

Lors de la sélection d'une valeur une fois V Exécution unique dans le champ date Entrez la date de début du planning de travail. Sur le terrain Temps Entrez l'heure de début du planning de travail. Entrez l'heure de la journée : heure, minute et seconde.

Au chapitre Résumé V Description Vérifiez que tous les paramètres de planification de travail sont corrects.

Cliquez sur le bouton D'ACCORD.

Lorsque vous avez terminé avec cette page, cliquez sur le bouton Plus loin.

Sur la page Afficher le résumé Au chapitre Afficher vos sélections Développez toutes les options disponibles pour vous assurer que tous les paramètres de section sont corrects. Si tous les paramètres sont corrects, cliquez sur le bouton Prêt.

Page Performance L'assistant de création de partition est utilisé pour suivre les informations d'état sur les actions de l'assistant de création de partition. Selon les actions sélectionnées dans l'assistant, la page de progression peut contenir une ou plusieurs actions. La zone supérieure affiche l'état général de l'assistant et le nombre de messages d'état, d'avertissement et d'erreur qu'il a reçus.

Sur la page Performance Les options suivantes sont disponibles dans l'assistant de création de section :

Intelligence
Informations sur l'événement, l'état et tous les messages renvoyés suite aux actions de l'assistant.

Action
Spécifie le type et le nom de chaque action.

État
Indique si l'action de l'assistant dans son ensemble a renvoyé une valeur Avec succès ou Erreur.

Message
Tous les messages d’erreur ou avertissements du processus.

Rapport
Créez un rapport contenant les résultats de l'assistant de création de section. Options disponibles: Voir le rapport, Enregistrer le rapport dans un fichier, Et Envoyer le rapport par email.

Consulter le rapport
Ouvrir une boîte de dialogue Consulter le rapport, qui contient un rapport textuel sur le travail de l'assistant de création de section.

Copier le rapport dans le presse-papiers
Copie des résultats du rapport de l'assistant dans le presse-papiers.

Envoyer le rapport par email
Copie les résultats du rapport d'état de l'assistant dans un message électronique.

Lorsque vous avez fini de sélectionner vos options, cliquez sur Fermer.

L'assistant de création de partition créera une fonction et un schéma de partition, puis appliquera la partition à la table spécifiée. Pour vérifier le partitionnement d'une table, dans l'Explorateur d'objets, cliquez avec le bouton droit sur la table et sélectionnez Propriétés. Aller à la page Stockage. La page affiche des informations, notamment le nom de la fonction de partition, le schéma et le nombre de partitions.

Création d'une table partitionnée

DANS Explorateur d'objets Connectez-vous à une instance du moteur de base de données.

Sur le panneau standard, sélectionnez Créer une demande.

Copiez l'exemple suivant dans la fenêtre de requête et cliquez sur le bouton Exécuter. L'exemple suivant montre la création de groupes de fichiers, les fonctionnalités et les schémas de partitionnement. Une nouvelle table est créée lorsque vous spécifiez un schéma de partition comme emplacement de stockage.

UTILISER AdventureWorks2012 ; ALLER -- Ajoute quatre nouveaux groupes de fichiers à la base de données AdventureWorks2012 MODIFIER LA BASE DE DONNÉES AdventureWorks2012 AJOUTER FILEGROUP test1fg ; ALLER MODIFIER LA BASE DE DONNÉES AdventureWorks2012 AJOUTER FILEGROUP test2fg ; ALLER MODIFIER LA BASE DE DONNÉES AdventureWorks2012 AJOUTER FILEGROUP test3fg ; ALLER MODIFIER LA BASE DE DONNÉES AdventureWorks2012 AJOUTER FILEGROUP test4fg ; -- Ajoute un fichier pour chaque groupe de fichiers. MODIFIER LA BASE DE DONNÉES AdventureWorks2012 AJOUTER UN FICHIER (NOM=test1dat1, FILENAME= "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat1.ndf", TAILLE = 5 Mo, TAILLE MAX = 100 Mo, FILEGROWTH = 5 Mo) TO FILEGROUP test1fg; MODIFIER LA BASE DE DONNÉES AdventureWorks2012 AJOUTER UN FICHIER (NOM=test2dat2,FILENAME= "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t2dat2.ndf", TAILLE = 5 Mo, MAXSIZE = 100 Mo, FILEGROWTH = 5 Mo) TO FILEGROUP test2fg; ALLER MODIFIER LA BASE DE DONNÉES AdventureWorks2012 AJOUTER UN FICHIER (NOM=test3dat3,FILENAME= "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t3dat3.ndf", TAILLE = 5 Mo, MAXSIZE = 100 Mo, FILEGROWTH = 5 Mo) TO FILEGROUP test3fg; ALLER MODIFIER LA BASE DE DONNÉES AdventureWorks2012 AJOUTER UN FICHIER (NOM=test4dat4, FILENAME= "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t4dat4.ndf", TAILLE = 5 Mo, MAXSIZE = 100 Mo, FILEGROWTH = 5 Mo) TO FILEGROUP test4fg; ALLER -- Crée une fonction de partition appelée myRangePF1 qui partitionnera une table en quatre partitions CRÉER UNE FONCTION DE PARTITION myRangePF1 (int) COMME PLAGE À GAUCHE POUR LES VALEURS (1, 100, 1000); ALLER -- Crée un schéma de partition appelé myRangePS1 qui applique myRangePF1 aux quatre groupes de fichiers créés ci-dessus CRÉER UN SCHÉMA DE PARTITION myRangePS1 COMME PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg) ; ALLER -- Crée une table partitionnée appelée PartitionTable qui utilise myRangePS1 pour partitionner col1 CREATE TABLE PartitionTable (col1 int PRIMARY KEY , col2 char ( 10 )) ON myRangePS1 (col1) ; ALLER

Définir le partitionnement des tables

La requête suivante renvoie une ou plusieurs lignes si PartitionTable est partitionnée. Si la table n'est pas partitionnée, aucune ligne n'est renvoyée.

Définir des valeurs limites pour une table partitionnée

La requête suivante renvoie les valeurs limites pour chaque partition dans PartitionTable .

SELECT t .name AS TableName, i .name AS IndexName, p .partition_number, p .partition_id, i .data_space_id, f .function_id, f .type_desc, r.boundary_id, r.value AS BoundaryValue FROM sys .tables AS t JOIN sys .indexes AS i ON t .object_id = i .object_id JOIN sys .partitions AS p ON i .object_id = p .object_id AND i .index_id = p .index_id JOIN sys .partition_schemes AS s ON i .data_space_id = s.data_space_id JOIN sys .partition_functions AS f ON s.function_id = f .function_id LEFT JOIN sys .partition_range_values ​​​​AS r ON f .function_id = r.function_id et r.boundary_id = p .partition_number OÙ t .name = "PartitionTable" ET i .type<= 1 ORDER BY p .partition_number;

Définition d'une colonne de partition pour une table partitionnée

La requête suivante renvoie le nom de la colonne de partitionnement de la table. Table de partition.

SÉLECTIONNER t. AS ObjectID , t .name AS TableName , ic.column_id AS PartitioningColumnID , c .name AS PartitioningColumnName FROM sys .tables AS t JOIN sys .index AS i ON t . = je. Et moi.<= 1 -- clustered index or a heap JOIN sys .partition_schemes AS ps ON ps.data_space_id = i .data_space_id JOIN sys .index_columns AS ic ON ic. = i . AND ic.index_id = i .index_id AND ic.partition_ordinal >= 1 -- parce que 0 = colonne non partitionnée REJOIGNEZ sys .columns AS c ON t . =c. ET ic.column_id = c .column_id OÙ t .name = "PartitionTable" ; ALLER

Voir la section pour plus d'informations.

Bonsoir/jour/matin, chers habrapeople ! Nous continuons à développer et à élargir le blog sur mon rdbms open source préféré Postgresql. Miraculeusement, il se trouve que le sujet du sujet d'aujourd'hui n'a jamais été abordé ici. Je dois dire que le partitionnement dans postgresql est très bien décrit dans la documentation, mais est-ce que cela m'arrêtera ?).

Introduction

En général, le partitionnement n'est généralement pas compris comme une sorte de technologie, mais plutôt comme une approche de la conception de bases de données apparue bien avant que les SGBD ne commencent à prendre en charge ce qu'on appelle. tables partitionnées. L'idée est très simple : divisez le tableau en plusieurs parties plus petites. Il existe deux sous-types : les coupes horizontales et verticales.

Cloisonnement horizontal

Certaines parties d'un tableau contiennent différentes lignes. Disons que nous avons une table de journal pour une application abstraite - LOGS. Nous pouvons le diviser en parties : une pour les journaux de janvier 2009, une pour les journaux de février 2009, et ainsi de suite.

Cloison verticale

Certaines parties d'un tableau contiennent différentes colonnes. Trouver une utilisation pour le cloisonnement vertical (lorsqu'il est réellement justifié) est un peu plus difficile que pour le cloisonnement horizontal. En tant que cheval sphérique, je propose d'envisager cette option : la table NEWS a les colonnes ID, SHORTTEXT, LONGTEXT, et laisse le champ LONGTEXT être utilisé beaucoup moins souvent que les deux premiers. Dans ce cas, il est logique de diviser la table NEWS par colonnes (créer respectivement deux tables pour SHORTTEXT et LONGTEXT, reliées par des clés primaires + créer une vue NEWS contenant les deux colonnes). Ainsi, lorsque nous avons seulement besoin d'une description de l'actualité, le SGBD n'a pas besoin de lire l'intégralité du texte de l'actualité sur le disque.

Prise en charge du partitionnement dans les SGBD modernes

La plupart des SGBD modernes prennent en charge le partitionnement des tables sous une forme ou une autre.

• Oracle- prend en charge le partitionnement à partir de la version 8. Travailler avec des sections, d'une part, est très simple (vous n'avez pas besoin d'y penser du tout, vous travaillez comme avec une table ordinaire*), et d'autre part, tout est très flexible. Les sections peuvent être divisées en « sous-partitions », supprimées, divisées, transférées. Diverses options d'indexation d'une table partitionnée sont prises en charge (index global, index partitionné). Lien vers une longue description.
• Microsoft SQL Server- le support du partitionnement est apparu récemment (en 2005). La première impression d'utilisation est "Eh bien, enfin !! :)", la seconde est "Ça marche, tout semble aller bien." Documentation sur msdn
• MySQL- prend en charge à partir de la version 5.1.
• Et ainsi de suite…

*-Je mens, bien sûr, il existe un ensemble standard de difficultés - créer une nouvelle section à temps, jeter l'ancienne, etc., mais d'une manière ou d'une autre, tout est simple et clair.

Partitionnement dans Postgresql

Le partitionnement des tables dans postgresql est légèrement différent dans sa mise en œuvre de celui des autres bases de données. La base du partitionnement est l'héritage de table (une chose unique à postgresql). Autrement dit, nous devons avoir une table principale et ses sections seront des tables successeurs. Nous envisagerons le partitionnement à partir d'un exemple de tâche proche de la réalité.

Formulation du problème

La base de données est utilisée pour collecter et analyser des données sur les visiteurs du ou des sites. Les volumes de données sont suffisamment importants pour penser au partitionnement. Dans la plupart des cas, l'analyse utilise les données du dernier jour.
1. Créez la table principale :

CRÉER UNE TABLE analytique.events

user_id UUID NON NULL ,
event_type_id SMALLINT NON NULL ,
event_time TIMESTAMP DEFAULT now() NOT NULL ,
URL VARCHAR (1024) NON NULL ,
référent VARCHAR(1024),
IP INET NON NULL
);

2. Nous partitionnerons par jour en utilisant le champ event_time. Nous créerons une nouvelle section pour chaque jour. Nous nommerons les sections selon la règle : Analytics.events_DDMMYYYY. Voici un exemple de section pour le 1er janvier 2010.

CRÉER UNE TABLE analytique.events_01012010
event_id BIGINT DEFAULT nextval("analytics.seq_events" ) CLÉ PRIMAIRE,
CHECK(event_time >= TIMESTAMP "2010-01-01 00:00:00" ET event_time< TIMESTAMP "2010-01-02 00:00:00" )
) HÉRITES(analytics.events);

* Ce code source a été mis en évidence avec Source Code Highlighter .

Lors de la création d'une section, nous définissons explicitement le champ event_id (PRIMARY KEY n'est pas hérité) et créons une CONTRAINTE CHECK sur le champ event_time afin de ne pas insérer d'éléments inutiles.

3. Créez un index sur le champ event_time. Lorsque nous partitionnons la table, nous nous attendons à ce que la plupart des requêtes sur la table des événements utilisent une condition sur le champ event_time, donc un index sur ce champ nous aidera beaucoup.

CRÉER UN INDEX events_01012010_event_time_idx ON Analytics.events_01012010 EN UTILISANT btree(event_time);

* Ce code source a été mis en évidence avec Source Code Highlighter .

4. Nous voulons nous assurer qu'une fois insérées dans le tableau principal, les données se retrouvent dans la section qui leur est destinée. Pour ce faire, nous effectuons l'astuce suivante : nous créons un déclencheur qui contrôlera les flux de données.

CRÉER OU REMPLACER UNE FONCTION Analytics.events_insert_trigger()
DÉCLENCHEUR DE RETOUR EN $$
COMMENCER
SI (NEW .event_time >= TIMESTAMP "2010-01-01 00:00:00" ET
NOUVEAU .event_time< TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSÉRER DANS LES VALEURS analytiques.events_01012010 (NOUVEAU .*);
AUTRE
LEVER UNE EXCEPTION "Le % de date est hors plage. Corrigez Analytics.events_insert_trigger", NOUVEAU .event_time ;
FIN SI ;
RETOUR NULL ;
FIN ;
$$
LANGUE plpgsql;

* Ce code source a été mis en évidence avec Source Code Highlighter .

CREATE TRIGGER events_before_insert
AVANT INSÉRER SUR Analytics.events
POUR CHAQUE LIGNE, EXÉCUTER LA PROCÉDURE Analytics.events_insert_trigger();

* Ce code source a été mis en évidence avec Source Code Highlighter .

5. Tout est prêt, nous avons maintenant une table analytique.events partitionnée. Nous pouvons commencer à analyser furieusement ses données. À propos, nous avons créé des contraintes CHECK non seulement pour protéger les sections des données incorrectes. Postgresql peut les utiliser lors de la création d'un plan de requête (cependant, avec un index live sur event_time, le gain sera minime), utilisez simplement la directive constraint_exclusion :

SET contrainte_exclusion = on ;
SELECT * FROM Analytics.events WHERE event_time > CURRENT_DATE ;

* Ce code source a été mis en évidence avec Source Code Highlighter .

Fin de la première partie

Alors qu'est-ce que nous avons? Allons-y point par point :
1. Le tableau des événements, divisé en sections, l'analyse des données disponibles pour le dernier jour devient plus simple et plus rapide.
2. L'horreur de réaliser que tout cela doit être soutenu d'une manière ou d'une autre, les sections doivent être créées à temps, sans oublier de modifier le déclencheur en conséquence.

Je vais vous expliquer comment travailler facilement et sans soucis avec des tables partitionnées dans la deuxième partie.

UPD1 : remplacement du partitionnement par le partitionnement
UPD2 :
Basé sur un commentaire d’un des lecteurs qui, malheureusement, n’a pas de compte sur Habré :
Plusieurs problèmes associés à l’héritage doivent être pris en compte lors de la conception. Les partitions n'héritent pas de la clé primaire et des clés étrangères sur leurs colonnes. Autrement dit, lors de la création d'une section, vous devez créer explicitement des CLÉS PRIMAIRES et FOREIGN KEY pour les colonnes de la section. Je voudrais noter par moi-même que créer FOREIGN KEY sur les colonnes d'une table partitionnée n'est pas la meilleure solution. Dans la plupart des cas, la table partitionnée est une « table de faits » et fait elle-même référence à une « dimension » de la table.

Lorsque nous travaillons sur de grandes tables, nous rencontrons constamment des problèmes de performances lors de la maintenance et de la mise à jour des données. L’une des solutions les plus productives et les plus pratiques aux problèmes émergents est le partitionnement.
En termes généraux, le partitionnement est la division d'une table ou d'un index en blocs. Selon le paramètre de partitionnement, les blocs peuvent être de différentes tailles et peuvent être stockés dans différents groupes de fichiers et fichiers.
Le partitionnement présente à la fois des avantages et des inconvénients.
Les avantages sont bien décrits sur le site de Microsoft, en voici un extrait :

« Le partitionnement de tables ou d'index volumineux peut offrir les avantages suivants en matière de gestion et de performances :

• Cela permet de déplacer et d'accéder rapidement et efficacement à des sous-ensembles de données tout en préservant l'intégrité de l'ensemble de données. Par exemple, une opération telle que le chargement de données depuis OLTP vers un système OLAP s'effectue en quelques secondes, plutôt qu'en minutes ou en heures comme avec des données non partitionnées.
• Les opérations de maintenance peuvent être réalisées plus rapidement avec une ou plusieurs sections. Les opérations sont plus efficaces car elles sont effectuées uniquement sur des sous-ensembles de données plutôt que sur la table entière. Par exemple, vous pouvez compresser des données dans une ou plusieurs partitions ou reconstruire une ou plusieurs partitions d'index.
• Vous pouvez améliorer les performances des requêtes en fonction des requêtes exécutées fréquemment dans votre configuration matérielle. Par exemple, l'optimiseur de requêtes peut exécuter des requêtes d'équijointure plus rapidement sur deux tables partitionnées ou plus si les tables ont les mêmes colonnes de partition, car les partitions elles-mêmes peuvent être jointes.

Lors du tri des données pour les opérations d'E/S dans SQL Server, il trie d'abord les données en partitions. SQL Server ne peut accéder qu'à un seul disque à la fois, ce qui peut réduire les performances. Pour accélérer le tri des données, il est recommandé de répartir les fichiers de données en sections sur plusieurs disques durs, créant ainsi un RAID. De cette façon, même si les données sont triées par partition, SQL Server pourra accéder simultanément à tous les disques durs de chaque partition.
Vous pouvez également améliorer les performances en utilisant des verrous au niveau de la partition plutôt que sur la table entière. Cela peut réduire le nombre de conflits de verrouillage pour la table».

Les inconvénients incluent la difficulté d'administrer et de prendre en charge le fonctionnement des tables partitionnées.

Nous ne nous attarderons pas sur la mise en œuvre du partitionnement, puisque cette problématique est très bien décrite sur le site de Microsoft.

Au lieu de cela, nous essaierons de montrer un moyen d'optimiser le fonctionnement des tables partitionnées, ou plutôt, nous montrerons la manière optimale (à notre avis) de mettre à jour les données pour n'importe quelle période de temps.

Le gros avantage d’une table partitionnée est la séparation physique de ces sections. Cette propriété nous permet d'échanger des sections entre elles ou avec n'importe quelle autre table.
Pour une mise à jour typique des données utilisant une fenêtre glissante (par exemple mensuelle), nous devrons suivre les étapes suivantes :

1. Recherchez les lignes requises dans un grand tableau ;
2. Supprimez les lignes trouvées de la table et de l'index ;
3. Insérez de nouvelles lignes dans le tableau, mettez à jour l'index.

Lorsqu'on stocke des milliards de lignes dans un tableau, ces opérations prendront assez de temps, mais on peut se limiter pratiquement à une action : remplacer simplement la section souhaitée par un tableau (ou section) préparé à l'avance. Dans ce cas, nous n'avons pas besoin de supprimer ou d'insérer des lignes, et nous devons également mettre à jour l'index sur l'ensemble de la grande table.

Passons des paroles aux actes et montrons comment les mettre en œuvre.

1. Tout d’abord, configurez une table partitionnée comme décrit dans l’article mentionné ci-dessus.
2. Nous créons les tableaux nécessaires à l'échange.

Pour mettre à jour les données, nous avons besoin d'une mini copie de la table cible. Il s'agit d'une mini-copie car elle stockera les données qui doivent être ajoutées à la table cible, c'est-à-dire données pour seulement 1 mois. Vous aurez également besoin d'une troisième table vide pour mettre en œuvre l'échange de données. Pourquoi c'est nécessaire - je l'expliquerai plus tard.

Des conditions strictes sont fixées pour la mini-copie et le tableau d'échange :

• Les deux tables doivent exister avant que l'instruction SWITCH puisse être utilisée. Avant qu'une opération de changement puisse être effectuée, la table à partir de laquelle la partition est déplacée (la table source) et la table recevant la partition (la table cible) doivent exister dans la base de données.
• La section destination doit exister et doit être vide. Si une table est ajoutée en tant que partition à une table partitionnée existante, ou si une partition est déplacée d'une table partitionnée à une autre, la partition de destination doit exister et être vide.
• La table secondaire non partitionnée doit exister et doit être vide. Si la partition est destinée à former une seule table non partitionnée, alors la table recevant la nouvelle partition doit exister et être une table vide non partitionnée.
• Les sections doivent provenir de la même colonne. Si une partition passe d'une table partitionnée à une autre, alors les deux tables doivent être partitionnées sur la même colonne.
• Les tables source et destination doivent être dans le même groupe de fichiers. Les tables source et cible d'une instruction ALTER TABLE...SWITCH doivent être stockées dans le même groupe de fichiers, ainsi que leurs colonnes de grandes valeurs. Tous les index, partitions d'index ou vues de partition indexées correspondants doivent également être stockés dans le même groupe de fichiers. Cependant, il peut être différent du groupe de fichiers pour les tables correspondantes ou d'autres index pertinents.

Laissez-moi vous expliquer les limites avec notre exemple :

1. La mini-copie de la table doit être partitionnée sur la même colonne que celle cible. Si la minicopie est une table non partitionnée, elle doit être stockée dans le même groupe de fichiers que la partition à remplacer.

2. La table à échanger doit être vide et doit également être partitionnée par la même colonne ou doit être stockée dans le même groupe de fichiers.

3. Nous mettons en œuvre l'échange.

Nous avons maintenant ce qui suit :
Tableau avec les données pour tous les temps (ci-après Tableau_A)
Tableau avec données pour 1 mois (ci-après Tableau_B)
Table vide (ci-après Table_C)

Tout d’abord, nous devons savoir dans quelle section nous stockons les données.
Vous pouvez le savoir en demandant :

SÉLECTIONNER
Compter comme
, $PARTITION.(dt) comme
, Rank() over (ordre par $PARTITION.(dt))
DE dbo. (aucun verrou)
regrouper par $PARTITION.(dt)

Dans cette requête, nous obtenons des sections contenant des lignes d’informations. Il n'est pas nécessaire de compter la quantité - nous l'avons fait pour vérifier la nécessité d'un échange de données. Nous utilisons Rank pour pouvoir tourner en boucle et mettre à jour plusieurs sections en une seule procédure.

Dès que nous savons dans quelles sections nos données sont stockées, elles peuvent être échangées. Disons que les données sont stockées dans la partition 1.

Ensuite, vous devez effectuer les opérations suivantes :
Échangez les partitions de la table cible avec la table à échanger.
MODIFIER TABLE . COMMUTATION DE LA PARTITION 1 SUR . PARTIE 1
Nous avons maintenant ce qui suit :
Il n'y a plus de données dans la table cible dans la section dont nous avons besoin, c'est-à-dire la section est vide
Échanger les partitions de la table cible et de la vignette
MODIFIER TABLE . COMMUTATION DE LA PARTITION 1 SUR . PARTIE 1
Nous avons maintenant ce qui suit :
Les données mensuelles sont apparues dans la table cible et la mini-copie est désormais vide
Effacez ou supprimez la table d'échange.

Si vous avez un index clusterisé sur votre table, ce n'est pas non plus un problème. Il doit être créé sur les 3 tables partitionnées sur la même colonne. Lors du changement de section, l'index sera automatiquement mis à jour sans reconstruction.

Dans cet article, je vais démontrer les spécificités des plans d'exécution de requêtes lors de l'accès aux tables partitionnées. Notez qu'il existe une grande différence entre les tables partitionnées (qui ne sont disponibles que dans SQL Server 2005) et les vues partitionnées (qui étaient disponibles dans SQL Server 2000 et sont toujours disponibles dans SQL Server 2005 et versions ultérieures). Je démontrerai les spécificités des plans de requête pour les vues partitionnées dans un autre article.

Voir le tableau

Créons une simple table partitionnée :

créer une fonction de partition pf(int) comme plage de valeurs (0, 10, 100)

créer un schéma de partition ps en tant que partition pf all to ()

créer la table t (a int, b int) sur ps(a)

Ce script crée une table avec quatre partitions. SQL Server a attribué des valeurs aux ID de chacune des quatre partitions comme indiqué dans le tableau :

ID de point	valeurs
1	t.a<= 0
2	0 < t.a <= 10
3	10 < t.a <= 100
4	100 < t.a

Examinons maintenant un plan de requête qui forcerait l'optimiseur à utiliser un Table Scan :

……|–Analyse constante (VALEURS :(((1)),((2)),((3)),((4))))
…….|–Analyse de table(OBJET :([t]))

Dans le plan ci-dessus, SQL Server spécifie explicitement tous les ID de partition dans l'instruction « Constant Scan », qui implémente l'analyse de table et fournit les données à l'opérateur de jointure en boucle imbriquée. Il convient de rappeler ici que l'opérateur de jointure en boucle imbriquée parcourt la table interne (dans ce cas, une analyse complète de la table) une fois pour chaque valeur de la table externe (dans notre cas, « Analyse constante »). Nous parcourons donc le tableau quatre fois ; une fois pour chaque ID de section.

Il convient également de noter que la connexion des boucles imbriquées montre clairement que la table externe correspond aux valeurs de la colonne où sont stockés les ID de partition. Bien qu'elle ne soit pas immédiatement visible dans la vue texte du plan d'exécution (malheureusement, nous ne remarquons parfois pas cette information), l'analyse de table utilise une colonne avec les ID des sections sélectionnées pour effectuer l'analyse et déterminer quelle section analyse. Ces informations sont toujours disponibles dans le plan d'exécution graphique (il faut regarder les propriétés de l'opérateur vue table), ainsi que dans la représentation XML du plan d'exécution de la requête :