Me revoici avec ma table de 115 millions de lignes (j'ai perdu accidentellement celle de 125 millions). Vu le nombre de lignes ce n'est pas très important.
Mes indexes bitmap m'ont permis d'avoir des temps de réponse très acceptable sur des requêtes portant sur les notes mais j'ai des soucis car bon nombre de requêtes portent également sur des journées précises en fonction du type de formulaire.
Pour ceux qui débarqueraient, je rappelle l'article d'origine :http://www.lao-dba.com/article-23969555.html
- Une table Reponse_1
- 115 millions de lignes
- 10 colonnes q1 à q10 correspondant à des notes de 0 à 10 pour une grille de 100 formulaires dans 25 pays possibles.
Par ailleurs, je m'étais arreté à décembre 2007, mais bien evidemment tous les jours les statistiques arrivent de part nos 25 pays et continuent d'alimenter notre table.
Pas besoin d'être fort en mathématiques pour comprendre:
décembre 2007=115 millions de lignes
Années 2008 => Plus d' UN MILLIARD DE LIGNES.
L'idée étant de conserver bien sur l'année en cours + une année d'archive: Au bas mot 2,5 milliards de lignes !!
Les intérrogations seront essentiellement par date et par quesionnaire.
Il convient de bien réfléchir à comment stocker ces informations car les manipulations de centaines de millions de lignes ne s'effectuent pas de la même facon que pour une table de quelques milliers de lignes.
Tout devient critique (Statistiques, sauvegarde, maintenance des indexes,...)
Pour les tables à forte volumétrie ORACLE a bien sur pensé à nous, et il a créé le partitionnement.
Cela va revenir à découper notre table selon des critères bien précis.
Il existe trois type de partitionnement:
- Partition par intervalle (BY RANGE)
- Partition par liste (BY LIST)
- Partiyion par haschage (BY HASH)
Dans mon cas, Etant donnée que je vais interroger la table par date et par questionnaire, il peut être jucieux d'effectuer un partitionnement par intervalle sur la date, suivi d'un sous partitionnement par questionnaire. Pour le sous partitionnement, on utilisera un partitionnement par hash.
Afin de permettre à ORACLE d'utiliser au mieux son algorythme de répartition des valeurs, il convient de choisir un nombre de sous partitions qui soit une puissance de 2( 4,8,16,32,64,128,...)
En général les deux raisons qui poussent à effectuer du partionnement sont:
- Une administration plus simple.
- Des gains de performance.
Pour ce qui est de l'administration on en reparlera dans un prochain article.
Pour les performances, c'est assez simple à comprendre.
Admettons que je fasse une requête du type
SELCECT COUNT (*) FROM Reponse_1 WHERE date_questionnaire=20071225 AND id_formulaire=14;
Si ma table est partitionnée par date et sous partitionné par type de formulaire, ORACLE va utiliser cette information pour aller directement dans la partition concernée et va donc travailler sur un sous ensemble de la table.
Assez parlé, crééons une nouvelle table qui prennent en compte le partitionnement.
CREATE TABLE REPONSE_1_P
(
q1 number,
q2 number,
q3 number,
q4 number,
q5 number,
q6 number,
q7 number,
q8 number,
q9 number,
q10 number,
id_pays number,
id_formulaire number,
date_questionnaire number)
PARTITION BY RANGE(date_questionnaire) SUBPARTITION BY HASH(id_formulaire) SUBPARTITIONS 64
(
PARTITION P_20071201 VALUES LESS THAN (20071202),
PARTITION P_20071202 VALUES LESS THAN (20071203),
PARTITION P_20071203 VALUES LESS THAN (20071204),
PARTITION P_20071204 VALUES LESS THAN (20071205),
PARTITION P_20071205 VALUES LESS THAN (20071206),
PARTITION P_20071206 VALUES LESS THAN (20071207),
PARTITION P_20071207 VALUES LESS THAN (20071208),
PARTITION P_20071208 VALUES LESS THAN (20071209),
PARTITION P_20071209 VALUES LESS THAN (20071210),
PARTITION P_20071210 VALUES LESS THAN (20071211),
PARTITION P_20071211 VALUES LESS THAN (20071212),
PARTITION P_20071212 VALUES LESS THAN (20071213),
PARTITION P_20071213 VALUES LESS THAN (20071214),
PARTITION P_20071214 VALUES LESS THAN (20071215),
PARTITION P_20071215 VALUES LESS THAN (20071216),
PARTITION P_20071216 VALUES LESS THAN (20071217),
PARTITION P_20071217 VALUES LESS THAN (20071218),
PARTITION P_20071218 VALUES LESS THAN (20071219),
PARTITION P_20071219 VALUES LESS THAN (20071220),
PARTITION P_20071220 VALUES LESS THAN (20071221),
PARTITION P_20071221 VALUES LESS THAN (20071222),
PARTITION P_20071222 VALUES LESS THAN (20071223),
PARTITION P_20071223 VALUES LESS THAN (20071224),
PARTITION P_20071224 VALUES LESS THAN (20071225),
PARTITION P_20071225 VALUES LESS THAN (20071226),
PARTITION P_20071226 VALUES LESS THAN (20071227),
PARTITION P_20071227 VALUES LESS THAN (20071228),
PARTITION P_20071228 VALUES LESS THAN (20071229),
PARTITION P_20071229 VALUES LESS THAN (20071230),
PARTITION P_20071230 VALUES LESS THAN (20071231),
PARTITION P_20071231 VALUES LESS THAN (20080101)
) ;
Comme vous pouvez le constater, la syntaxe n'a rien d'extraordinaire. On définit l'inervalle pour ma partition "by range" et on indique ensuite le nombre de sous partitions. Ici, j'ai choisis 64, ce qui signigie 64 sous partitions par partition soit un total de 64*31= 1984 sous partitions.
On peut simplifier en disant que ma table de 115 millions est découpée en 1984 espaces de rangement caractérisés par une date et un type de formulaire.
Si l'on a une répartion homogène des formulaires par date, cela nous fait environ 60000 lignes par sous-ensemble, ce qui est beaucoup plus souple à manipuler !
Je vous épargne toutes les options de stockage, mais il bien sur possible de spécifier des tablespaces particuliers pour les partitions et les sous partitions.
Maintenant que notre table REPONSE_1_P est créer, il faut l'alimenter !
INSERT INTO Reponse_1_P SELECT * FROM Reponse_1;
COMMIT;
BEGIN
DBMS_STAT.GATHER_TABLE_STATS(user,tabname=>'REPONSE_1_P',
granularity=>'ALL',
Estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
Simple mais long ! Si vous voulez faire, faites le tourner la nuit (je vous rappelle qu'il faut quelque GO pour la table).
Pour le moment je ne vais pas créer les indexes bitmap, ni d'indexes sur la date ou sur le questionnaire.
Alors une petite démonstration:
On ouvre son petit SQLPLUS
SQL> connect LAO/LAO
SQL> SET AUTOTRACE ON
SQL> SET LINESIZE 131
SQL> SELECT COUNT(*) from Reponse_1_P
Et oh surprise, résultat quasi instantané ! (1 seconde au plus) et pourtant le plan d'execution indique un FULL SCAN sur la table.
Effectivement, on un full scan, mais si vous regardez les étapes 2 et 3 du plan, vous constatez que ORACLE effectue les opération "Partition Range Single" et "Partition Hash Single", et dans les deux dernière colonnes, on peut apercevoir les partitions parcourues par ORACLE !!
Donc un FULL mais uniquement sur les partitions utiles !
Que du bonheur ! une table de 115 millions de lignes sans indexes et des résultats de requetes rapide. En admettant que les types de formulaire soient répartis dans différentes équipes de marketing, on s'assure de la sorte des accès concurents sur la table sans risque de collisions !
Pour ceux qui seraientt décu que je ne sois pas allé plus loin dans les différents type de partitionnement, sous partitionnement et qui se demandent :
- Quand utiliser tel type de partitionnement ?
- Comment indiquer un tablespace pour les partitions et les sous partitionnement
- Quels sont les types de combinaisons possibles ?
- Les indexes partitionnés
- Que se passe t-il pour les indexes
- Sont-il liés à la table ou à la partition ?
- Quelles sont les opérations possibles sur partition ?
- ... et bien d'autres encore
Et bien je réponds, qu'avec ce sujet, j'ai de quoi remplir mon blog pour novembre !!!!
LAO.
AVERTISSEMENT : Oracle Partionning, n'est possible qu'avec une version Enterprise et nécessite une license particulière. Oracle parle de "Extra cost".
