Comment alléger une base trop grosse

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?

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....

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?

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.

Citation:

---

Envoyé par 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. (...)

---

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 ;)

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

Citation:

---

Envoyé par Pomalaix

- version d'Oracle --> Oracle 10g

---

Pour tout le reste, je ne sais pas... En tous cas j'ai accès au partitionnement vu le début du tutoriel*** que j'ai pu faire.

Intriguant ce tutoriel d'ailleurs
Initialement : BIG_TABLE a 10.000 lignes avec des dates de 2010 à 2012
2010 : 333333 lignes
2011 : 333334 lignes
2012 : 333333 lignes

Après les 3 partitionnements ma table a toujours 10.000 lignes mais :

Code:

---

1 2 3

SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'BIG_TABLE';

---

donne :

Code:

---

1 2 3 4 5 6

PARTITION_NAME NUM_ROWS [CHAR ] [FLOAT ] -------------- -------- BIG_TABLE_2010 333875 BIG_TABLE_2011 332932 BIG_TABLE_2012 332421

---

-->la somme fait 999228!
(si je fait un select sur critere created_date, j'ai toujours en réalité le même nombre de lignes pour chaque année)

Citation:

---

Envoyé par stof

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 ?

Citation:

---

Envoyé par Rei Ichido

À quoi correspondraient "les nouvelles données" sans critère de date ?

---

Lesquelles? Quand on recherche une ligne de STK, on passe par les jointures en partant de INV. Je crois que le modèle est bon.

Citation:

---

Envoyé par Rei Ichido

peut-être faire appel à un DBA expérimenté (pour quelques jours) pourrait vous permettre d'avancer ?

---

--> ceux que j'ai sous la main ont déjà donné ce qu'ils pouvaient :lol:

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.

10g ? Dommage !

En 11g, vous avez des nouveautés très intéressantes qui auraient pu vous être utiles :
- partitionnement par intervalles automatiques : les nouvelles partitions se créent dès que vous avez une nouvelle valeur qui le nécessite
- partitionnement par référence : on peut partitionner une table fille à l'identique de la table mère, alors même que la colonne de partitionnement n'existe pas dans la table fille (par exemple une date)

Pour le reste, je rejoins les copains : l'optimisation, et le partitionnement, sont de véritables compétences, pas un bricolage sur un coin de table.

En outre, attention à cette subtilité : le fait que le partitionnement fonctionne ne veut pas dire que vous disposez pour autant de la licence pour l'utiliser légalement. Il faut se référer à votre contrat.

Que donne la requête :

Code:

---

select banner from sys.v_$version;

---

Citation:

---

Envoyé par stof

Lesquelles? Quand on recherche une ligne de STK, on passe par les jointures en partant de INV. Je crois que le modèle est bon.

---

Sauf que si j'ai bien compris (je peux me tromper), la date est rarement un critère de sélection. De ce fait, la partition n'a pas de sens fonctionnel, les requêtes porteront sur toutes les partitions, au final gain nul voire perte.

Par contre, il serait peut-être intéressant de cherche un autre partitionnement. Pour ça, il faudrait voir la tête des requêtes. Par exemple, sur du stock, on peut imaginer que les requêtes les plus utilisées sont du genre "quel est le stock de tel type d'article pour tel magasin", ce qui laisserait envisager un partitionnement par magasin ou par type d'article. Ou plus globalement, par type d'enseigne (regroupement de magasins), etc.

Citation:

---

Envoyé par Waldar

Que donne la requête :
select banner from sys.v_$version;

---

Code:

---

1 2 3 4 5

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Solaris: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production

---

C'est payant même quand on est en "Enterprise Edition"?

En vous lisant, je suis pas sûr effectivement d'y gagner parce que les innombrables requêtes ne sont pas aussi simples que :

Code:

---

select...where THEDATE = ...

---

-->en ayant des tables ayant réellement moins de données, ça marcherait à tous les coups (quoiqu'apparemment tout le monde ne semble pas penser que des dizaines de millions de lignes puissent être un problème, ce dont je doute vu les jointures faites dans plein de requêtes où j'imagine bien que si on tape sur 3 fois moins de lignes on ait des chances d'aller plus vite).

Et en plus il n'y aura personne ne plus compétent pour m'aider à faire ça mieux que "sur un coin de table" ;)
-->je crois qu'il ne me reste qu'à demander à mes chefs d'embaucher des méga spécialistes (:lol:)

PS : bizarre non ce que donne le résultat du tutoriel (cf mon post de 11h47)

Entreprise Edition c’est OK.

Pour le partitionnement faite un test avant de vous lancer dans les grandes manouvres. En ce qui concerne les performances le partitionnement comme toute autre fonctionnalité peut également empirer les choses.

En gros, Num_rows est une estimation.

Citation:

---

Envoyé par stof

[code]C'est payant même quand on est en "Enterprise Edition"?

---

Oui ! Vous payez X pour l'édition Entreprise, plus Y pour l'option de partitionnement.

Citation:

---

Envoyé par mnitu

Num_rows est une estimation.

---

8O

Citation:

---

Envoyé par Pomalaix

Oui ! Vous payez X pour l'édition Entreprise, plus Y pour l'option de partitionnement.

---

8O8O

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 :D

Cordialement,
Franck.