Discussion :

[dtateur]

Bonjour,

Nous avons une table contenant aujourd'hui 10 Millions de lignes.
Cette table grossit d'environs 2 Millions de lignes par mois.
Voici la structure de ma table :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Name                              Type
 ----------------------------- -------- -------------------
TABLE_ID                           NUMBER(10)
TABLE_DATE                       DATE
COLUMN_A                         VARCHAR2(100)
COLUMN_B                         VARCHAR2(100)
COLUMN_C                         VARCHAR2(10)
COLUMN_D                         VARCHAR2(30)
COLUMN_E                         VARCHAR2(50)
COLUMN_F                         VARCHAR2(30)
COLUMN_G                         VARCHAR2(50)
COLUMN_H                         VARCHAR2(30)
COLUMN_I                          VARCHAR2(30)

Nous avons un écran de recherche permettant de requêter cette table.
A partir cet écran, nous pouvons faire une recherche sur n'importe quelles colonnes de cette table.
Voici des exemples de requêtes :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT * FROM MA_TABLE WHERE COLUMN_A = 'valeura' AND COLUMN_B = 
'valeurb' AND COLUMN_I IN ('val1', 'val2', 'val3', 'val4');
 
SELECT * FROM MA_TABLE WHERE COLUMN_B = 'valeurb' AND COLUMN_E IN ('val10', 'val20', 'val30', 'val40');
 
SELECT * FROM MA_TABLE WHERE TABLE_DATE between '01-DEC-2013' AND '01-JAN-2014' AND COLUMN_A IN ('vala', 'valb', 'valc', 'vald') AND COLUMN_B = 'valeurb' AND COLUMN_I IN ('val1', 'val2', 'val3', 'val4') AND COLUMN_E IN ('val10', 'val20', 'val30', 'val40') ;
...

Dans l'ensemble, ça marche pas mal, à part une certaine lenteur au niveau des requêtes. Pour info, les requêtes sont générées à partir d'un ORM (Hibernate).

Nous avons posé des index sur certaines combinaisons pour améliorer le temps d’exécution des requêtes. Mais les requêtes pouvant se faire sur toutes les colonnes indifféremment, nous ne pouvons pas poser des index sur toutes les combinaisons possibles.

Avez vous des idées pour améliorer les temps d’exécutions des requêtes quel que soit la combinaison de colonnes choisies en critères ?
Merci de votre aide.

[SQLpro]

Vous avez visiblement un problème de conception....

1) pourquoi avez-vous nommé vos colonnes COLUMN_A... COLUMN_I ?
2) avez vous du NULL dans certaines lignes pour vos colonnes COLUMN_A... COLUMN_I ?
3) allez-vous avoir d'autres colonnes comme COLUMN_J, COLUMN_K, COLUMN_L, ... un de ces jours ?

Comprenez que plus une table comporte de nombreuses colonnes, plus indexer toutes les combinaisons devient absurde...
Pour information, dans votre cas, pour répondre à toutes les problématiques d'indexation, il vous faudrait 9 864 100 index !

La solution est de faire un VRAI modèle relationnel en portant dans des tables vos colonnes parasites. Le volume de données en serait sans doute réduit, l'indexation simple et les performances radicalement efficaces.

Une croyance malheureuse et imbécile, hélas véhiculées par certains internautes dénués de tout sens critique est de vous faire croire que les jointures c'est mal et c'est pas performant ! C'est bien évidemment tout le contraire, dès que l'on commence à s'interroger sur la volumétrie des données, la concurrence d'accès et bien entendu les performances...
À lire sur le sujet : http://blog.developpez.com/sqlpro/p1...mances_petites

Donc, dans votre cas, modélisez comme suit :

Code :

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

1
2
3
4
5
6
7
CREATE TABLE MA_GROSSE_TABLE 
(
TABLE_ID                           NUMBER(10) NOT NULL PRIMARY KEY,
TABLE_DATE                       DATE
);
CREATE INDEX X_MA_GROSSE_TABLE_DATE_ID 
   ON MA_GROSSE_TABLE (TABLE_DATE, TABLE_ID);

Puis pour chacune de vos "colonnes" :

Code :

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

1
2
3
4
5
6
7
CREATE TABLE MA_GROSSE_TABLE_A 
(
TABLE_ID                           NUMBER(10) NOT NULL PRIMARY KEY,
COLUMN_A                         VARCHAR2(100)
);
CREATE INDEX X_MA_GROSSE_TABLE_A_COLA
   ON MA_GROSSE_TABLE_A (COLUMN_A, TABLE_ID);

A +

[dtateur]

Merci pour votre réponse.

1) N’étant pas au travail au moment de la rédaction de mon post, je n'avais le nom exact des colonnes donc j'ai mis COLUMN_A, COLUMN_B, ... pour l'exemple mais mes colonnes ont des vraies noms .
2) Non je n'ai pas de NULL dans certaines de mes lignes. Toutes mes colonnes sont remplies.
3) Peut-être. Difficile de savoir comment va évoluer cette table.

Les colonnes parasites ont déjà été externalisé dans d'autres tables.
Donc, nous possédons déjà des tables du genre :

Code :

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

1
2
3
4
5
6
7
 
Name                              Type
 ----------------------------- -------- -------------------
TABLE_ID                           NUMBER(10)
COLUMN_J                         VARCHAR2(20)
COLUMN_K                         VARCHAR2(20)
...

Mais, peut-être qu'en transformant chaque colonne de la table principale en table de références, aurons nous de meilleures performances.
Qu'en pensez-vous ?

[mnitu]

Le table_id c’est une clé technique (surogate key). A priori donc votre clé fonctionnelle semble à être la date (colonne table_date). N’est-il pas envisageable d’avoir toujours une dat,e ou mieux dit un intervalle dans vos recherches ?

[dtateur]

Non la date n'est pas une clé fonctionnelle. Les utilisateurs peuvent faire des recherches sur n'importe quelles colonnes sans spécifier de dates.

Mais, on peut imposer la sélection d'une date ou d'un intervalle si cela doit améliorer les performances.
Mais lors de mes tests, ajouter la date n'améliore pas toujours le temps d'execution.

[Waldar]

Quelle est votre édition d'Oracle Database ?
Standard, Entreprise ?

[dtateur]

Oracle Database 11g Enterprise Edition
version 11.2.0.3.0

[Waldar]

C'est une bonne nouvelle. Avez-vous l'option de partitionnement ?

Votre table, comment est-elle alimentée ?
Ligne à ligne par une application ou bien par des lots d'alimentations ?
Est-elle sujette à mise à jour et/ou suppression ou bien est-elle en insertion seule ?

[dtateur]

La table n'est pas partitionnée.
Elle est alimentée via une application ligne par ligne.
Nous faisons de l'insertion et de l'update sur la table (pas de suppression).
Mais lors de l'update, une seule colonne est mise à jour.
Les autres colonnes ne sont jamais modifiées.

[Waldar]

On peut envisager grosso modo deux pistes.

La première c'est de mettre en place un RANGE INTERVAL PARTITIONNING sur votre date, avec un pas au mois.
Si les dates sont spécifiées dans la requête, les partitions sans donnée utile seront éliminées par l'optimiseur.

Ensuite, positionnez des index bitmap.
Vous en mettez un par colonne, ils se combinent parfaitement entre eux.

Par contre, et j'attire vraiment votre attention sur ce point, soyez très vigilants avec ces index : ils ont été créé pour le décisionnel et pas pour le transactionnel : ils supportent mal le chargement ligne à ligne et les mise à jour / suppression.
De même, plus il y a de valeurs distinctes dans votre colonne et moins ils seront efficace => pas d'index bitmap sur la colonne date type une heure de log.
Si vos colonnes ont une énorme sélectivité, ne posez pas ce type d'index dessus.

Ça pourrait apporter un énorme mieux, mais ils pourraient aussi faire du mal à l'application s'ils partent en vrille, ce qu'on veut éviter : il faudra les maintenir souvent.

D'où la seconde piste, vous créez une vue matérialisée sur la première, qui se rafraîchit à une fréquence à définir avec les utilisateurs de l'application.
Vous gardez alors le partitionnement et sur la première table, et sur la VM, par contre vous laissez tomber les index bitmap sur la table. Le refresh se faisant cette fois-ci sur un volume de données plus conséquent, les index bitmap nécessiteront moins de maintenance.

Votre application écrit toujours dans la table, mais affiche les données de la VM.

[pachot]

Bonjour,

Si les tables sont alimentées en même temps qu'elles sont interrogées, ce sera difficile. L'optimisation des interrogations (bitmap index, compression) posera des problèmes (locks + performances) aux mises à jour.

Est-ce que les interrogations doivent voir les données en temps réel ? Sinon, c'est peut-être possible de répliquer (vue matérialisée) les tables opérationnelles vers des tables décisionnelles.

Nous avons posé des index sur certaines combinaisons pour améliorer le temps d’exécution des requête

Au moins les index les plus selectifs. Pas nécéssaire de faire toutes les combinaisons: Oracle peut en combiner plusieurs. Même s'ils ne sont pas bitmap.

Cordialement,
Franck.

[dtateur]

Bonjour,

He oui, nous souhaitons du temps réel sur cette table.
Le partitionnement semble une bonne idée mais la date n'étant pas un critère de recherche obligatoire, c'est pour cette raison que nous ne l'avons pas encore mis en place. Mais, si les performances doivent se dégrader, je pense que nous n'aurons pas le choix.

Malgré un gros volume en nombre de lignes, le nombre de valeurs distinctes dans chaque colonne n'est pas énorme. La colonne possédant le plus de valeurs distinctes en a 14 000. Les autres colonnes sont plutot aux alentours de 1 000 valeurs distinctes.

[pachot]

Bonjour,

Faire des requêtes analytiques avec n'importe quel critère sur des données en temps réel mis à jour en même temps... si ça marche pas mal c'est déjà bien !

Les solutions pour ça existent mais coûtent cher:
- réplication temps réel vers une base dédie au décisionnel (Golden Gate par exemple)
- Exadata pour se passer des indexes (Grâce à SmartScan et StorageIndex)
- [pas encore sorti] l'option In-Memory annoncée par Oracle.

Cordialement,
Franck.

[ora_home]

Puisque les tables sont interrogé au m^me temps que les opérations de l'alimentation, alors les vues matérialisé ne semble pas une bonne solution.

par contre, si vous arrivez a utilisé des tables partitionnées, vous allez avoir un temps de réponse mieux.

[islamov2000]

Pour les tables partitionnées, son organisation n'est pas forcement par date, mais aussi identifiant, par exemple.
Le

Code :

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

select *

peut provoquer une lourdeur.
Le

Code :

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

order by

aussi peut provoquer une lourdeur, si la zone de tri est faible.
Une des solutions, un tablespace dédié à cette table par exemple ou réorganiser la SGA après avoir collecté les statistiques.