Partition par date et PK

Question

Je suis en train de concevoir une nouvelle base de données de laboratoire.

Mes tableaux de données primaires auront au moins id (PK NUMBER) y created_on (DATE) . De même, pour deux entrées quelconques, l'entrée avec un plus grand id aura une date ultérieure created_on date.

Je prévois de cloisonner par created_on pour augmenter les performances sur les données récemment saisies. Puisque les colonnes augmentent ensemble, la table serait également partitionnée par id implicitement. Oracle n'est pas au courant du partitionnement implicite en id pour tirer profit du partitionnement des jointures de tables sur id .

Deux questions :

1. Comment faire en sorte que les deux colonnes augmentent ensemble ?

2. Comment puis-je tirer parti de ce partitionnement implicite pour les jointures de tables ?

Answer 1

À mon avis, la décision de partitionner devrait être basée davantage sur le besoin d'activités de maintenance des tables (purge, archivage, etc.) que sur les performances. Dans votre cas, je suppose que vous allez probablement effectuer des balayages de plage d'index sur les échantillons pour une plage de dates, donc assurez-vous que l'index de date est également partitionné localement (au lieu de globalement). Cela éliminera également la nécessité de reconstruire l'index si vous tronquez une partition. Je suppose également que les jointures sur le PK utiliseront des recherches par rowid, ce qui se produira après le balayage de plage d'index et il n'y a aucune façon dont le partitionnement peut affecter cela.

[Edit]

En ce qui concerne la mise en relation des colonnes PK et CREATED_ON, je travaille avec quelques systèmes qui construisent la clé numérique à partir d'une séquence préfixée par YYYYMMDD et cela fonctionne assez bien. Vous devrez le faire :

• Estimez librement le nombre de échantillons que vous aurez par jour

• Définissez une séquence qui a ceci comme une valeur maximale et qui revient ensuite à 0

• Avoir une fonction qui renvoie YYYYMMDD || {valeur de la séquence, complétée à gauche par
  des zéros à la longueur fixe appropriée} qui est appelé par un déclencheur ou un code d'application lorsque la clé est nécessaire.

Certains ne sont pas d'accord avec l'intégration d'une signification dans la clé, mais dans la pratique, il est utile de regarder l'ID d'un échantillon et d'avoir une idée de la date de son traitement.

Answer 2

La vraie question importante est la suivante : aurez-vous jamais besoin d'effectuer une requête par plage d'identifiants ? Il est peu probable que vous ayez besoin de construire une requête avec ID BETWEEN :A AND :B . Par conséquent, Oracle ne bénéficierait pas d'un schéma de partition corrélé. Pour autant, vous pourriez utiliser un GUID pour la clé primaire et vous obtiendrez une meilleure évolutivité pour INSERTS .

Answer 3

C'est assez délicat, pour être honnête. Le partitionnement multicolonne est une option, par laquelle vous créez des partitions basées sur des plages sur plus d'une colonne. Dans 11g, vous pouvez l'implémenter sous forme de partitionnement sur la colonne A et de sous-partitionnement sur la colonne B, mais dans 10g, vous devez partitionner par plage sur les deux colonnes ensemble. Je pense que la partie la plus délicate est de savoir sur quelle frontière il faut partitionner car vous voulez probablement que les deux schémas de partitionnement soient "synchronisés".

Answer 4

Dans ce cas, pour accélérer les performances des jointures sur "table_id", vous devez également stocker le "created_on" correspondant dans les tables que vous allez principalement joindre. Si vous faites cela, vous pouvez toujours effectuer des jointures sur "table_id" et "created_on", de sorte que votre "PARTITION RANGE ALL" se transforme en "PARTITION RANGE SINGLE". Vous pouvez mesurer les gains de vitesse et les mettre en balance avec les coûts de stockage supplémentaires.

Edit :

Comment faire en sorte que les deux domaines augmentent ensemble :

ALTER TABLE my_table MODIFY created_on DEFAULT SYSDATE;

Et remplissez l'ID de la séquence dans tous vos inserts.

Answer 5

Comment faire respecter les deux colonnes augmentent ensemble ?

1. En supposant qu'il s'agit d'un chargement groupé et que l'id est une séquence générée au moment du chargement groupé, vous pourriez ALTER SEQUENCE entre les chargements pour avoir plus de contrôle sur la gamme de séquences utilisées pour chaque partition. Si la séquence et la date de création sont attribuées avant le chargement en masse, vous pouvez avoir besoin d'une étape dans votre processus ETL pour déterminer l'identifiant minimum/maximum pour chaque date de création.

2. Partition de gamme sur created_on, Sous-partition de la gamme sur id. Chaque partition ne doit avoir qu'une seule sous-partition.

3. En supposant que, puisqu'il s'agit d'une nouvelle base de données, vous aurez 11g, que diriez-vous d'un contrôle des contraintes sur les colonnes virtuelles ? Colonne virtuelle date_partition

   CASE WHEN created_on BETWEEN ... ET ... THEN "PARTITION_1 WHEN created_on BETWEEN ... ET ... ALORS "PARTITION_2". ... END

Colonne virtuelle similaire sur id_partition, bien que vous deviez faire une requête pour obtenir le PK minimum/maximum pour chaque partition. Cela devrait être rapide car, étant la clé primaire, il y a un index sur elle.

Ensuite, vous ajoutez une contrainte telle que id_partition = date_partition