Définition :

Les partitions permettent de découper les objets de base en petites parties plus faciles à manipuler.

• Très répandue dans les datawarehouses. Spécialement lorsque le volume de données augmente.

• C’est une approche de type « diviser pour mieux régner ». Les objets de base de données de type table, index et vue matérialisée peuvent être divisés en morceaux plus petits et plus gérables : les partitions.

• Rend possible des opérations de maintenance des données au niveau des partitions. Les opérations comme:
  • Le chargement de données,
  • La construction des index,
  • La mise à jour des statistiques de l’optimizer Oracle,
  • La purge des données (Truncate Partition),
  • La sauvegarde et la restauration
  • Peut améliorer la performance des requêtes lancées sur la table. L’optimizer Oracle est capable de déterminer si certaines requêtes peuvent obtenir une réponse en parcourant uniquement une ou quelques partitions. De cette manière un parcours complet et couteux de la table (full table scan) peut être évité. Cette caractéristique est appelée Partition Elimination ou Dynamic Partition Pruning.

  Dans des requêtes de type datawarehouse, le partitionnement de table à plusieurs grands avantages :

  1. Les requêtes DSS récupèrent souvent une forte proportion des colonnes de la table. Dans ces conditions l'optimiseur peut choisir de récupérer les données par un full table scan plutot qu'en passant par un index car ce type de scan est réalisé en scannant les données par paquet de block (squattered read) alors que le scan d'index (qui ne contiendrait pas toute les colonnes de la requête) se fait block à block (sequential read) puis les rowid ainsi trouvées sont récupérées dans la table. Lorsqu'il existe des partitions et que le partition pruning s'applique, les données à récupérer peuvent être récupérer par un balayage rapide (scattered read) mais seulement sur les partitions nécessaires.
  2. Le parallelisme (que l'on evoquera dans un autre tutorial) sur ces tables partitionnées est dans certains cas beaucoup plus efficace. C'est le cas dans le cas de jointure entre deux tables partionnées et jointent sur les critères qui forment le partionnement (partition wise join)

Il existe plusieurs type de partitionnement :

• Par Range
• Par List
• Par Hash
• Composite (Range/Hash, Range/List List/Hash pour 9i et 10g et toutes les combinaisons en 11g)

Le partitionnement par Range n'est pas nécessairement homogène et les partitions peuvent avoir des tailles différentes. Dans l'exemple si dessous, la table est partitionnée en mois pour les données des 3 dernières années et de l'année en cours, puis les données plus anciennes sont stockées sur des partitions par année.
La dernière partition "POLD" contient les données les plus anciennes. Un des avantages d'un tel partitionnement est d'affecter les partitions récentes et sensées être plus utilisées dans le reporting sur le buffer pool KEEP (si celui-ci est déclaré et non nul sur la configuration de l'instance). De cette manière les données des partitions récentes vont être placées dans un espace mémoire spécifique et dont la durée de rétention est plus longue que le buffer cache standard.
Les partitions plus anciennes (par année) on quand à elles l'option COMPRESS de manière à minimiser les IOs lorsqu'on accède aux données de ces partitions.

Il est également important de bien comprendre le "partition pruning" :

Le partition pruning ou partition elimination est la capacité qu'a l'optimiseur oracle de déterminer quelles sont la ou les partitions concernées par la requête. Si le prédicat d'extraction (where) est formé de manière convenable pour l'optimiseur, celui-ci sera capable d'appliquer le partition pruning.

Il existe deux types de partition pruning :

• Le pruning statique : le prédicat est un litéral : where jour >= '20100101'
• Le pruning dynamique : le predicat est une variable bindée ou calculée : where jour >= trunc(sysdate,'YYYY')

Dans ces deux cas l'optimiseur est capable de déterminer qu'il lui faudra balayer certaines partitions. Dans le premier cas, il est certain des partitions à balayer, dans le deuxième cas, il ne le saura vraiment qu'au moment de l'execution.

Le partition pruning ne peut pas s'appliquer si la requête utilise une fonction sur la colonne support du partitionnement.

Exemple : where substr(jour,1,6) >= '201001'

Dans ce cas l'optimiseur scannera toutes les partitions !

Il est donc extrêmement important de bien paramétrer son application pour que celle-ci profite pleinement du partitionnement et plus particulièrement du partition pruning.

Partition Wise Join

Les jointures entre deux tables partitionnées sur des critères comparables pourront également bénéficier du partitionnement.

Les jointures seront réalisables sur des jeux de données plus restreint et en parallèle.
Il est possible de combiner les avantages du partition pruning et en plus de profiter d'un partition wise join sur un autre critère en sous partitionnant.

Le partitionnement hybrique avec des sous partitions par hash permet de :

• Profiter du partition pruning sur les partitions mères
• Répartir les données sur les tablepaces/fichiers (c'est une sorte de stripping des données)
• Réaliser des jointures "wise" avec utilisation massive du parallelisme

Le sous partitionnement par hash devra se faire sur une colonne qui permet de répartir de manière équitable les données dans les sous-partitions pour rendre le sous-partitionnement le plus efficace possible. Si vous avez plusieurs colonnes candidates pour le sous-partitionnement prennez la plus utilisée dans les jointures et si vous avez encore des colonnes ex-aequo, prennez la colonne avec le plus de valeurs distinctes.