Gestion de tables partitionnées

Version imprimable

08/04/2021, 09h19
nabelou1

Gestion de tables partitionnées

Bonjour à tous,

Ayant travailler plus souvent avec oracle qui gère assez facilement les tables partitionnées, je me demande comment cela se fait dans sql server 2016?

je m'explique:

quand on crée une table partitionnée sur une le mois par exemple,
le jour ou on veut y insérer une valeur sur un mois dont la partition n'existe pas encore, est ce que sql server 2016 la créée automatiquement comme oracle?
sur la version sql server 2012 que j'ai utilisée quelque temps, cela n'etait pas automatique?

Merci d'avance
08/04/2021, 16h44
SQLpro

Voici ce que je dit concernant le partitionnement dans SQL Server dans un article à paraître très prochainement :

"
La solution de Microsoft SQL Server possède de nombreux avantages par rapport aux solutions développées notamment dans certains SGBD relationnels comme Oracle ou PostGreSQL : elle est sans oubli, sans chevauchement et générique.
• Sans oubli parce que la fonction de partition prend en compte l’intégralité des valeurs possibles (domaine) pour le type de données considéré (dans le cas d’une date du 1/1/1 au 31/12/9999 limites des dates dans le langage SQL). Il n’est donc pas possible, par construction, d’oublier un intervalle de partitionnement comme on le voit trop souvent dans la plupart des autres SGBDR, ce qui conduit à bloquer la production.
• Sans chevauchement, par ce que l’on ne créé pas des intervalles, mais on fixe les valeurs des bornes communes aux intervalles (sans aucune répétition) et que, lors de la création du schéma de partitionnement, le système vérifie l’adéquation entre le nombre de bornes indiquées dans la fonction de partition et le nombre d’espace de stockage spécifiées lors de l’exécution de la commande CREATE PARTITION SCHEME… La règle étant celle régissant les « piquets » et les intervalles, à savoir qu’à n piquets correspondent n + 1 intervalles. Là aussi c’est par construction qu’il est impossible de réaliser un chevauchement.
• Générique, car le partitionnement est géré par des objets propres à la base de données et non pas au niveau de la table, ce qui permet de synchroniser l'ensemble des tables et index partitionnés quel qu'en soit le nombre, avec un minimum de commandes.
"

La solution est donc beaucoup plus simple. Pour information, j'ai partitionné 30 tables d'un DW pour un important groupe de supermarché en ne codant que 2 objets, ce qui a nécessité, en tout, 8 lignes de commandes...

A +
23/04/2021, 17h45
Michel.Priori

Bonjour,

Citation:

Envoyé par SQLpro

La solution de Microsoft SQL Server possède de nombreux avantages par rapport aux solutions développées notamment dans certains SGBD relationnels comme
Oracle ou PostGreSQL

... et des inconvénients.

CREATE PARTITION FUNCTION : Crée une fonction qui défini les fameux "piquets" qui sont des valeurs (et non pas une expression)
ALTER PARTITION FUNCTION : Permet SPLIT et MERGE des partitions
+ la préconisation M$ : une table ne devrait pas avoir plus de partitions que le nombre de coeurs

donc si on veut un partitionnement "adaptatif" par rapport au "point chaud" sur un modèle :

-données de plus de 2 ans => tout dans le même partition = P1
-données entre 2 ans et 1 an => une partition par trimestre = P2, P3, P4, P5
-données entre 1 an et 3 mois futurs => une partition par mois = P5 ... P20
-données au delà de 3 mois futurs et 1 an futur => une partition par trimestre = P21 ... P26
-données au delà de 1 an futur => tout dans le même partition = P27
Il faudra faire une série d'ALTER PARTITION FUNCTION tantôt avec des SPLIT tantôt avec des MERGE.

Est-ce, mieux ou moins bien, d'avoir une règle ou une série de valeur ?

bien sûr que cela se scripte mais pourquoi ce n'est pas packagé alors ?

Et si le modèle de partitionnement est infini (une partition par mois, chaque mois on ajoute une partition) il faudra en plus modifier le schéma via ALTER PARTITION SCHEME ... NEXT USED ;
Pas simple ; Même si j'avoue que ce n'est pas un modèle de fonctionnement très stratégique.

De plus le fait d'imposer la colonne portant la valeur de partitionnement comme première valeur de l'index cluster change drastiquement le système de choix du partitionnement ; Les IOT n'étant pas la norme de stockage.

Conclusion :
Il vaut mieux considérer que le partitionnement Oracle et SQL server ne sont simplement pas la même chose.
Chacun d'eux nécessite une compréhension fine du fonctionnement du moteur ainsi que des syntaxes à disposition.

Note : il faut ajouter qu'en version 2019 le partitionnement est pris en charge par la version Express mais avec tellement de restriction que s'est stupide de vouloir le mettre en œuvre sur cette édition. Le comportement entre l'édition standard et entreprise n'est pas le même non plus. A valider.
28/04/2021, 17h44
SQLpro

Citation:

Envoyé par Michel.Priori

Bonjour,

... et des inconvénients.

CREATE PARTITION FUNCTION : Crée une fonction qui défini les fameux "piquets" qui sont des valeurs (et non pas une expression)

Pour les expressions il suffit de créer une colonne calculée persistante !

Citation:

ALTER PARTITION FUNCTION : Permet SPLIT et MERGE des partitions
+ la préconisation M$ : une table ne devrait pas avoir plus de partitions que le nombre de coeurs

Je ne sais pas ou tu a lut ça mais c'est faux, ou tronqué ce qui revient à faux ....

Citation:

donc si on veut un partitionnement "adaptatif" par rapport au "point chaud" sur un modèle :

-données de plus de 2 ans => tout dans le même partition = P1
-données entre 2 ans et 1 an => une partition par trimestre = P2, P3, P4, P5
-données entre 1 an et 3 mois futurs => une partition par mois = P5 ... P20
-données au delà de 3 mois futurs et 1 an futur => une partition par trimestre = P21 ... P26
-données au delà de 1 an futur => tout dans le même partition = P27
Il faudra faire une série d'ALTER PARTITION FUNCTION tantôt avec des SPLIT tantôt avec des MERGE.

Tout cela peut se prépaprer à l'avance

Citation:

Est-ce, mieux ou moins bien, d'avoir une règle ou une série de valeur ?

bien sûr que cela se scripte mais pourquoi ce n'est pas packagé alors ?

Il y a un assistant...

Citation:

Et si le modèle de partitionnement est infini (une partition par mois, chaque mois on ajoute une partition) il faudra en plus modifier le schéma via ALTER PARTITION SCHEME ... NEXT USED ;
Pas simple ; Même si j'avoue que ce n'est pas un modèle de fonctionnement très stratégique.

Encore une fois préparer à l'avance...

Citation:

De plus le fait d'imposer la colonne portant la valeur de partitionnement comme première valeur de l'index cluster change drastiquement le système de choix du partitionnement ; Les IOT n'étant pas la norme de stockage.

Il n'existe aucune norme en matière de stockage. La norme SQL ne concerne que les aspect logiques d'un SGBDR pas les aspects physique. De plus vous n'êtes pas obligé de structurer vos tables en cluster mais dans ce cas il faut assumer d'avoir des performances aussii médiocre qu'oracle !

Citation:

Conclusion :
Il vaut mieux considérer que le partitionnement Oracle et SQL server ne sont simplement pas la même chose.
Chacun d'eux nécessite une compréhension fine du fonctionnement du moteur ainsi que des syntaxes à disposition.

Pour avoir pratiqué les deux, le temps d'implémentation est largement très supérieur dans oracle, avec plein de multiples pièges (comme les "trous" dans les ranges de partitionnement.... !) alorsq que cela n'existe pas dans SQL Server...

Citation:

Note : il faut ajouter qu'en version 2019 le partitionnement est pris en charge par la version Express mais avec tellement de restriction que s'est stupide de vouloir le mettre en œuvre sur cette édition. Le comportement entre l'édition standard et entreprise n'est pas le même non plus. A valider.

Le comportement général de SQL Server n'est pas le même dans les deux éditions à bien des niveaux.... C'est parfaitement voulu...

A +
29/04/2021, 08h47
Michel.Priori
Bonjour,

Citation:

Envoyé par SQLpro

Je ne sais pas ou tu a lut ça mais c'est faux, ou tronqué ce qui revient à faux ....

J'ai lu ça ici : https://docs.microsoft.com/fr-fr/sql...ons-guidelines

Et instinctivement ça me semble correct :
Prenons le cas d'une table partitionnée sur la colonne C1 et ayant un index aligné sur C2.
Lorsque que l'optimizer choisi d'utiliser l'index C2 et que la requête ne lui permet pas de faire du pruning, le traitement va parcourir n "sous" index.

Discutons maintenant de la valeur de n par rapport à x (nb de coeurs)
- Si n=1 : la table n'est pas partitionnée, tous les coeurs sont mobilisables pour parcourir l'index ; non utile.
- Si n=x : alors on peut mobiliser un coeur par index
- Si n>x : alors il faudra temporiser la lecture des index le temps de lecture des premiers
La réponse que je n'ai pas est : Quel est le point mort entre n et x dans un environnement sollicité ?
En absence de réponse le premier qui parle à raison ;)

Pour ma part je choisi de fixer arbitrairement en fonction de la quantité de y =RAM allouable au cache de données et z = taille de la table (hors index)
- z > plusieurs fois n alors n = partie_entière (n/z) * x
- sinon n défini entre 50% x et 90% x (choix arbitraire en fonction de la réalité terrain)
Pour le reste des remarques ce sont juste des questions de point de vue.
Merci pour avoir explicité le tien.