tuning partionner table ou index?

Version imprimable

Bonjour à tous,

J'ai 2 questions :

1) vaut il mieux utiliser une table partitionnée ou un index partitionné?

2) problème sur une manip.
Voilà, j'ai suivit la procédure suivante :
Partitioning Existing Tables à l'adresse suivante
http://www.oracle-base.com/articles/...exes.php#Range

Cette procédure m'a permis de transformer une table non partitionnée avec des données existante en table partitionnée.
J'ai juste modifier le scipt suivant
Code:

1 2 3 4 CREATE TABLE my_table_2 (id NUMBER, description VARCHAR2(50)) PARTITION BY RANGE (id) (PARTITION my_table_part VALUES LESS THAN (MAXVALUE));
en
Code:

1 2 3 4 5 6 CREATE TABLE my_table_2 (id NUMBER, description VARCHAR2(50)) PARTITION BY RANGE (id) (PARTITION my_table_part1 VALUES LESS THAN (10) tablespace "user", PARTITION my_table_part2 VALUES LESS THAN (20) tablespace "user", PARTITION my_table_part1 VALUES LESS THAN (MAXVALUE) tablespace "user");
et donc les alter exchange partition pour les 3 partitions
Code:

1 2 3 4 ALTER TABLE my_table_2 EXCHANGE PARTITION my_table_part1 WITH TABLE my_table WITHOUT VALIDATION; ALTER TABLE my_table_2 EXCHANGE PARTITION my_table_part2 WITH TABLE my_table WITHOUT VALIDATION; ALTER TABLE my_table_2 EXCHANGE PARTITION my_table_part3 WITH TABLE my_table WITHOUT VALIDATION;
avec creation d'une cle primaire
Code:

1 2 alter table my_table add (constraint "code_PK" primary key (id);
Et maintenant si je fait une requete avec une clause where sur id tel que :

Code:

select * from my_table where id='1'

et bien je n'ai aucun resultat, alors que je vous assure qu'il existe !!!!:(

Merci pour votre aide

23/02/2007, 13h19
rouardg

Citation:

Et maintenant si je fait une requete avec une clause where sur id tel que :

SELECT * FROM my_table WHERE id='1'
et bien je n'ai aucun resultat, alors que je vous assure qu'il existe !!!!

C'est normal que tu ne retrouves pas tes données, étant donné que tu as échangé le contenu de cette table avec la 1ère partition.

Essaye de faire un :

Code:

select * from my_table_2 partition (my_table_part1) ;

Je pense que tu vas retrouver toutes tes données dedans.

De plus, les 2 échanges suivants ne servent à rien, vu que tu échanges une table vide, avec des partitions vides.

En fait, je ne comprends pas trop ce que tu veux faire. Mais si tu souhaitais charger ta table partitionnée, il suffisait de faire :

Code:

insert into my_table_2 select * from my_table ;
23/02/2007, 13h27
NGasparotto

Citation:

Envoyé par crazykangourou

Bonjour à tous,

J'ai 2 questions :

1) vaut il mieux utiliser une table partitionnée ou un index partitionné?

Heu, on est obligé de choisir ou l'un ou l'autre ? Je dirais qu'un index partitionné sans que la table le soit, je ne comprendrais pas trop.

Citation:

Envoyé par crazykangourou

...

Code:

select * from my_table where id='1'

et bien je n'ai aucun resultat, alors que je vous assure qu'il existe !!!!:(

Ben, çà semble normal, tu as fais des EXCHANGE... donc tu vides ta table de départ (non partitionnée) dans tes partitions de ta table cible (partitionnée). Donc il ne devrait plus rien y avoir dans ta table my_table. Donc ta requête n'invente pas le résultat.

Nicolas.

PS : bon j'ai été un peu lent sur cette affaire... :?
23/02/2007, 13h39
orafrance

Citation:

Envoyé par NGasparotto

Heu, on est obligé de choisir ou l'un ou l'autre ? Je dirais qu'un index partitionné sans que la table le soit, je ne comprendrais pas trop.

Partitionner une table après coup ce n'est pas faisable. C'est très long et ça peut nécessité pas mal de volume que de recréer la table :?. Quand à l'intérêt... Bah ça en a autant sur une table partitionnée que non partitionné :mrgreen:

C'est à dire que l'index peut être très volumineux et dans ce cas, un partitionnement peut s'avérer salvateur ;)

Un exemple avec 3 partitions et 3 tables non vides:

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
SQL> 
SQL> 
SQL> drop table ptable;
 
Table dropped.
 
SQL> drop table table10;
 
Table dropped.
 
SQL> drop table table20;
 
Table dropped.
 
SQL> drop table table30;
 
Table dropped.
 
SQL> 
SQL> select * from v$version;
 
BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod                
PL/SQL Release 10.1.0.2.0 - Production                                          
CORE	10.1.0.2.0	Production                                                      
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production                         
NLSRTL Version 10.1.0.2.0 - Production                                          
 
SQL> 
SQL> 
SQL> CREATE TABLE ptable (id NUMBER,  description  VARCHAR2(50))
  2  PARTITION BY RANGE (id)
  3  (PARTITION p1 VALUES LESS THAN (10) ,
  4   PARTITION  p2 VALUES LESS THAN (20) ,
  5   PARTITION  p3 VALUES LESS THAN (MAXVALUE));
 
Table created.
 
SQL> 
SQL> 
SQL> CREATE TABLE table10 (id NUMBER,  description  VARCHAR2(50));
 
Table created.
 
SQL> insert into table10 values(1, '1');
 
1 row created.
 
SQL> 
SQL> CREATE TABLE table20(id NUMBER,  description  VARCHAR2(50));
 
Table created.
 
SQL> insert into table20 values(11, '11');
 
1 row created.
 
SQL> 
SQL> CREATE TABLE table30(id NUMBER,  description  VARCHAR2(50));
 
Table created.
 
SQL> insert into table30 values(21, '21');
 
1 row created.
 
SQL> 
SQL> 
SQL> ALTER TABLE ptable  EXCHANGE PARTITION p1	WITH TABLE table10;
 
Table altered.
 
SQL> ALTER TABLE ptable  EXCHANGE PARTITION p2	WITH TABLE table20;
 
Table altered.
 
SQL> ALTER TABLE ptable  EXCHANGE PARTITION p3	WITH TABLE table30;
 
Table altered.
 
SQL> 
SQL> SELECT * FROM table10;
 
no rows selected
 
SQL> SELECT * FROM table20;
 
no rows selected
 
SQL> SELECT * FROM table30;
 
no rows selected
 
SQL> 
SQL> SELECT * FROM ptable;
 
        ID DESCRIPTION                                                          
---------- --------------------------------------------------                   
         1 1                                                                    
        11 11                                                                   
        21 21                                                                   
 
SQL> SELECT * FROM ptable partition (p1) ;
 
        ID DESCRIPTION                                                          
---------- --------------------------------------------------                   
         1 1                                                                    
 
SQL> SELECT * FROM ptable partition (p2) ;
 
        ID DESCRIPTION                                                          
---------- --------------------------------------------------                   
        11 11                                                                   
 
SQL> SELECT * FROM ptable partition (p3) ;
 
        ID DESCRIPTION                                                          
---------- --------------------------------------------------                   
        21 21                                                                   
 
SQL> 
SQL> 
SQL> exit

L'échange de partitions doit être quand même plus rapide que INSERT .. SELECT * dans le cas suivant (http://download-uk.oracle.com/docs/c...htm#sthref2761):

Citation:

Exchanging table partitions is most useful when you have an application using non-partitioned tables that you want to convert to partitions of a partitioned table. For example, in data warehousing environments exchanging partitions facilitates high-speed data loading of new, incremental data into an already existing partitioned table. Generically, OLTP as well as data warehousing environments benefit from exchanging old data partitions out of a partitioned table. The data is purged from the partitioned table without actually being deleted and can be archived separately afterwards

23/02/2007, 14h34
NGasparotto

Citation:

Envoyé par Fred_D

C'est à dire que l'index peut être très volumineux et dans ce cas, un partitionnement peut s'avérer salvateur ;)

Je comprends bien, mais un gros index est, en général, sur une table elle-même déjà relativement volumineuse, et qui est souvent aussi candidate à la partition.

Nicolas.
23/02/2007, 15h09
peter27x

Citation:

Envoyé par NGasparotto

Je comprends bien, mais un gros index est, en général, sur une table elle-même déjà relativement volumineuse, et qui est souvent aussi candidate à la partition.

Nicolas.

Dis moi Nicolas, cela t'arrive t'il de lire tes messages privés ??? 8-)
23/02/2007, 15h41
orafrance

Citation:

Envoyé par peter27x

Dis moi Nicolas, cela t'arrive t'il de lire tes messages privés ??? 8-)

tu vas le faire dans tous les sujets ? :roll:
23/02/2007, 15h42
orafrance

Citation:

Envoyé par NGasparotto

Je comprends bien, mais un gros index est, en général, sur une table elle-même déjà relativement volumineuse, et qui est souvent aussi candidate à la partition.

Nicolas.

certes mais c'est pas forcément utile. En effet, si tu ne fais que de l'index scan alors le partitionnement de la table ne sert pas à grand chose ;) Et surtout, comme je le disais, recréer la table peut être très pénible :?
23/02/2007, 16h01
crazykangourou

Merci pour vos réponse.

Alors je réponds à rouardg : effectivement, je voulais remplir ma table partitionnée en ayant au préalable saisie mes conditions de partitionnement de 10 en 10, puis en echangeant partition par partition, mais bon apparement, mauvaise option.

Je réponds à tout le monde, oui, c'est fastidieux de modifier une table pour la partitionner, surtout que si je comprends bien pour faire une requete sur la partition, il faut faire :

Code:

SELECT * FROM my_table_2 partition (my_table_part1) ;

J'ai donc choisi l'option de faire un index partitionné, ce qui me va trés bien!!!!
Et sur une requete avec jointure contenant une table de 1millions de lignes, en partitionnement avec les index, le gain de temps est vraiment impressionnant!!!

Je peux vous donner plus de détails si vous voulez...

A bientot et encore merci
23/02/2007, 17h21
orafrance

non... pour sélectionner une partition il suffit d'avoir une clause restrictive sur la clé de partitionnement. Tu peux indiquer la partition mais c'est pas une obligation ;)
23/02/2007, 20h14
NGasparotto

Citation:

Envoyé par Fred_D

non... pour sélectionner une partition il suffit d'avoir une clause restrictive sur la clé de partitionnement. Tu peux indiquer la partition mais c'est pas une obligation ;)

C'est même bien contre-indiquer, mieux vaut ne pas le mettre le nom de la sous-partition, Oracle le fais très bien tout seul, comme tu l'as dit, sur la clé de partition.

Nicolas.
23/02/2007, 20h56
orafrance

ha... je ne savais pas que c'était contre-indiqué... tu sais pourquoi ?

Citation:

Envoyé par Fred_D

ha... je ne savais pas que c'était contre-indiqué... tu sais pourquoi ?

Ben, éventuellement tu peux "oublier" des données qui sont dans une autre partition... d'autres part, c'est tout de même assez rare d'avoir à balayer toute une partition.
Et puis, tu peux ne pas utiliser un éventuel index :

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
SQL> explain plan for select * from my_table_2 partition (my_table_part);
 
Explained.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2562846711
 
----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |     4 |   160 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|            |     4 |   160 |     3   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS FULL    | MY_TABLE_2 |     4 |   160 |     3   (0)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement
 
13 rows selected.
 
SQL> explain plan for select * from my_table_2 where id <= 4;
 
Explained.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 487781135
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |     4 |   160 |     2   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| MY_TABLE_2    |     4 |   160 |     2   (0)| 00:00:01 | ROWID
|*  2 |   INDEX RANGE SCAN                 | MY_INDEX_TBL2 |     4 |       |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ID"<=4)
 
Note
-----
   - dynamic sampling used for this statement
 
18 rows selected.
 
SQL>

L'utilisation de la clause PARTITION devrait être des cas rares, il vaut mieux laisser faire Oracle dans ce cas précis.

Enfin, avec le nom de la partition en dur comment fait tu pour interroger les autres partitions ? Il te faut autant de requête disponible que de partition, non ?

Nicolas.

23/02/2007, 21h48
orafrance

et bien nous on l'utilise au boulot pour paralléliser les traitements, on lance 10 SELECT chacun sur une partition différente... c'est bien plus performant que le parallélisme "classique". Bien sûr cela entend d'être sûr de sa clé de partitionnement ;)
23/02/2007, 22h01
NGasparotto

Citation:

Envoyé par Fred_D

et bien nous on l'utilise au boulot pour paralléliser les traitements, on lance 10 SELECT chacun sur une partition différente... c'est bien plus performant que le parallélisme "classique". Bien sûr cela entend d'être sûr de sa clé de partitionnement ;)

Oui pourquoi pas, comme je l'ai dit précédemment, tu as autant de requêtes que de partitions.
Après ca dépend de la fréquence d'ajout/suppression de partition, mais si on en ajoute/supprime une tous les mois, ca peut vite devenir lourd de modifier, d'ajouter, des requêtes (avec le nouveau nom de partition).

Nicolas.