Optimisation en utilisant les index

Bonjour,
J'utilise les trois tables suivantes dans ma base de données Oracle 10G.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

create table USER ( USER_ID NUMBER(10) not null, FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50), primary key (USER_ID) );       create table TASK ( TASK_ID NUMBER(10) not null, TASK_NAME VARCHAR(50), primary key (TASK_ID) );         create table TASK_USER ( USER_ID NUMBER(10), TASK_ID NUMBER(10), EXPIRY_DATE DATE );

---

Un utilisateur peut effectuer plusieurs taches. Une tache peut être réalisées par plusieurs utilisateurs. Je souhaite optimiser la requête qui renvoie les taches d'un utilisateur donné.

Il y a actuellement un 1 million de lignes dans la table TASK, 100 mille ligne dans la table USER, et 1,2 million de ligne dans la table TASK_USER.
Lorsqu'un utilisateur n'est plus affecté à une tache donnée, au lieu de supprimer physiquement la ligne, je remplis une date de suppression, d'où la colonne EXPIRY_DATE.




Ma requête est la suivante

Code:

---

select TASK_ID, TASK_NAME from TASK_USER, TASK where TASK_USER.USER_ID = ? and TASK_USER.EXPIRY_DATE is null and TASK_USER.TASK_ID = TASK.TASK_ID;

---

Quels index dois-je créer pour optimiser ma requête ?

Merci pour votre aide

Compte tenu du nombre d'enregistrements de vos différentes tables , je ne croit pas que vous aillez besoin de rajouter des index , vos clefs primaires devrait etre sufisantes .

Les statistiques Oracle sont elles effectives sur vos tables ?

Quelle sont vos temps de réponses sur votre requete des taches d'un utilisateurs ?

Au besoin , vous pouvez essayer de voir ce que donne ce qui suit

Code:

---

1 2 3 4 5 6 7 8

select /*+ RULE */ T.TASK_ID, T.TASK_NAME from TASK T , TASK_USER U where U.USER_ID = &1 And U.task_id > 0 And U.EXPIRY_DATE is null And T.TASK_ID = U.TASK_ID;

---

Tu fais ta recherche sur 1 user particulier ou tu veux la liste de toutes les tâches pour tous les users ?

Désolé, il y avait une erreur dans mon premier message que je viens de modifier. Il n'y a pas de clef primaire sur la table TASK_USER
La raison est que quand je ne connais pas l'utilisateur précisément, j'ai un utilisateur par défaut à qui j'affecte la tache. je peux donc avoir deux fois le même couple (USER_ID, TASK_ID) dans la table TASK_USER.

Sur la table TASK_USER, j'ai créé un index sur USER_ID et un deuxième index sur TASK_ID.

Avec ça, j'ai un temps de réponse de quelques secondes par requête pour un utilisateur donné (pour répondre à la question de spdev666). Etant donné que j'utilise souvent cette requête dans mon logiciel, je dois encore optimiser la requête.

Merci pour votre aide.

Citation:

---

Envoyé par jelefevre2003

La raison est que quand je ne connais pas l'utilisateur précisément, j'ai un utilisateur par défaut à qui j'affecte la tache. je peux donc avoir deux fois le même couple (USER_ID, TASK_ID) dans la table TASK_USER.

---

Est-ce que cette information de tâche sans user est exploitable ?
Si elle ne présente pas d'intérêt ne l'enregistrez pas et gardez votre PK.

La cohérence du modèle est la plus importante.

Que se passe-t'il si le même utilisateur effectue deux fois la même tâche ?

En fait, votre requête cherche des informations dans la table TASK: TASK_ID, TASK_NAME mais les critères de sélection concernent la table TASK_USER: USER_ID et EXPIRY_DATE. Donc vous pouvez reformuler votre requête comme

Code:

---

1 2 3 4 5 6 7 8

Select t.task_id, t.task_name From TASK t Where task_id In (Select task_id from TASK_USER where user_id = ? And expiry_date Is Null )

---

Vu la volumétrie des tables et en supposant des distributions uniformes, le critère user_id devrait être assez sélectif. Pour cette requête votre meilleur index pour la table TASK_USER est un index composite sur les zones (USER_ID, TASK_ID) et le plan d'exécution devrait l'utiliser avec un RANGE_SCAN sans attaquer la table TASK_USER. Pour la table TASK vous avez déjà votre index du à la contraint clé primaire.
Si les données sont reparti d'une manière uniforme la requête devrait s'exécuter assez rapidement pourvu que les statistiques sont à jour. Par contre ce type d'index est moins intéressant si vous cherchez les utilisateurs affectés à une tache précise. Donc, vous devez analyser l'ensemble des requêtes à exécuter pour décider de la meilleur façon d'indexer.
Si votre table TASK_USER est plutôt une table d'historique: la plupart des enregistrements contient une date pour la zone expiry_date et il y a peu d'enregistrements avec une date nulle vous pouvez envisager peut être «*d'indexer les nuls*» pour cette date.
Votre petit astuce concernant l'utilisateur bidon peut induire une non uniformité dans la distribution des données de la table TASK_USER ce qui pourrait impliquer la nécessité de collecter des histogrammes pour que l'optimiseur peut y prendre connaissance (je rejoint Waldar pour sa recommandation).
En fin, il semble que vous devez en fait optimiser un traitement et non pas seulement une requête. Si une requête prendre 0,01 secondes pour s'exécuter mais vous l'exécutez 10000 fois dans votre traitement le temps total de votre traitement restera quand même de l'ordre de 100 secondes.

Je vais essayer de gérer différemment la notion d'utilisateur par défaut pour remettre la clef primaire (USER_ID, TASK_ID). J'ai fait qulques tests et c'est effectivement mon meilleur index sur cette table.
Merci pour vos réponses.