Théorie du partitionnement

**SheikYerbouti** · 14/04/2010, 09h43

Bonjour,

Je travaille sur une table contenant plusieurs millions d'enregistrements.
Chaque année, elle grossit d'autres millions de lignes.
Le traitement s'effectue toujours sur une année donnée, donc une partie de la table. Le partionnement de cette table sur la colonne annee (number) semble approprié.
Le simple fait de créer les différentes partitions est-il suffisant, ou faut-il également agir sur l'indexation. Aujourd'hui, seule un PK est définie sur cette table. J'ajoute, pour finir, qu'il est hors de question de modifier les requêtes existantes qui agissent sur cette table.
D'avance merci.

**mnitu** · 14/04/2010, 10h06

Envoyé par SheikYerbouti

Bonjour,

Je travaille sur une table contenant plusieurs millions d'enregistrements.
Chaque année, elle grossit d'autres millions de lignes.
Le traitement s'effectue toujours sur une année donnée, donc une partie de la table. Le partionnement de cette table sur la colonne annee (number) semble approprié.
Le simple fait de créer les différentes partitions est-il suffisant, ou faut-il également agir sur l'indexation. Aujourd'hui, seule un PK est définie sur cette table. J'ajoute, pour finir, qu'il est hors de question de modifier les requêtes existantes qui agissent sur cette table.
D'avance merci.

Les requêtes qui utilisent la clé primaire n'ont pas besoin de partitionement.
Les requêtes qui font full table scan vont automatiquement béneficier de la partition.
Mais il faut voir la tête des requêtes pour parler index.
Il y a un chapitre (3) du Effective Oracle By Design du Tom Kyte disponible en télechargement sur sont site qui parle du partitionement.
Il semble que le but du partitionement dans ton cas c'est la performance, est-ce vrai ?

**sgora** · 14/04/2010, 10h13

Tu peux aussi créer des partitions sur tes index, sachant que ce ne sera pas l'idéal pour la PK, puisque tu devras le déclarer en global sur la table, ce qui est moins pratique de d'avoir une partition locale, où on a correspondance parfaite entre partition de table et partition d'index.

On peut ruser pour contourner cela en mettant l'année dans la PK, mais c'est bricoler...

Ca dépend aussi si tes traitements se servent beaucoup de la PK, si ce n'est pas le cas, partitionner les tables suffit à réduire les temps de traitement.

**SheikYerbouti** · 14/04/2010, 10h20

oui, les traitements n'utilisent que la PK.

**sgora** · 14/04/2010, 11h08

Dans ce cas, on peut préfixer la PK par l'année, on pourra alors construire un index partitionné en local sur la table partitionnée, ce qui veut dire qu'on est sûr dans ce cas de n'utiliser qu'une partition de table et la partition correspondante de l'index.

**SheikYerbouti** · 14/04/2010, 11h18

Dans notre cas, l'année est en 2ème position dans la PK et je ne suis pas certain que la passer en première position n'ait auncune incidence sur les requêtes existantes.

**mnitu** · 14/04/2010, 11h22

Envoyé par SheikYerbouti

oui, les traitements n'utilisent que la PK.

Donc partitionner ne servira pas à grand chose.
Mais, est ce que t'as un exemple (table, pk, requête)?
Et c'est quoi le problème ?

**SheikYerbouti** · 14/04/2010, 11h23

Voici le plan sur la table partitionnée avec sa PK sans index particulier:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
SQL> SELECT * FROM XXXX WHERE annee = 2008 and ROWNUM < 100
  2  /
99 ligne(s) sélectionnée(s).
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=99 Bytes=52
          47)
   1    0   COUNT (STOPKEY)
   2    1     PARTITION RANGE (SINGLE) (Cost=2 Card=101 Bytes=5353)
   3    2       TABLE ACCESS (FULL) OF 'XXXX' (TABLE) (Cost=2 Card=1
          01 Bytes=5353)

Il semblerait que la bonne partition sélectionée.

**SheikYerbouti** · 14/04/2010, 11h25

Envoyé par mnitu

Donc partitionner ne servira pas à grand chose.
Mais, est ce que t'as un exemple (table, pk, requête)?
Et c'est quoi le problème ?

Il n'a pas de problème a proprement parler. Je cherche des solutions d'optimisation s'il y en a. cette table fait 70 millions de lignes et il me semblait que le partitionnement était justement fait pour améliorer les temps de traitement des requêtes.

**mnitu** · 14/04/2010, 11h27

Envoyé par SheikYerbouti

Voici le plan sur la table partitionnée avec sa PK sans index particulier:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
SQL> SELECT * FROM XXXX WHERE annee = 2008 and ROWNUM < 100
  2  /
99 ligne(s) sélectionnée(s).
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=99 Bytes=52
          47)
   1    0   COUNT (STOPKEY)
   2    1     PARTITION RANGE (SINGLE) (Cost=2 Card=101 Bytes=5353)
   3    2       TABLE ACCESS (FULL) OF 'XXXX' (TABLE) (Cost=2 Card=1
          01 Bytes=5353)

Il semblerait que la bonne partition sélectionée.

C'est une requête qui fait un FULL et qui n'utilise pas la clé primaire.
Si l'annee est un deuxième position dans l'index il se peut q'un index skip scan en profite.

**mnitu** · 14/04/2010, 11h33

Envoyé par SheikYerbouti

Il n'a pas de problème a proprement parler. Je cherche des solutions d'optimisation s'il y en a. cette table fait 70 millions de lignes et il me semblait que le partitionnement était justement fait pour améliorer les temps de traitement des requêtes.

Je dirait plutôt d'améliorer les temps de traitement des certaines requêtes.

**SheikYerbouti** · 14/04/2010, 11h33

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
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ------------
 SOC                                       NOT NULL NUMBER(11)
 ANNEE                                     NOT NULL NUMBER(11)
 EMPLOYE                                   NOT NULL NUMBER(11)
 C1                                        NOT NULL VARCHAR2(1)
 C2                                        NOT NULL NUMBER(11)
 T01                                               FLOAT(126)
 T02                                               FLOAT(126)
 T03                                               FLOAT(126)
 T04                                               FLOAT(126)
 T05                                               FLOAT(126)
 T06                                               FLOAT(126)
 T07                                               FLOAT(126)
 T08                                               FLOAT(126)
 T09                                               FLOAT(126)
 T10                                               FLOAT(126)

et la pk:

Code :

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

1
2
3
4
5
6
7
8
9
ALTER TABLE XXXX
  ADD CONSTRAINT XXXX_PK PRIMARY KEY (
    SOC,
    ANNEE,
    EMPLOYE,
    C1,
    C2
  )
/

Requete:

Code :

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

1
2
3
4
5
6
7
Select T01
From   XXX
Where  SOC = 
And    ANNEE = 
And    EMPLOYE = 
And    C1 = 
And    C2 =

**sgora** · 14/04/2010, 11h36

Tu n'utilises pas la PK dans ta requête, donc ne change rien à l'index, partitionne la table par année et tu profiteras du "partition pruning" d'Oracle, tu ne balayeras que la partition désirée

**SheikYerbouti** · 14/04/2010, 11h38

Envoyé par sgora

Tu n'utilises pas la PK dans ta requête,...

Je crois que tu as mal lu. Ma requête tape exactement dans la PK

**sgora** · 14/04/2010, 11h40

Ah pardon, oui celle-là utilise la PK, mais dans ce cas le partitionnement n'améliorera pas le temps d'exécution.

SELECT T01
FROM XXX
WHERE SOC =
AND ANNEE =
AND EMPLOYE =
AND C1 =
AND C2 =

**Waldar** · 14/04/2010, 11h42

Vous n'effectuez jamais de calcul d'agrégat ?
La table des ventes que je suis, qui n'est pas foncièrement énorme (40 M lignes) est partitionnée par mois (ça fait des partitions de 250 / 500 K lignes), mais l'intérêt et de pouvoir faire des calculs de CA soit par mois, soit par année, et c'est très rapide ainsi.

Les partitions permettent aussi de gérer facilement les historisations, si vous voulez gardez cinq ans en ligne, avec des partitions c'est enfantin.

**SheikYerbouti** · 14/04/2010, 11h42

Désolé, ja parlais de la dernière requête fournie.

**mnitu** · 14/04/2010, 11h46

Peut être que j’ai raté quelque chose mais, je pense que pour ce type de requête (celle qui utilise les colonnes de la table primaire) le partitionnement ne va pas rapporter grand chose. Je suis curieux du résultat d’un test. Peut être pour que ce type de requête qui utilise toujours l’index partitionner seulement l’index pourrait être intéressant.

J’ai encore deux remarques:

Très probablement que l’index a un bon facteur de clustering, les données arrivant exercice par exercice.

Essaie d’étudier que est-ce que la compression d’index sur le premières 2 colonnes donne. Regarde dans statistique de l'index, la colonne blevel.

**SheikYerbouti** · 14/04/2010, 11h48

... partitionner seulement l'index...

Mais encore ?

**sgora** · 14/04/2010, 11h51

Je conseille pas la compression, à moins que tables et index soient en read only, si on fait insert update ou delete en nombre sur une table comprimée, le prix à payer est important.

Théorie du partitionnement

SQL Oracle

Discussions similaires

Partager

Partager