Dans cet article nous allons aborder un sujet oh! combien sensible et bien trop souvent délaissé par les éditeurs de progiciels et autres applications « maison » la purge des données.
Attention nous parlons bien de purge c’est à dire de l’éradication totale des données et non pas d’archivage qui est une toute autre technique bien que complémentaire mais régi par des règles bien précises qui définissent un toute autre format que celui des données d’origine indépendant de toute contrainte technique. Mais revenons à notre sujet de prédilection la PURGE des données comme vous avez bien souvent pu le constater vous voyez grossir vos chères bases de données de façon outrancière sans qu’il n’existe une procédure ou un traitement qui permettent d’alléger celles-ci des données qui deviennent obsolètes au fil du temps et sur lesquelles plus aucun accès n’est effectué aussi bien en lecture qu’en écriture mais en revanche qui viennent polluer les tables risquant de dégrader fortement les performances. Alors une petite cure d’amaigrissement ne serait pas un luxe pour retrouver une deuxième jeunesse et respecter les canons de beauté qui inondent les unes des magazines bien que je doute qu’une base de données déclenche un tel engouement… L’effet est beaucoup moins attrayant je vous l’accorde…
Alors vers qui se retournera-t-on pour trouver ce remède miracle qui en un minimum de temps nous fera perdre plusieurs tailles ? Mais bien sûr vers le nutritionniste de service, notre cher DBA qui finit par moins ricaner derrière son écran.
Analyse de l’embonpoint
Avant de diagnostiquer le régime le plus efficace qu’il soit, nous devons avant tout étudier la nature de l’embonpoint auquel nous faisons face et délimiter quelles sont les données qui méritent ce régime sec. En général nous avons déjà une petite idée des données qui grossissent au fil de l’eau ou plutôt du temps comme les factures, les commandes, les consommations, les tickets de communication, les transactions financières….. Maintenant reste à identifier les tables sur lesquelles ces données grassouillettes se concentrent, oui mais alors quelle démarche adopter :
- Identifier les tables les plus volumineuses un simple SELECT sur la colonne BYTES de la table DBA_SEGMENTS devrait suffire
- Identifier les tables qui prennent du gras quotidiennement, une simple historisation des colonnes NUM_ROWS et AVG_ROW_LEN de la table DBA_TABLES pour chacune des tables devrait dégager la croissance de celles-ci sur une période d’observation par le simple produit de NUM_ROWS x AVG_ROW_LEN ou plus rapide en contrôlant la colonne INSERT de la table DBA_TAB_MODIFICATIONS qui est utilisée par Oracle pour sélectionner les tables nécessitant un calcul des statistiques (mise à jour supérieure à 10% des lignes de la table).
- Maintenant déterminer les tables communes résultant des deux investigations précédentes pour vous concentrer uniquement sur ce périmètre, sujet de toutes nos préoccupations.
- Contrôler les intégrités référentielles qui pourraient élargir votre périmètre à de nouvelles tables. La requête suivante devrait nous donner amplement satisfaction. Si de nouvelles tables apparaissent alors il faudra élargir la recherche en intégrant ces tables dans le périmètre de recherche car elles peuvent elles-mêmes faire l’objet d’une intégrité référentielle.
select a.TABLE_NAME Fille , b.TABLE_NAME Mere from dba_constraints a, dba_constraints b where a.R_CONSTRAINT_NAME in (select constraint_name from dba_constraints where table_name in ('liste des tables du périmètre')) and a.R_CONSTRAINT_NAME = b.constraint_name order by 2,1;
- Maintenant armé de tous ces éléments il faut impérativement que ce périmètre soit validé fonctionnellement par l’éditeur ou par le service des études s’il existe bien sûr sinon vous prendriez des risques qui pourraient vous coûter très cher.
- Il ne nous reste plus qu’à faire une petite étude sur les requêtes qui portent sur ces fameuses tables callipyges ce qui nous aidera amplement pour vérifier les colonnes qui participent aux différentes jointures. Une bonne idée est de scruter dans la vue V$SQLAREA à fréquence régulière et pour les mieux lotis dans le catalogue DBA_HIST_SQLTEXT où vous devriez trouver votre bonheur.
Critère de la cure d’amaigrissement
Comme il s’agit de données qui grossissent au fil du temps, le seul critère qui soit pertinent est bien sûr une date qui doit impérativement correspondre fonctionnellement à un acte significatif de l’application (prise de commande, consommation, communication, transaction, facturation….). Maintenant il suffit de valider avec les études ou les gestionnaires d’application ce critère et de déterminer de combien vous allez amputer aux données ce superflu de volume… Autrement dit dans un langage plus trivial « couper dans le lard ». Cette période de rétention nous permettra d’affiner et de ciseler la silhouette de la base de données en fonction de nos désidératas.
Le régime d’amaigrissement adéquate
Maintenant il ne nous reste plus qu’à appliquer notre traitement curatif selon nos possibilités et nos moyens.
Le régime drastique
Pour les mieux nantis et à condition que l’on dispose de l’option de partitionnement (qui vaut quand même son pesant de cacahuètes soit dit en passant) et qu’en plus pour toutes les tables par bonheur vous disposez du fameux critère de date alors la solution est toute trouvée c’est de recourir à l’option de partitionnement par intervalle ou ‘range partitioning’ .
Avec la version Oracle 11g cette option a été améliorée ainsi il n’est plus obligatoire de créer à l’avance les partitions car celles-ci sont générées automatiquement dès qu’une nouvelle ligne doit être insérée dans une partition encore inexistante.
Comme toujours il existe certaines restrictions dans l’utilisation de ce type de partitionnement par intervalle
- Une seule colonne ne peut constituer le critère de partitionnement et cette colonne doit être de type DATE ou NUMBER
- Le partitionnement par intervalle n’est pas autorisé sur des tables organisées en indexes IOT
La profondeur de l’intervalle de partitionnement peut se limiter à la journée, la semaine, le mois, le trimestre…. par les fonctions suivantes: INTERVAL (NUMTODSINTERVAL(1,’day’)), INTERVAL (NUMTODSINTERVAL(7,’day’)), INTERVAL (NUMTOYMINTERVAL(1,’month’)), INTERVAL (NUMTOYMINTERVAL(3,’month’))….. Comme le nom des partitions est généré automatiquement selon des conventions internes Oracle il est recommandé de les renommer selon vos propres normes.
ALTER TABLE <nom_de_la_table> RENAME PARTITION SYS_P22 TO P_DAY_22;
Donc la cure d’amaigrissement s’effectuera par une simple suppression de la partition par la commande DROP en respectant bien sûr le séquencement des contraintes référentielles. (d’abord les tables filles puis les tables mères).
ALTER TABLE <nom_de_la_table> DROP PARTITION P_DAY_22;
Une contrainte forte de cette commande DROP PARTITION demeure le fait que tous les indexes soient locaux c’est à dire qu’ils portent uniquement et directement sur une seule partition de la table dans le cas inverse les indexes seront invalidés et devront être reconstruits ce qui nécessite un arrêt du service de l’application et nuit quand même à la disponibilité de la base de données. Reste le cas épineux des indexes primaires qui portent sur la globalité de la table pour qu’ils deviennent locaux il est impératif d’adjoindre le critère de partitionnement dans la définition de clé de ces indexes primaires.
CREATE TABLE "EASY_CONSUMER_ITEM"
( "CONSUMER_ITEM_ID" VARCHAR2(40) NOT NULL ENABLE,
"USER_ID" VARCHAR2(40) NOT NULL ENABLE,
"COMMERCE_ITEM_ID" VARCHAR2(40),
"PRODUCT_ID" VARCHAR2(40),
"SKU_ID" VARCHAR2(40),
"OPTION_ID" VARCHAR2(40),
"CREATION_DATE" TIMESTAMP (6),
"LAST_MOD_DATE" TIMESTAMP (6),
CONSTRAINT "PK_CONSUMER_ITEM" PRIMARY KEY ("CONSUMER_ITEM_ID","CREATION_DATE")
USING INDEX LOCAL PCTFREE 15 INITRANS 30 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "INDEX_CONSO_TBS2" ENABLE VALIDATE
) SEGMENT CREATION IMMEDIATE
PARTITION BY RANGE(CREATION_DATE)
INTERVAL(NUMTODSINTERVAL(1,'day'))
(PARTITION p_first VALUES LESS THAN ('01-JAN-2013'))
TABLESPACE "DATA_CONSO_TBS2";
Cf Documentation Oracle Partionnement
Alors comment transformer vos tables en partitions ? Deux à trois techniques peuvent répondre à vos besoins que nous ne développerons pas ici car à elles seules, elles méritent tout un article, nous noterons juste les points principaux de ces solutions.
- Chargement de nouvelles tables partitionnées cibles à partir des tables sources
- Création de la nouvelle table partitionnée cible
- Insertion des données dans la nouvelle table à partir des données ‘source’
- Suppression de la table source
- Renommer la table partitionnée à sa désignation d’origine
- Création des indexes et des contraintes
- Recompilation des vues, procédures…..
INSERT /*+ APPEND */ INTO <nouvelle_table_partitions> SELECT /*+ PARALLEL */ * FROM <table_source>
- Utilisation du package DBMS_REDEFINITION
- Création de la nouvelle table partitionnée cible
- Démarrage l’opération de redéfinition : Dbms_Redefinition.Start_Redef_Table
- Synchronisation de la table cible avec la source : dbms_redefinition.sync_interim_table
- Création des indexes et contraintes sur la table cible
- Arrêter l’opération de redéfinition : dbms_redefinition.finish_redef_table
- Renommer les indexes et les contraintes à leur nom d’origine
- Export / Import des tables
- Export des tables sources
- Suppression des tables sources
- Création des tables partitionnées
- Import des données des tables sources
Le régime classique
Pour ce régime nous allons adopter une toute autre technique celle des vues matérialisées, contrairement au régime précédent qui supprimait le superflu là nous ne conserverons que le maigre qui sera au-delà de notre période de rétention.
Une vue matérialisée permet de créer une vue physique de la table. A la différence d’une vue standard les données de l’objet source sont dupliquées dans la vue matérialisée (REFRESH COMPLETE) et peuvent être synchronisées à fréquence régulière sur le delta de mise à jour apporté à l’objet source depuis la création de la vue matérialisée ou sa dernière synchronisation (REFRESH FAST).
Pour pouvoir conserver les données de la vue matérialisée nous construirons en premier la table dans laquelle les données de la vue matérialisée seront stockées. A la création de la vue matérialisée nous ferons référence à cette table par l’option ON PREBUILT TABLE. Ainsi nous pourrons par la suite conserver cette table pour qu’elle puisse être renommée à sa désignation d’origine.
Dans notre cas de figure il est impératif que les vues matérialisées puissent être synchronisées mais comme toujours Oracle impose un certain nombre de restrictions qui limite le champ d’application des vues matérialisées en mode REFRESH FAST ou incrémental.
Pour utiliser un rafraichissement incrémental, la vue matérialisée ne doit pas être considérée comme une requête complexe, ainsi elle ne doit pas contenir dans sa requête de constitution :
- La clause CONNECT BY
- L’opérateur ensembliste MINUS, INTERSECT, UNION ALL
- Les mots ou fonctions DISTINCT, UNIQUE, SYSDATE
- Certaines opérations UNION lorsque la liste des colonnes de l’ordre SELECT est différente.
Lorsque la requête de constitution de la vue matérialisée utilise des sous-requêtes, le rafraîchissement incrémental impose de :
- Les journaux des vues matérialisées (MATERIALIZED VIEW LOG) doivent inclure les colonnes de la jointure de la sous-requête.
- La sous-requête doit utiliser la condition EXISTS pour accéder à la requête imbriquée (IN est interdit)
- La condition NOT EXISTS est prohibée
- Chaque table ne peut apparaître qu’une seule fois dans la condition EXISTS et dans les jointures
- Chaque table participant dans les imbrications des sous-requêtes doivent détenir une clé primaire
- Seules les équi-jointures sont autorisées
- Les sous requêtes peuvent utilisées uniquement l’opérateur logique AND (OR lorsque retourne 1 ligne)
- Avec la clause ON PREBUILT TABLE les colonnes de la jointure doivent être présentes dans les journaux de la vue matérialisée (MATERIALIZED VIEW LOG).
Cf Restrictions sur les vues matérialisées avec rafraîchissement incrémental
Pour optimiser la création des vues matérialisées, celle-ci devrait suivre ces deux principes :
- Parallélisation de l’ordre SELECT par l’utilisation d’un HINT Oracle
- Rafraichissement complet de la vue matérialisée avec l’option atomic_refresh initialisé à FALSE, la désactivation de cette option permet d’effectuer un chargement de masse (DIRECT PATH) et non du ligne à ligne.
Maintenant à partir de ce modèle de données qui délimite notre périmètre, prenons pour exemple la création de la vue matérialisée : EASY_DET_PRICE (en rouge)
Le script de création de la vue matérialisée Create_View_EASY_DET_PRICE.sql devrait être le suivant :
SET TIMING ON LINE 132 TIME ON ECHO ON
spool /tmp/Create_View_EASY_DET_PRICE.log
-- Création des journaux des vues matérialisées sur les tables sources avec colonnes pour satisfaire aux restrictions FAST
CREATE MATERIALIZED VIEW LOG ON EASY_ITMPRICE_DET WITH PRIMARY KEY (CUR_PRICE_DETAILS) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON EASY_ITMPRICE_DET WITH PRIMARY KEY INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON EASY_ITEM WITH PRIMARY KEY (PRICE_INFO) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON EASY_CONSUMER_ITEM WITH PRIMARY KEY (COMMERCE_ITEM_ID, LAST_MOD_DATE) INCLUDING NEW VALUES;
whenever sqlerror exit 1;
alter session enable parallel dml;
-- Création de la table de la vue matérialisée
CREATE TABLE EASY_DET_PRICE_MV AS SELECT * FROM EASY_DET_PRICE where 1=2;
-- Création de la vue matérialisée avec passage de l'argument de la date de rétention
CREATE MATERIALIZED VIEW EASY_DET_PRICE_MV
ON PREBUILT TABLE
REFRESH FAST ON DEMAND AS
SELECT /*+ PARALLEL(a,10) */ * FROM EASY_DET_PRICE A WHERE EXISTS
(select /*+ PARALLEL(b,10) */ b.cur_price_details from EASY_ITMPRICE_DET b where b.cur_price_details = a.AMOUNT_INFO_ID and exists
( select /*+ PARALLEL(c,10) */ price_info from EASY_ITEM c where b.AMOUNT_INFO_ID = c.price_info and exists
( select /*+ PARALLEL(d,10) */ commerce_item_id from EASY_CONSUMER_ITEM d
where d.COMMERCE_ITEM_ID=c.commerce_item_id and TRUNC(LAST_MOD_DATE)>TO_DATE('&1','DD-MM-YYYY')))
)
/
-- Chargement complet des données de la vue matérialisée
exec dbms_mview.refresh('EASY_DET_PRICE_MV','C',atomic_refresh => FALSE );
Le traitement de purge avec les vues matérialisées devrait se décomposer en 2 phases car ce traitement nécessite une indisponibilité de la base de données pour l’application du fait du renommage des vues matérialisées à la désignation des tables d’origine. Chacune de ces phases enchaîne un certain nombre d’opérations qui peuvent s’exécuter en parallèle.
- PHASE 1
1. Création des journaux des vues matérialisées sur les tables ‘source’
CREATE MATERIALIZED VIEW LOG ON <table_source> with primary key
2. Création des tables des vues matérialisées et des vues matérialisées (en parallèle)
CREATE TABLE <table_mv> as SELECT * FROM <table_source> WHERE 1=2;
CREATE MATERIALIZED VIEW <table_mv> ON PREBUILT REFRESH FAST AS SELECT.... ;
exec DBMS_MVIEW.REFRESH('<table_mv>','C', atomic_refresh => FALSE);
- PHASE 2
1. Arrêt de l’application, arrêt du listener et ouverture de la base de données en mode restreint.
2. Synchronisation des vues matérialisées en mode incrémental (REFRESH FAST)
exec DBMS_MVIEW.REFRESH('<table_source_mv>','F', atomic_refresh => FALSE);
3. Renommage des tables d’origine, indexes, contraintes
ALTER TABLE <table_source> RENAME CONSTRAINT <constraint_src> TO <cons_bck>; ALTER INDEX <index_source> RENAME TO <index_source_bck>; ALTER TABLE <table_source> RENAME TO <table_source_bck>;
4. Renommage des vues matérialisées
DROP MATERIALIZED VIEW <table_source_mv>; ALTER TABLE <table_source_mv> RENAME TO <table_source>;
5. Reconstruction des indexes
CREATE INDEX <index_source> ON <table_source> (<key_column>);
6. Reconstruction des contraintes sur indexes primaires puis contraintes référentielles
ALTER TABLE <table_source> ADD CONSTRAINT <const_source> PRIMARY_KEY ... ; ALTER TABLE <table_source> ADD CONSTRAINT <const_source> FOREIGN KEY... ;
7. Calcul des statistiques Oracle
exec DBMS_STATS.GATHER_TABLE_STATS('<schema>','<table_source>',degree=>10);
8. Réouverture de la base de données, redémarrage du listener, relance de l’application
Le régime homéopathique
Alors pour ce dernier régime, nous allons tout simplement utiliser la commande SQL connue de tous, le DELETE, mais avant de tailler dans le vif il vaudra mieux conserver les données à supprimer dans une table de sauvegarde on ne sait jamais un retour arrière pourrait être toujours envisageable autant que faire se peut. Une bonne idée est de créer ces tables de sauvegarde avec l’option ROWDEPENDENCIES ce qui nous permettra de conserver la trace des données supprimées comme bon nous semble. Je vous renvoie à l’excellent article traitant de ce sujet. Cf : Comment retrouver la date et l’heure de modification d’une donnée
La seule contrainte de ce type de régime est de respecter l’ordre de suppression des données que nous impose les intégrités référentielles d’abord les tables filles puis les tables mères.
Ce régime peut-être planifié quotidiennement ou hebdomadairement cela dépendra d’une part de la fenêtre de maintenance dont on dispose et d’autre part du volume de données traité mais surtout il faudra tenir compte de l’activité transactionnelle sur la période retenue qui risque de compromettre le traitement par une forte augmentation des attentes sur verrou, alors prudence choisissez une plage horaire avec une très faible activité transactionnelle.
Mais reprenons notre petit exemple avec la table EASY_DET_PRICE et voyons comment cela se traduit.
SET TIMING ON LINE 132 TIME ON ECHO ON spool /tmp/Purge_EASY_DET_PRICE.log -- Création de la table de sauvegarde DROP TABLE EASY_DET_PRICE_BCK; CREATE TABLE EASY_DET_PRICE_BCK ROWDEPENDENCIES AS SELECT * FROM EASY_DET_PRICE where 1=2; whenever sqlerror exit 1; alter session enable parallel dml; -- Chargement des donnés dans la table de sauvegarde INSERT /*+ APPEND PARALLEL(x,10) */ INTO EASY_DET_PRICE_BCK x SELECT /*+ PARALLEL(a,10) */ * FROM EASY_DET_PRICE A WHERE EXISTS (select /*+ PARALLEL(b,10) */ b.cur_price_details from EASY_ITMPRICE_DET b where b.cur_price_details = a.AMOUNT_INFO_ID and exists ( select /*+ PARALLEL(c,10) */ price_info from EASY_ITEM c where b.AMOUNT_INFO_ID = c.price_info and exists ( select /*+ PARALLEL(d,10) */ commerce_item_id from EASY_CONSUMER_ITEM d where d.COMMERCE_ITEM_ID=c.commerce_item_id and TRUNC(LAST_MOD_DATE)<TRUNC(sysdate - &1))) ); COMMIT; --- Suppression des données DELETE /*+ PARALLEL(a,10) */ FROM EASY_DET_PRICE A WHERE EXISTS (select /*+ PARALLEL(b,10) */ b.cur_price_details from EASY_ITMPRICE_DET b where b.cur_price_details = a.AMOUNT_INFO_ID and exists ( select /*+ PARALLEL(c,10) */ price_info from EASY_ITEM c where b.AMOUNT_INFO_ID = c.price_info and exists ( select /*+ PARALLEL(d,10) */ commerce_item_id from EASY_CONSUMER_ITEM d where d.COMMERCE_ITEM_ID=c.commerce_item_id and TRUNC(LAST_MOD_DATE)<TRUNC(sysdate - &1))) ); COMMIT;
Pour réduire à une peau de chagrin la durée du traitement, il est vivement conseillé d’utiliser la parallélisation aussi bien au niveau des requêtes qu’au niveau des différentes opérations.
1. Création des tables de sauvegarde incluse ou non dans le traitement
DROP TABLE <table_source_bck>; CREATE TABLE <table_source_bck> ROWDEPENDENCIES AS SELECT * FROM <table_source> where 1=2;
2. Chargement des données à supprimer dans les tables de sauvegarde
INSERT /*+ APPEND PARALLEL(x,10) */ INTO <table_source_bck> x SELECT /*+ PARALLEL(a,10) */ * FROM <table_source> A WHERE .......
3. Suppression des données
DELETE /*+ PARALLEL(a,10) */ FROM <table_source> a WHERE TRUNC(<critere_date>)<TRUNC(sysdate - <duree_retention>);
4. Reconstruction des indexes en ligne
alter session set ddl_timeout_lock=1800; ALTER INDEX <index_source> REBUILD ONLINE PARALLEL 10;
7. Calcul des statistiques Oracle
exec DBMS_STATS.GATHER_TABLE_STATS('<schema>','<table_source>',degree=>10);
Un dernier conseil avant de vous lancez dans ce genre d’aventure, prenez toutes les précautions pour vous couvrir de tous risques : validation fonctionnelle, tables de sauvegardes, tests en environnement iso-production car à défaut de diminuer la taille de vos bases de données vous risqueriez de faire grossir les statistiques de pôle emploi….. et ce n’est pas le but recherché non mais !!!
