Discussion :

[stof]

Bonjour,

Dans certaines tables, on atteint les 14 millions de lignes en 2 ans; je pense qu'entre les insertions et les lectures, ça devient lourd (à la fois performance de recherche de données, les insertions croisées avec des modifications qui peuvent se bloquer parfois pendant quelques minutes...).

Bref :
solution1 --> on créer des tables XXX_sav qu'on remplit quand on veut archiver mais ça force à rediriger sur XXX ou XXX_sav dans toute l'application, créer autant de classes de mapping Hibernate, etc (sans compter tous les risques d'être parfois entre les 2 dans une requête, de louper les traitements post-insertion sur un élément similaire déjà existant...).

solution2 --> j'ai vu les histoires de partitionnement; ça semble compliqué aussi :
-ça ne se fait que sur des nouvelles tables,
-apparemment faut aussi faire des index partitionnés
-si on cherche hors critère de partitionnement on ne gagne rien
+si TAB_A (A_id, A_date, A_value) et SUBTAB_A (SA_id, A_id, SA_value) on n'a pas de critère de partitionnement sur SUBTAB_A puisque la date est dans TAB_A.

-->quelle est la meilleure solution pour accéder facilement aux anciennes données alors qu'on travaille 99% du temps sur les 4-5 derniers jours?

[amir.]

En fait, tu peux partitionner une table existente. Il y a plusieurs techniques (exchange partition, dbms_redefinition...). Ce n'est pas compliqué tu sais

Quand tu crée une table partitionnée, tu n'es pas obligé de créer des index partitionés. Tu peux créer des index GLOBAL et c'est bon. Mais, les index partitionnées peuvent aider.

Il y a plusieurs avantages aux tables partitionnées... Entre autre pour les backups. Mais dans ton cas, tu es plus intéressé par l'utilisation quotidienne.

Si tu accèdes dans 99% des cas aux derniers jours, je te conseillerai vivement de partionnée ta table (voire même partition + sub-partition). Lorsque tu accèdes à une table partitionée en spécifiant dans tes critères de restriction la clé de partitionnement (dans ce cas-ci probablement une date), alors l'optimiseur pourrait se limiter à ne parcourir qu'une partition (dans ce cas, la dernière).

Honnêtement, réfléchi bien à ton problème avant de te lancer. Et si tu as besoin d'un conseil, expllique bien on problème en donnant le contexte et les volumétries en jeux. 14 milions d'enregistrement contenant une date et 2 nombres c'est pas vraiment très gros quoi....

[stof]

Bon, c'est donc le partitionnement qu'il me faut.
Il faut donc bien déplacer les données dans une nouvelle table partitionnée (il y a tellement de dépendances entre mes tables que je ne suis pas sûr que ça soit simple avec les contraintes).


Sauf que dans les exemples, "big_table" (la table à partitionner) est celle qui contient la date.

Sauf que moi, voici la structure réelle :
INV (inventaires) : 300.000 lignes
-->8 colonnes dont INVIDT (PK), THEDATE et autres infos sur l'inventaire
INVLIG(lignes d'inventaire) : 14.000.000 lignes
-->11 colonnes dont INVLIGIDT (PK), INVIDT (FK) et autres infos générales sur la ligne
Et on a 2 types de lignes d'inventaires :
STK (lignes de stock) : 11.000.000 lignes
-->15 colonnes dont INVLIGIDT (PK), et autres infos sur la ligne
LIQ (lignes de liquidités) : 3.000.000 lignes
-->5 colonnes dont INVLIGIDT (PK), et autres infos sur la ligne

Question1 : Donc avec cette méthode, je vois bien comment partitionner INV mais pas les autres qui n'ont une référence à la date que de manière indirecte.

Question2 : J'ai des index sur différents champs qui peuvent être critères de sélection --> GLOBAL ou LOCAL?

Question3 : Je découpe en 2010, 2011, 2012? Et début 2013 je dois de nouveau refaire le "cirque" de partitionnement?

Question4 : Si je devais retourner des données qui chevauchent 2011 & 2012 d'après les critères de recherche, Oracle saurait se débrouiller?

[mnitu]

J’ai l’impression que vous avez un problème de performance mais pour l’instant vous ne savez pas très bien identifier la cause. Donc vous avez conclu que c’est la volumétrie des données qui sera la cause. Par voie de conséquence vous cherchez une solution « simple » à ce problème de volumétrie. Le problème avec cette approche est qu’après avoir appliqué la « solution » il se pourrait que votre application ne tourne pas mieux.

[stof]

J’ai l’impression que vous avez un problème de performance mais pour l’instant vous ne savez pas très bien identifier la cause. (...)

pas faux mais tout le reste a déjà été fait : pose des index après étude complète des requêtes utilisées, mise en cache de certaines données autant que possible dans l'appli...
Donc à tort ou à raison, je me dis qu'avec l'accumulation des données, la situation va aller de moins en moins bien.
Mais je reste ouvert à toute autre suggestion

[Pomalaix]

D'autres précisions seraient utiles :
- version d'Oracle
- édition (standard, entreprise)
- options que vous avez (partitionnement, etc)
- êtes-vous prêt à payer des options complémentaires si nécessaire

[Rei Ichido]

pas faux mais tout le reste a déjà été fait : pose des index après étude complète des requêtes utilisées, mise en cache de certaines données autant que possible dans l'appli...
Donc à tort ou à raison, je me dis qu'avec l'accumulation des données, la situation va aller de moins en moins bien.
Mais je reste ouvert à toute autre suggestion

Je rejoins Mntitu sur ce coup là : avancer à tâtons risque de causer plus de problèmes que d'en résoudre.

Le partitionnement est une bonne option dès lors qu'on a effectivement un partitionnement fonctionnel naturel. Ce qui ne semble pas vraiment être le cas ici ! À quoi correspondraient "les nouvelles données" sans critère de date ?

À mon avis il faut faire simple, et partir des problèmes de performance identifiés. Vu la question sur le partitionnement, je pense qu'il vous manque un DBA ; peut-être faire appel à un DBA expérimenté (pour quelques jours) pourrait vous permettre d'avancer ?

[doc malkovich]

Le partitionnement est la meilleure solution mais attention, c'est une option payante.

Tu as aussi la compression si tu penses avoir des pbs d'io.

[pachot]

Bonjour,

A mon avis, l'accumulation des données historiques, ça justifie le partitionnement vu que vous êtes en Enterprise Edition. Et ce indépendamment des problèmes de performance.
D'une part parce qu'un jour il faudra bien penser à purger les vieilles données. Et plus vous attendrez plus ce sera difficile à faire. Alors que quand c'est partitionné, ça prend 5 minutes.

D'autre part, même pour des requêtes qui doivent lire tout l'historique, le partitionnement sera utile car les jointures pourront se faire partition par partition (Partition Wise Join).

Et puis je suis sûr que beaucoup de requêtes n'ont besoin que d'une plage de date.

Pour moi, il y a déjà un problème de modélisation à la base. J'ai l'impression qu'ici une PK technique a été mise dans toutes les tables sans réfléchir. C'est parfois recommandé pour des raisons de simplicité et flexibilité. Mais quand on a des millions de lignes il faut avoir un vrai modèle physique de données. Parce que rajouter des clés techniques partout c'est rajouter des index à maintenir lors des inserts, c'est faire plus de jointures pour aller chercher les données de la clé naturelle, et c'est se limiter fortement dans les possibilités d'optimisations comme le partitionnement.

Je ne connais pas votre modèle métier. Mais s'il était possible d'avoir l'année dans la PK de inventaire, beaucoup de problèmes seraient résolus. La PK de INV serait (ANNEE,invno). La PK de INVLIG serait (ANNEE,invno,ligno). Du coup:
- pas besoin de rajouter une colonne et surtout un index sur les clés fabriquées (INVIDT,INVLIG)
- pas besoin d'aller faire des jointures juste pour aller charcher la date.
- partitionnement par date partout car la date est disponible partout (FK).
Et tout ça sans rien avoir à dénormaliser.

Donc les requêtes qui se font sur une plage de date seront rapides: on a la date dans toutes les tables pour filter avant de faire la jointure.
Et pour les requêtes qui se font sur tout l'historiques, la jointure se fera partition par partition. Même avec 20 ans d'historique, on peut maintenir le même temps de réponse.

Quelles sont les clés naturelles (i.e métier) des tables ? Si ces clés sont immuables (valeur connue dès l'insert et pas d'update par la suite) alors c'est celles là qu'il faut utiliser pour les contraintes foreign key.
Et s'il y en a qui disent que ce n'est pas possible avec hibernate, c'est faux. C'est juste un tout petit peu plus complexe. Mais on ne peut pas stocker des millions de lignes tous les ans et les interroger sans réfléchir un peu

Cordialement,
Franck.