Besoin d'aide : optimisation table (index) + requête [Résolu]

[sdisp+]

Bonjour.

J'ai besoin d'un peux d'aide pour optimiser une table que j'ai créé ainsi que la requête que j'exécute dessus.

Tous d’abord, voici m'as table:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE TOTO
(
  ID_TOTO       NUMBER NOT NULL,
  DATE_TOTO     DATE NOT NULL,
  ID_LIB1       NUMBER(3) NOT NULL,
  NOM_ LIB1     VARCHAR2(50 BYTE) NOT NULL,
  NO_ CARTE1     NUMBER(3) NOT NULL,
  NO_ CARTE2     NUMBER(3) NOT NULL,
  NO_ CARTE3      NUMBER(5) NOT NULL,
  ID_LIB2       NUMBER(5),
  NOM_ LIB2     VARCHAR2(50 BYTE),
  ID_ LIB3      NUMBER(3),
  NOM_ LIB3     VARCHAR2(50 BYTE),
  ID_ LIB4      NUMBER(6),
  NOM_ LIB4     VARCHAR2(50 BYTE)
)

Je vous explique 2-3 choses :
- ID_TOTO est l’identifiant de la table (n° de ligne)
- Je crée cette table à partir de plusieurs autres tables :

TABLE LIB1(ID_TOTO, ID_LIB1, NOM_ LIB1)
TABLE LIB2(ID_TOTO, ID_LIB2, NOM_ LIB2)
TABLE LIB3(ID_TOTO, ID_LIB3, NOM_ LIB3)
TABLE LIB4(ID_TOTO, ID_LIB4, NOM_ LIB4)

Je fais ensuite une requête selon les choix que fond les utilisateurs selon date_toto, no_carte1, no_carte2, no_carte3, id_lib1, id_lib2, id_lib3, id_lib4

Exemple de requête :

Code :

SELECT DATE_TOTO, NO_ CARTE1, NO_ CARTE2, NO_ CARTE3,  NOM_ LIB1, NOM_ LIB2, NOM_ LIB3, NOM_ LIB4 FROM TOTO WHERE no_carte1=33 AND no_carte2=10 AND no_carte3=28 AND (id_lib1=22 OR id_lib1=23) AND id_lib3=24 AND id_lib4=32 AND date_toto BETWEEN ‘10/01/2008’ AND ‘20/01/2008’

Dernière précision :
- tous ces champs peuvent être renseigné ou non la requête se construisant selon les choix renseigné
- les champs retournés son toujours les mêmes (DATE_TOTO, NO_ CARTE1, NO_ CARTE2, NO_ CARTE3, NOM_ LIB1, NOM_ LIB2, NOM_ LIB3, NOM_ LIB4)
- il y a un choix multiple sur LIB1, 2 ,3 et 4
- un choix unique sur NO_ CARTE1, 2 et 3
- la date si elle est renseigné et toujours choisit sur un intervalle ( between ‘date_min’ and ‘date_max’)
- chose qui a sont importance, la table toto comporte environ 10 millions d’enregistrement

M’as 1er question est donc comment puis-je améliorer m’as table :
- je vois déjà la primary key : CONSTRAINT TABPASSAGES PRIMARY KEY (ID_PASSAGES) USING INDEX TABLESPACE USER_INDEXES
- et au niveau des index, je n’y connais rien je dirais qu’il faut en mettre sur LIB1, 2 ,3 et 4, NO_ CARTE1, 2 et et la date, mais comment et dans quelles ordre ?

M’as 2ème question est comment améliorer m’as requête :
- y a-t-il un ordre dans la disposition des champs dans le ‘where’ ou cela ne change t’il rien ?

Je me rends compte que c’est un peu confus, j’espère ne pas vous avoir perdu en route et que vous pourrais m’aider.

Bonne journée.

sdisp

[orafrance]

Envoyé par sdisp+

- et au niveau des index, je n’y connais rien je dirais qu’il faut en mettre sur LIB1, 2 ,3 et 4, NO_ CARTE1, 2 et et la date, mais comment et dans quelles ordre ?

l'ordre dépend de l'utilisation des champs dans les clauses WHERE. Par exemple, un index sur (id2, id1, id3) ne pourra pas être utilisé si id2 n'est pas renseigné. Dans ce cas ça peut valloir le coup de faire : (id1, id3) et (id2,id3) par exemple.

Néanmoins; tu imagines bien que c'est très difficile de t'aider a priori. L'idéal c'est de scruter les requêtes montées en SGA et contenant cette table pour voir les différents clauses WHERE utilisées et agir en conséquence.

Envoyé par sdisp+

y a-t-il un ordre dans la disposition des champs dans le ‘where’ ou cela ne change t’il rien ?

Non, sauf en mode RULE qui est obsolète depuis un moment mais utilisé dans le cas de contraintes d'un éditeur d'appli par exemple.

[sdisp+]

Merci pour cette réponse.

Et si je fais des indexs sur chaque champ indépendamment (id1) (id2) (id3), ça ne sert à rien?
Je pense que les utilisateurs utilisent vraiment toutes les combinaisons, dois je essayer de mettre en index toutes les combinaisons?
Est-ce que (id2, id1, id3) est égale à (id1, id2, id3)?

PS: la date est un champ obligatoire, je peux déjà l'indexer dans tous les cas.

Merci.

[wondersonic]

Par exemple, un index sur (id2, id1, id3) ne pourra pas être utilisé si id2 n'est pas renseigné.

Ceci n'est plus vrai à partir de la 9i avec le chemin d'accès INDEX SKIP SCAN.

Sinon, en quelle version d'Oracle es-tu ?
Quelle est l'activité sur ta table TOTO ? Beacoup plus de consulations que de modifications ? Si c'est le cas, considère les index BITMAP seulement si tu n'as que très très très peu de modifications/insertions/suppressions de données (en terme de fréquence) et si tu as peu de valeurs distinctes dans tes colonnes.

Enfin, si tu pouvais nous détailler ton modèle relationel. Quel est le but de la table TOTO par rapport aux tables existantes LIB1, LIB2, LIB3, LIB4. A quoi servent les colonnes NO_CARTE% ? Pourquoi n'y a-t-il pas de colonne NO_CARTE4 ? ;o)

Plus on en saura, plus on pourra t'aider.

Enfin une chose est sûre : indexer aveuglément toutes les colonnes d'une table n'est pas une solution en soit (la maintenance par Oracle de tes index a un coût). Un index ne sera utile pour une requête que si l'index retourne moins de 5% des données de ta table (ici moins de 500 000 lignes).

[orafrance]

Envoyé par sdisp+

Merci pour cette réponse.

Et si je fais des indexs sur chaque champ indépendamment (id1) (id2) (id3), ça ne sert à rien?

d'abord c'est plus couteux, un index avec les 3 colonnes est plus rapide que 3 indexes (entendu que les 3 sont utilisés ) et surtout ça accentue le risque d'erreur de l'optimiseur.

Envoyé par sdisp+

Je pense que les utilisateurs utilisent vraiment toutes les combinaisons, dois je essayer de mettre en index toutes les combinaisons?

Dans ce cas, tu seras peut-être obliger de faire plusieurs indexes alors

Envoyé par sdisp+

Est-ce que (id2, id1, id3) est égale à (id1, id2, id3)?

oui si au moins id2 et id1 sont utilisés dans la clause WHERE, au moins la 1° colonne de l'index doit être dans le WHERE pour pouvoir utiliser l'index.

Envoyé par sdisp+

PS: la date est un champ obligatoire, je peux déjà l'indexer dans tous les cas.

tu veux dire qu'elle est obligatoirement dans le WHERE ? Tu peux aussi penser à partitionner la table sur cette date si tu as plusieurs millions de lignes

[orafrance]

Envoyé par wondersonic

Ceci n'est plus vrai à partir de la 9i avec le chemin d'accès INDEX SKIP SCAN.

ha oui, c'est exact : http://download.oracle.com/docs/cd/B...htm#PFGRF10105

[sdisp+]

Cette appli sert à faire des stat sur des passages à des bornes de controle.
J’ai donc une table avec les passages (date, no_carte1,2,3 , id_lib1,2,3,4)
Le lien avec les tables lib me permet de récupérer le libellé associé aux id_lib (environ 30 libellés par tables lib).

Sinon, en quelle version d'Oracle es-tu ?

J'utilise Oracle 9.2

A quoi servent les colonnes NO_CARTE

la concaténation des 3 n° de carte (no_carte1-no_carte2-no_carte3) identifie une personne unique.

Quelle est l'activité sur ta table TOTO ? Beaucoup plus de consultations que de modifications ? Si c'est le cas, considère les index BITMAP seulement si tu n'as que très très peu de modifications/insertions/suppressions de données (en terme de fréquence)

Pendant la nuits un scripte récupère les passages en les reliant aux tables TABLE LIB1, 2, 3,4 (en passant par une autre table intermédiaire reliée aux passages via no_carte1-no_carte2-no_carte3 puis relier aux tables lib toujours via no_carte1-no_carte2-no_carte3).
L'insertion ne se déroule donc que pendant la nuit (500000 lignes/jours), ensuite les utilisateurs se servent pendant la journée d'un petit formulaires qui leurs permettent de choisir les différents critères qui constitue la requêtes select dont j'ai donné un exemple plus haut

tu as peu de valeurs distinctes dans tes colonnes.

Donc 30 par table lib

Quel est le but de la table TOTO

j'ai créé la table toto car j'avais rendu disponible l'appli sans avoir regroupé toutes les infos dans une seul table et les temps de réponse était énorme, j'avais également des problème de mémoire.
La table toto me sert donc à ne pas faire d'inner join dans la requete

tu veux dire qu'elle est obligatoirement dans le WHERE ? Tu peux aussi penser à partitionner la table sur cette date si tu as plusieurs millions de lignes

Oui la date est obligatoirement dans le where.
Qu'entend-tu par partitionner la table?

J'espère avoir répondu à la plus pars des question.

Merci encore à vous 2.

[sdisp+]

Après une petite réflexion je dirais que :
- no_carte1 no_carte2 no_carte3 sont les champs qui doivent être le moins souvent renseigné par les utilisateur (à la limite no_carte2 plus souvent car ca identifie le point de vente de la carte)
- lib1 ne dois pas être également utilisé très souvent
- les autres lib doivent l'être plus souvent
- date renseigné en permanece car obligatoire dans mon formulaire (mais la période est souvent grande (4 mois)

Autres choses que je n'ai pas dis, on peux faire un GROUP BY no_carte1, no_carte2, no_carte3 ainsi que GROUP BY DATE

Je viens de contacté une utilisatrice qui m'as dis souvent faire des requetes en ne renseignant que la date, pour répondre à ce cas, je met un idex uniquement sur date?

Merci.

[wondersonic]

Alors le partitionnement par date semble une bonne idée si c'est un critère de recherche obligatoire (cherche partitionnement sur ce forum, tu trouveras plein d'informations).

Un index sur la date peut sembler un bon début mais (corries moi si je me trompe), pour une date donnée, tu as 500000 lignes dans ta table ?

Pour les 2 group by possibles, deux vues matérialisées pourraient également servir.

En fait, j'ai tout de même un problème, je pense que ta table TOTO est toute dénormalisée (par exemple, quel est l'intérêt de stocker id_lib1 et nom_lib1 ?).

Et question idiote peut-être mais combien de lignes les utilisateurs récupèrent-ils en moyenne et au maximum par requête ?

[sdisp+]

Bonjour.

Un index sur la date peut sembler un bon début mais (corries moi si je me trompe), pour une date donnée, tu as 500000 lignes dans ta table ?

Je viens de faire une moyenne sur un mois et ca me donne en fait 200000/jours.

En fait, j'ai tout de même un problème, je pense que ta table TOTO est toute dénormalisée (par exemple, quel est l'intérêt de stocker id_lib1 et nom_lib1 ?).

Alors, le formulaire utilisateur (en php), est constitué de champs de type select, dans lequel, l'utilisateur voit les libellés et qui me retourne les identifiants des champs qu'il a sélectionnés.
Je fais ensuite une requete SELECT avec WHERE id_lib1=xx and id_lib2=yyy, arrete moi si je me trompe mais je pense que faire le where sur des id est plus rapide que de le faire sur des champ text? (le SELECT retournant les libéllé associé aux id_lib)

Et question idiote peut-être mais combien de lignes les utilisateurs récupèrent-ils en moyenne et au maximum par requête ?

Je viens de recontacter une utilisatrice, et elle me dis qu'elle utilise le GROUP BY no_carte1-no_carte2-no_carte3, elle récupère donc environ 20000/jours.
Elle exploite les données sous excel, elle se débrouille donc pour ne pas avoir + de 65000 lignes max.

Alors le partitionnement par date semble une bonne idée si c'est un critère de recherche obligatoire (cherche partitionnement sur ce forum, tu trouveras plein d'informations).

Pour les 2 group by possibles, deux vues matérialisées pourraient également servir.

Ok je me renseigne sur tout ceci.

Merci pour ton aide.

[orafrance]

pour schématiser, le partitionnement consiste à découper la table en plusieurs tables selon un critère : liste de valeur d'une colonne, tranche (inférieur au 01/01/2007; inférieur à 01/02/2007, etc...), par hash (des partitions de tailles égales selon certaines conditions) ou l'association de plusieurs de ces critères.

[sdisp+]

OK merci.
Dans mon cas, tu verrais bien un partitionnement par tranche de date?
Y a t'il une commande pour lui dire de découper par mois ou jours automatiquement? Ou dois-je spécifier toutes les bornes comme tu me l'as indiqué?

Merci encore.

[orafrance]

il n'y a malheureusement rien d'automatique. Après à toi de voir le mieux entre mensuel, trimestrielle ou autre

[DjinnS]

Envoyé par orafrance

d'abord c'est plus couteux, un index avec les 3 colonnes est plus rapide que 3 indexes (entendu que les 3 sont utilisés ) et surtout ça accentue le risque d'erreur de l'optimiseur.

Je rebondis sur ce que tu as dit car je me posais exactement cette question.

Il vaut mieux avoir un seul INDEX sur trois colonnes, si dans la clause where j'ai toujours les trois colonnes d'utilisées ?

Si une de ces colonnes est une clé de partitionnement, est ce que ca vaut encore le coup de faire un index qui regroupes les trois colonnes ? (sachant qu'il y a déjà un index sur les deux autres colonnes).

[orafrance]

si la table est partitionné c'est pas forcément utile d'indexer la clé de partitionnement mais là ça dépend du contexte. Et oui, vaut mieux un index de 3 colonnes que 3 indexes de une

et aussi, si tu sélectionnes une colonne par exemple et que tu fais une clause WHERE sur 3 autres, peut valoir le coup d'ajouter la colonne sélectionner, çà permet d'économiser un accès sur la table

[DjinnS]

Merci pour les infos, je vais maintenant regarder du coté du code sql.

[sdisp+]

Merci à tous pour ces infos, le fonctionnement d'oracle est mainteant bien plus claire pour moi.

Bonne journée.

[kervoaz]

Bonsoir,

je ferais également une action, probablement sur le code php, pour introduire des bind variables. Ça sera d'autant plus simple de logguer les différentes requêtes pour positionner les index et connaître le nombre d'exécutions.

[wondersonic]

Envoyé par sdisp+

Bonjour.
Alors, le formulaire utilisateur (en php), est constitué de champs de type select, dans lequel, l'utilisateur voit les libellés et qui me retourne les identifiants des champs qu'il a sélectionnés.
Je fais ensuite une requete SELECT avec WHERE id_lib1=xx and id_lib2=yyy, arrete moi si je me trompe mais je pense que faire le where sur des id est plus rapide que de le faire sur des champ text? (le SELECT retournant les libéllé associé aux id_lib)

Je t'arrête. Oui faire un select avec where id_lib1 est plus rapide que where
nom_lib1. Par contre, quand tu scannes ta tables, tu lis automatiquement les nom_lib1 depuis le disque vers le cache Oracle. L'idée : les jointures.

Tu retires nom_lib1 de ta table TOTO, tu indexes ta foreign key id_lib1 dans TOTO et id_lob1 dans la table LIB1 (si c'est une primary key, la colonne est automatiquement indexée par Oracle). Ensuite, tu fais la jointure entre TOTO et LIB1 et tu récupères LIB1.nom_lib1.

Gains :
- espace disque : la table TOTO est moins grosse
- données plus condensées : un block de la table TOTO contient plus de données "utiles" (pas de redondance) => moins de mémoire cache nécessaire pour la table TOTO
- si tu places les petites tables LIB1 LIB2 etc... dans le keep pool, les jointures seront très rapides
- pas de problème si tu mets à jour nom_lib1 (tu ne mets à jour que LIB1 et pas TOTO = normalisation) => facilité d'administration des libellés

[sdisp+]

Merci à vous deux.

Tu retires nom_lib1 de ta table TOTO, tu indexes ta foreign key id_lib1 dans TOTO et id_lob1 dans la table LIB1 (si c'est une primary key, la colonne est automatiquement indexée par Oracle). Ensuite, tu fais la jointure entre TOTO et LIB1 et tu récupères LIB1.nom_lib1.

Ok je comprend tout à fait. Je vais le mettre en place.

Merci à tous pour votre aide.