Discussion :

[Krashtest]

Bonjour,

J'aurai besoin d'un conseil méthodologique de gestion de mes partitions. Voici le pitch, merci d'avance


Suite à la taille croissante de mes tables de faits de mon Datawarehouse, j'ai décidé d'utiliser les options de partitionnement Oracle (je suis en 11g R2).

Chaque mois, nous réalisons des archivages (des photos) de données au sein des tables de fait (c'est à dire que chaque donnée est dupliquée) . Chaque table de fait contient donc une colonne VERSION_ARCHIVAGE qui fait partie de la clé.

Plutôt que d'utiliser des partition "Range" qui ne correspondent pas à notre besoin, j'ai choisi un mode de partitionnement en "Liste"

Ainsi mes tables de fait sont bâties selon le même "standard" avec :
- une partition P_ARC_J1 qui contient les données à J-1 (valeur 'J-1' de la colonne VERSION_ARCHIVAGE) écrasée tous les jours
- une partition P_ARC_MOISM qui contient le dernier mois archivé (valeur '10/2012' de la colonne VERSION_ARCHIVAGE)
- une partition P_ARC_MOISX qui contient les 24 derniers mois archivés (valeur DEFAULT)
- une partition P_ARC_MOISOLDS qui contient tous les autres mois déplacés sur un tablespace d'archive (mois d'archivage listés manuellement)

Cette stratégie me permet :
- d'accélérer les chargements quotidiens de ma table de fait (un TRUNCATE de la partition P_ARC_J1 est moins couteux qu'un DELETE WHERE VERSION_ARCHIVAGE='J-1')
- d'accélérer les requêtes sur le J-1 ou le dernier mois archivé (soit 90% des requêtes environ)


Tout irait pour le mieux dans le meilleur des monde sauf lorsque survient la fin du mois :

en effet, chaque mois, je doit :
- déplacer la version d'archivage 10/2012 de la partition P_ARC_MOISM vers P_ARC_MOISX
- associer le futur archivage 11/2012 à la partition P_ARC_MOISM
- déplacer la version d'archivage 10/2010 dans P_ARC_MOISOLDS, etc, ...


J'aurais aimé créer une procédure PL/SQL, à lancer chaque mois, qui réorganiserait mes partitions
Le problème, c'est que les ordres SPLIT et MERGE sont des ALTER TABLES et donc m’obligerai à passer par des EXECUTE IMMEDIATE dans la procédure (pas bon)
En +, j'aimerai rendre dynamique tout cela en faisant un curseur (une boucle) via 'dba_tab_partitions' sur toutes les tables partitionnées de la base (donc utiliser une variable 'nom_table' utilisée par un USING dans le EXECUTE IMMEDIATE) mais ce n'est pas possible apparemment.

Voila un exemple de code que j'avais commencé à écrire mais qui génère à l’exécution une erreur ORA-06546 dès la ligne 11 ("L'instruction DDL est exécutée dans un contexte interdit") :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
-- on demarre le parcours de la liste des tables candidates
OPEN curTABLES;
LOOP
 FETCH curTABLES INTO v_TABLE;
 EXIT WHEN curTABLES%NOTFOUND;
 
--------------------------------------------------------------------------------------------------------------
 -- Renommage de la partition d'archive P_ARC_MOISX en P_ARC_MOISX_TEMP 
v_txt_ALTER := 'ALTER TABLE :1 RENAME PARTITION P_ARC_MOISX TO P_ARC_MOISX_TEMP'; 
EXECUTE IMMEDIATE v_txt_ALTER USING v_TABLE.NOM_TABLE;
 
--------------------------------------------------------------------------------------------------------------
-- Fusion des 2 partitions au sein de la partition P_ARC_MOISX
--> la mois archivé précédemment est intégré à la partition P_ARC_MOISX
 
v_txt_ALTER :=
	'ALTER TABLE :1
	MERGE PARTITIONS P_ARC_MOISM, P_ARC_MOISX_TEMP
	INTO  PARTITION  P_ARC_MOISX
	NOLOGGING
	COMPRESS FOR OLTP
	UPDATE GLOBAL INDEXES';
 
EXECUTE IMMEDIATE v_txt_ALTER USING v_TABLE.NOM_TABLE;
 
--------------------------------------------------------------------------------------------------------------
-- Re-création de la partition P_ARC_MOISM avec le futur mois d'archivage
 
v_txt_ALTER :=
'ALTER TABLE :1 SPLIT PARTITION P_ARC_MOISX
VALUES (:2) INTO (PARTITION P_ARC_MOISM)
NOLOGGING
COMPRESS FOR OLTP
UPDATE GLOBAL INDEXES';
 
EXECUTE IMMEDIATE v_txt_ALTER USING v_TABLE.NOM_TABLE, v_mois_archive;
 
END LOOP;
COMMIT;
CLOSE curTABLES;

Donc, comment vous y prendriez vous pour gérer mes partitions de manière dynamique et automatique chaque mois ?
Toute aide ou début de solution serait grandement apprécié ! ;-)


Merci de m'avoir lu jusqu'au bout

[skuatamad]

USING c'est pour les variables de liason, pour le nom des tables et des colonnes, il faut concaténer.
Sinon pas d'avis sur la méthode, n'étant pas dba.

[Krashtest]

OK, en effet, cela fonctionne avec une concaténation des variables AVANT l'appel de l'EXECUTE IMMEDIATE

Comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
--------------------------------------------------------------------------------------------------------------
-- (Re)création de la partition P_ARC_MOISM avec le mois d'archivage spécifié (v_mois_archive)
--------------------------------------------------------------------------------------------------------------
 
v_txt_ALTER :=
'ALTER TABLE '||v_TABLE.NOM_TABLE_COMPLET||' SPLIT PARTITION P_ARC_MOISX
VALUES ('''||v_mois_archive||''') INTO (PARTITION P_ARC_MOISM NOLOGGING COMPRESS FOR OLTP, PARTITION P_ARC_MOISX NOLOGGING COMPRESS FOR OLTP)
UPDATE GLOBAL INDEXES';
 
EXECUTE IMMEDIATE v_txt_ALTER;

Merci beaucoup, maintenant c'est vraiment nickel