Discussion :

[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 :

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
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 :

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

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]

- 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.

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]

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.

[orafrance]

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.

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

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.

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

[DjinnS]

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.