Partition de table : ajout

[olivanto]

bonjour,

Je suis en 11g, je voudrai partitionnement des tables trop lourdes, sans avoir à les recréer, mais j'ai du mal à trouver la syntaxe...

quelque chose du style

Code :

1
2
3
4
5
6
7
ALTER TABLE maTable
ADD
PARTITION BY RANGE (COL3) -- COL3 est une année.
(
PARTITION PART1 VALUES MORE THAN ('2008'),
PARTITION PART2 VALUES LESS THAN ('2008'))
);

Par ailleurs, ais je besoin de "remplir" ces partitions, ou Oracle se débrouille t-il tout seul ???

merci...

[Pomalaix]

Partitionner une table existante par un simple ALTER TABLE n'est malheureusement pas possible.
Une table ne peut être partitionnée qu'à sa création.
Par contre, DBMS_REDEFINITION peut vous être d'une grande aide, et vous permettra pour ainsi dire de "transformer à la volée une table ordinaire en table partitionnée", sans devoir couper les accès à la table pendant sa transformation.

[olivanto]

aie....

bon, tout d'abord merci.

je vais essayer d'utiliser ce package. J'ai trouvé un exemple complet, malheuresement en anglais.

http://uhesse.wordpress.com/2010/02/..._redefinition/

merci bcp.

[Pomalaix]

Voici un petit exemple commenté en français :

Code :

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
42
43
44
45
46
47
48
49
SELECT * FROM scott.dept;
 
BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','DEPT', dbms_redefinition.cons_use_pk);
END;
/
 
CREATE TABLE scott.dept_int
AS SELECT deptno, dname, loc emplacement, 0 AS effectif FROM scott.dept
WHERE 0=1;
 
-- On ajoute le suffixe _DUP au nom de département lors du transfert des données
BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
         'scott', 'dept','dept_int', 'deptno deptno, dname||''_DUP'' dname, loc emplacement', dbms_redefinition.cons_use_pk);
END;
/
 
-- Si jamais on doit annuler l'opération
--> exec dbms_redefinition.abort_redef_table('SCOTT', 'DEPT', 'DEPT_INT');
 
-- Transfert des objets dépendants
DECLARE
    num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT', 'DEPT','DEPT_INT', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
dbms_output.put_line(num_errors);
END;
/
 
-- Vérification du bon déroulement
SELECT object_name, base_table_name, ddl_txt FROM DBA_REDEFINITION_ERRORS;
 
 
BEGIN
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','dept', 'dept_int');
END;
/
 
BEGIN
   DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','dept', 'dept_int');
END;
/
 
SELECT * FROM scott.dept;
 
-- suppression de la table intermédiaire
DROP TABLE scott.dept_int
cascade constraints;

Sur le principe, il faut noter que DBMS_REDEFINITION ne transforme pas vraiment, mais en donne l'illusion.
Vous devez donc créer votre table partitionnée (avec un nom quelconque temporaire), vide, sans contraintes ni index ni rien.
La transformation va à vrai dire consister à transférer toutes les données de la table source vers cette table cible, et à y reporter index et contraintes.
(On peut aussi si on le souhaite transformer non seulement la structure, mais aussi les données.)
Elle va aussi intervertir les noms de table : à la fin, la table finale partitionnée a le nom d'origine, la table d'origine non partitionnée a le nom temporaire.

Dans mon exemple, je ne partitionne pas, mais j'ajoute une colonne et j'en renomme une autre ; de plus je modifie les données.

La procédure CAN_REDEF_TABLE vérifie si la table peut être redéfinie à la volée, car ce n'est pas toujours possible.
START_REDEF_TABLE démarre les transformations.
COPY_TABLE_DEPENDANTS transfère tous les objets attachés : index, contraintes, déclencheurs
SYNC_INTERIM_TABLE est facultatif
FINISH_REDEF_TABLE conclut les opérations, et effectue le renommage final.

[olivanto]

merci beaucoup.

ca a le mérite d'être plus clair. Je vais m'y mettre...

[olivanto]

bonjour,

Je remonte ce post pour deux questions (je sais qu'il y a un autre post sur le même sujet, mais je continue mes questions sur le mien !).

1- j'ai partitionné, en test, une table sur un critère de date (en gros, j'ai une partition des données 'actuelles' sur les 4 dernières années, et une autre des données considérées comme 'obsolètes').
Dois je ajouter un index sur ce critère de date pour accélerer la recherche dans la table, ou la partition sur ce critère suffit t-elle, sur ce point précis ?

2- comment puis-je faire des tests de performances sur une table AVANT/APRES partition (afin de savoir si ce que j'ai fait, est utile ?)


3- pourquoi je ne vois pas la taille des partitions, alors que je vois parfaitement le nombre d'enregistrements (je travaille sous Toad).

merci,

[Mohamed.Houri]

Envoyé par olivanto

bonjour,

Dois je ajouter un index sur ce critère de date pour accélerer la recherche dans la table, ou la partition sur ce critère suffit t-elle, sur ce point précis ?

Le but du partitionnement peut-être:

(a) pour améliorer la performance
(b) pour faciliter la maintenance
(c) pour faire des purges et des archivages

Pour atteindre l'objectif (a) il faut tout de même que les nouvelles requêtes fassent de l'élimination de partition (partition pruning ou partition elimination).

Pour que cela soit possible, il suffit que la clause where contienne le critère sur la clé de partitionnement (sur la date dans votre cas). L'index n'est pas nécessaire pour avoir cette elimination de partition. Par contre, si en plus de faire une elimination de partition, vous accédez à cette partition via un index ceci peut s'avérer intéressant.

Lisez le lien suivant (il contient des informations valables)

https://forums.oracle.com/forums/mes...247097#9247097

2- comment puis-je faire des tests de performances sur une table AVANT/APRES partition (afin de savoir si ce que j'ai fait, est utile ?)

En faisant des tests avant et des tests après et en comparant les temps de réponse. N'oubliez pas que les inserts seront plus lent après qu'avant car oracle doit localiser la partition dans laquelle il va insérer. Mais ceci ne doit pas vous tracasser.

[olivanto]

tout d'abord merci pour cette longue réponse. J'essaie d'atteindre les trois objectifs, mais avant tout c'est un problème de performances, que je souhaite voire améliorées.

une chose me "choque" dans votre réponse.

"N'oubliez pas que les inserts seront plus lent après qu'avant car oracle doit localiser la partition dans laquelle il va insérer"

Soit, cela a l'air logique. Mais cela me chagrine...comment les améliorer, alors ?

En contrepartie, puis je prétendre que les updates, et les select seront plus rapides, si dans le requete se trouve ce champ DATE, qui sert à différencier les deux partitions ? (sinon, je n'ai vraiment rien compris au partitionning...)

[Mohamed.Houri]

Ne vous inquietez pas pour les inserts. C'était juste pour votre information.

En contrepartie, puis je prétendre que les updates, et les select seront plus rapides, si dans le requete se trouve ce champ DATE, qui sert à différencier les deux partitions ? (sinon, je n'ai vraiment rien compris au partitionning...)

Effectivement. Grâce à la présence de la clé de parititionnement dans les where clauses des selects et des updates, ces derniers seront plus rapides.

Cependant, posez-vous la question suivante: A quoi sert de partitionner une table si vous avez l'intention de ne créer que deux partitions?

Il faut voir le partitionnement comme étant une serie de tables plus petites en taille qu'on accède et manipule très facilement grâce à la clé de partitionnement. Si vous utilisez uniquement deux partitions, cela ne vaut pas le coup.

[Waldar]

Vous vous embêtez pour rien, en 11g vous avez le partitionnement par intervalle qui se charge de créer pour vous les nouvelles partitions.

Si votre table est déjà partitionnée vous pouvez rajouter l'intervalle à posteriori :

Code :

1
2
ALTER TABLE maTable
SET interval (<votre intervalle>);

[olivanto]

Envoyé par Mohamed.Houri

Cependant, posez-vous la question suivante: A quoi sert de partitionner une table si vous avez l'intention de ne créer que deux partitions?

Il faut voir le partitionnement comme étant une serie de tables plus petites en taille qu'on accède et manipule très facilement grâce à la clé de partitionnement. Si vous utilisez uniquement deux partitions, cela ne vaut pas le coup.

JE suppose que tout dépend de la taille de la table ; dans mon cas, au lieu de balayer les millions d'enregistrements obsolètes lors de mise à jours internes, on ne tape plus sur les enregistrements de la partition des éléments obsolètes (30 ans de données....).
Donc, bcp moins d'enregistrements (70%), donc je suppose traitement plus rapide.

Non ?

[olivanto]

Envoyé par Waldar

Vous vous embêtez pour rien, en 11g vous avez le partitionnement par intervalle qui se charge de créer pour vous les nouvelles partitions.

Si votre table est déjà partitionnée vous pouvez rajouter l'intervalle à posteriori :

Code :

excuse moi, mais tu peux développer s'il te plait ??? Est ce à dire que j'ai perdu un temps fou à comprendre comment partitionner mes tables après coup avec RBDMS REDEFINITION ???

[Mohamed.Houri]

Envoyé par olivanto

JE suppose que tout dépend de la taille de la table ; dans mon cas, au lieu de balayer les millions d'enregistrements obsolètes lors de mise à jours internes, on ne tape plus sur les enregistrements de la partition des éléments obsolètes (30 ans de données....).
Donc, bcp moins d'enregistrements (70%), donc je suppose traitement plus rapide.

Non ?

Et lorsqu'on veut voir(sélectionner) les données obsolètes on va se "taper" les 30 ans de données !!!

Certes vous n'allez pas attaquer les 30 ans de données lors d'update mais je suppose que vous allez sélectionner ces données de temps à autres. N'est-ce pas?

[olivanto]

oui, vu comme çà....

bon, en même temps, tester çà sur une base de PROD était plutôt destiné à me familiariser avec les commandes RDBMS...

merci pour ces remarques constructives !

[Waldar]

Envoyé par olivanto

excuse moi, mais tu peux développer s'il te plait ??? Est ce à dire que j'ai perdu un temps fou à comprendre comment partitionner mes tables après coup avec RBDMS REDEFINITION ???

Je ne sais pas si vous avez perdu du temps, dans la mesure où on apprend quelque chose, ce n'est jamais perdu.

Je sais juste que j'ai une table partitionnée par mois et que je ne fais rien dessus, le processus alimentant les données vérifie aussi s'il doit renommer une partition.

Code :

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
CREATE TABLE test_part
(
    col1    number,
    col2    date,
    col3    number,
    constraint pk_test_part
      PRIMARY KEY (col1)
)
partition BY range (col2)
interval (numtoyminterval(1,'YEAR'))
(
  partition test_part_2010 VALUES less than (date '2011-01-01')
);
-- Table created.
 
INSERT INTO test_part VALUES (1, date '2010-06-01', 1);
-- 1 row created.
 
commit;
-- Commit complete.
 
SELECT table_name, partition_name, high_value
  FROM user_tab_partitions
 WHERE table_name = 'TEST_PART';
 
TABLE_NAME         PARTITION_NAME      HIGH_VALUE                                                                      
------------------ ------------------- -----------------------------------------------------------------------------------
TEST_PART          TEST_PART_2010      TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
 
 
INSERT INTO test_part VALUES (2, date '2011-06-01', 1);
-- 1 row created.
 
commit;
-- Commit complete.
 
SELECT table_name, partition_name, high_value
  FROM user_tab_partitions
 WHERE table_name = 'TEST_PART';
 
TABLE_NAME         PARTITION_NAME      HIGH_VALUE                                                                      
------------------ ------------------- -----------------------------------------------------------------------------------
TEST_PART          SYS_P1395166        TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST_PART          TEST_PART_2010      TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
 
ALTER TABLE TEST_PART  RENAME partition SYS_P1395166 TO TEST_PART_2011;
-- table altered.
 
SELECT table_name, partition_name, high_value
  FROM user_tab_partitions
 WHERE table_name = 'TEST_PART';
 
TABLE_NAME         PARTITION_NAME      HIGH_VALUE                                                                      
------------------ ------------------- -----------------------------------------------------------------------------------
TEST_PART          TEST_PART_2011      TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST_PART          TEST_PART_2010      TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

[olivanto]

deux choses ;

1 -

2 -

Bon, maintenant que j'ai commencé RDBMS...je vais continuer un peu pour bien comprendre le machin, et ensuite passer à ton explication ; comme je viens de passer ma base en 11g le mois dernier, autant comprendre...
Merci bcp de ton intervention...

[Pomalaix]

Envoyé par olivanto

Est ce à dire que j'ai perdu un temps fou à comprendre comment partitionner mes tables après coup avec RBDMS REDEFINITION ???

Comme je retombe là-dessus, peut-être que des précisions peuvent encore servir.

Je confirme ce que je disais plus haut : une table non partitionnée ne peut pas être transformée en table partitionnée par un simple ALTER TABLE.
Il y a alors deux solutions :
- Si la table n'est pas utilisée 24 heures sur 24, alors vous créez la table partitionnée et vous insérez dedans les données de la table normale
- Si la table doit rester accessible 24 heures sur 24, alors il faut utiliser DBMS_REDEFINITION.

Le partitionnement par intervalles automatiques (nouveauté V11) est juste une commodité pour l'ajout de partitions supplémentaires.
Il peut être activé à deux moments :
- lorsque vous créez la table partitionnée
- plus tard, par ALTER TABLE, mais ceci exige que la table soit déjà partitionnée BY RANGE.