Ceci est une ancienne révision du document !
L'objectif de ce TME est de comprendre l'optimisation des requêtes qui contiennent des jointures.
Le TME dure 2 séances.
Ne pas oublier de consulter les questions fréquentes en bas de cette page
Lire l'énoncé de l'exercice dans le poly de TD: Exercice 3: Club de joueurs
commande | description |
---|---|
cd mon_répertoire | aller dans votre répertoire de travail |
tar zxvf /Infos/bd/public/tmeJointure.tgz | installer l'archive dans votre répertoire principal |
cd tmeJointure | aller dans le répertoire du TME |
emacs tmeJointure.sql & | éditer le fichier à compléter pendant le TME |
Alt-x my/sql-oracle ou Atl-x sql-oracle | se connecter à Oracle. Voir ConnexionOracle |
aller sur le paragraphe contenant @base3 et faire Ctrl-C Ctrl-C | créer vos tables J, C, F, les index et les statistiques nécessaires à l'optimisation basée sur le coût |
Les tables d'un club de sport sont :
qu'on abrège dans ce TME en :
Les index existants s'appellent: I_J_CNUM pour J(cnum), I_J_SALAIRE pour J(salaire), I_C_CNUM pour C(cnum), I_C_DVISION pour C(division) et I_F_CNUM pour F(cnum)
Il y a aussi la table BigJoueur(licence, cnum, salaire, sport, profil) dont l'attribut profil contient 4000 caratères.
Pour voir les plans proposés par le SGBD et leur coût :
EXPLAIN plan FOR SELECT ...
puis terminer chaque requête par
@p4
Combien de n-uplets ont chacune des relations J, C, F et BigJoueur?
EXPLAIN plan FOR SELECT * FROM J; @p4
Quel est le coût d'accès à chaque table ? Rappel: le coût d'accès à un plan se lit dans la colonne Cost, pour la racine de l'arbre représentant le plan (première ligne).
Combien de pages ont chacune des relations ?
SELECT TABLE_NAME, num_rows AS cardinalite, blocks AS nb_pages FROM user_tables; SELECT TABLE_NAME, num_rows AS cardinalite, blocks AS nb_pages FROM all_tables WHERE TABLE_NAME='BIGJOUEUR';
On veut calculer coût d'une lecture séquentielle avec la formule Cout(R) = a * page(R) + b
Rmq. : dans le cours on suppose a=1 et b=0.
Déduire les valeurs des constantes a et b utilisées par Oracle à partir des coûts affichés pour J, C. Vérifier que la formule est vraie pour F et BigJoueur.
On considère la requête R1.
EXPLAIN plan FOR SELECT J.licence, C.nom FROM J, C WHERE J.cnum = C.cnum AND J.salaire > 1000; @p4
a) Traduisez la requête en français.
b) Affichez le plan P1 de cette requête. Quel est le nom de l'opérateur de jointure ? Dessinez l'arbre de P1 en suivant la méthode présentée en TD : numérotez chaque opération avec son Id de 0 à 3.
c) Quelle table est lue en premier, J ou C, pourquoi ?
d) Quel est le coût de P1 ? Expliquer comment le coût est calculé en fonction des coûts d'accès à J et C.
On considère la requête R2.
EXPLAIN plan FOR SELECT J.licence, C.nom FROM J, C WHERE J.cnum = C.cnum AND J.salaire < 11000; @p4
a) Afficher et dessiner le plan P2 de cette requête.
b) Quelle table est lue en premier J ou C, pourquoi ?
c) Quel est le coût de P2 ?
d) On remplace la table Joueur par une table plus grande 'BigJoueur'. On étudie la jointure par hachage entre les tables C et BigJoueur.
Observer que la jointure dans l'ordre C, BigJoueur
a un coût plus petit que celle dans l'ordre BigJoueur, C
SELECT /*+ ordered */ * FROM C, BigJoueur j WHERE j.cnum = c.cnum;
SELECT /*+ ordered */ * FROM BigJoueur j, C WHERE j.cnum = c.cnum;
Sachant que le coût total de la jointure est la somme du coût de lecture des tables plus le coût de constuire la HashMap (cf. colonne TmpSpc). En déduire le coût de construction de la HashMap.
Enlever maintenant la directive ordered
et observer que le SGBD choisit de lire C avant BigJoueur car cela est moins coûteux.
On considère la requête R3.
EXPLAIN plan FOR SELECT J.licence, C.division FROM C, J WHERE J.cnum = C.cnum AND C.nom IN ('PSG', 'Barca'); @p4
a) Affichez le plan P3 de cette requête. Quel est le nom de l'opérateur de jointure ?
b) Détailler les étapes de l'évaluation
c) Quel est le coût du plan exprimé en fonction du coût pour lire une table, un index et pour lire un nuplet seul.
On considère la requête R4.
EXPLAIN plan FOR SELECT J.licence, C.division FROM C, J WHERE J.cnum = C.cnum AND J.salaire BETWEEN 10000 AND 10001; @p4
Affichez le plan P4 de cette requête, expliquer ses étapes et son coût.
Objectif : comprendre la notion de choix entre 2 plans equivalents, basé sur le coût.
Etant donné une requête on voudrait construire un autre plan équivalent à celui que propose l'optimiseur, mais qui utilise un algorithme de jointure différent.
Pour cela on ajoute une directive pour forcer l'usage d'un algorithme de jointure.
USE_NL
indique que la jointure doit être traitée par boucles imbriquées.USE_HASH
indique que la jointure doit être traitée par hachage.Reprendre les requêtes R1 à R4 de l'exercice précédent en ajoutant une directive. Expliquer le plan obtenu. Comparer les plans obtenus avec/sans directive pour une même requête.
On veut évaluer R1 avec une jointure par boucles imbriquées
SELECT /*+ USE_NL(J,C) */ J.licence, C.nom FROM J, C WHERE J.cnum = C.cnum AND J.salaire > 10;
Expliquer le plan. Vérifier que son coût est supérieur à celui de P1.
On veut évaluer R2 avec une jointure par boucles imbriquées
SELECT /*+ USE_NL(J,C) */ J.licence, C.nom FROM J, C WHERE J.cnum = C.cnum AND J.salaire < 11000;
Expliquer le plan. Vérifier que son coût est supérieur à celui de P2.
On veut évaluer R3 avec une jointure par hachage
EXPLAIN plan FOR SELECT /*+ USE_HASH(J,C) */ J.licence, C.division FROM C, J WHERE J.cnum = C.cnum AND C.nom IN ('PSG', 'Barca'); @p4
Expliquer le plan. Vérifier que son coût est supérieur à celui de P3 (de l'exercice 1).
On veut évaluer R4 avec une jointure par hachage
EXPLAIN plan FOR SELECT /*+ USE_HASH(J,C) */ J.licence, C.division FROM C, J WHERE J.cnum = C.cnum AND J.salaire BETWEEN 10000 AND 10001; @p4
Expliquer le plan. Vérifier que son coût est supérieur à celui de P4.
Soit la requête R5 :
EXPLAIN plan FOR SELECT c.nom, f.budget FROM J, C, F WHERE J.cnum = C.cnum AND C.cnum = F.cnum AND J.cnum = F.cnum AND c.division=1 AND J.salaire > 59000 AND j.sport = 'sport1'; @p4
On sait (cf. cours) qu'il y a 6 ordres de jointure possibles pour évaluer cette requête.
La directive ORDERED
permet de fixer l'ordre des jointures. Attention le mot ORDERED
est ajouté dans le SELECT mais l'ordre est fixé dans le FROM.
Par exemple, l'ordre C,F,J est fixé par FROM C, F, J
de cette façon :
EXPLAIN plan FOR SELECT /*+ ORDERED */ C.nom, F.budget FROM C, F, J WHERE J.cnum = C.cnum AND C.cnum = F.cnum AND J.cnum = F.cnum AND C.division=1 AND J.salaire > 59000 AND J.sport = 'sport1'; @p4
a) Avec la directive ORDERED
et en changeant le FROM, évaluer les 6 ordres.
Expliquer chaque plan et compléter le tableau
ordre | 1ère jointure | 2eme jointure | coût |
---|---|---|---|
C, F, J | HASH | HASH | 82 |
C, J, F | |||
F, C, J | |||
F, J, C | |||
J, C, F | |||
J, F, C |
b) D'après les résultats du tableau, quel(s) ordre(s) a un coût minimal. Quels index sont utilisés ? Vérifier que c'est bien l'ordre choisi par l'optimiseur sans la directive ORDERED.
c) Proposer un plan pour R5 qui utilise l'index sur le salaire avec la directive index(J I_J_salaire)
. Quel est l'ordre choisi par l'optimiseur ?
SELECT /*+ index(J I_J_salaire) */ c.nom, f.budget FROM J, C, F WHERE J.cnum = C.cnum AND C.cnum = F.cnum AND J.cnum = F.cnum AND c.division=1 AND J.salaire > 59000 AND j.sport = 'sport1';
Dessiner le plan obtenu. Expliquer son coût.
d) Proposer un plan pour R5 qui utilise l'index sur l'attribut division directive index(C I_C_division)
. Quel est l'ordre choisi par l'optimiseur ?
EXPLAIN plan FOR SELECT /*+ index(C I_C_division) */ C.nom, F.budget FROM J, C, F WHERE J.cnum = C.cnum AND C.cnum = F.cnum AND J.cnum = F.cnum AND C.division=1 AND J.salaire > 59000 AND J.sport = 'sport1'; @p4
Dessiner le plan obtenu. Expliquer son coût.
Le plan
| 1 | NESTED LOOPS |* 2 | TABLE ACCESS FULL | J | |* 3 | TABLE ACCESS BY INDEX ROWID | C | |* 4 | INDEX UNIQUE SCAN | I_C_CNUM |
est indentique au plan
| 1 | NESTED LOOPS | 2 | NESTED LOOPS |* 3 | TABLE ACCESS FULL | J | |* 4 | INDEX UNIQUE SCAN | I_C_CNUM | | 5 | TABLE ACCESS BY INDEX ROWID | C |
Dans les deux plans, la jointure est la même. Lire Joueur pour itérer: pour chaque n-uplet de Joueur, accéder à l'index sur (C.cnum) pour connaitre l'identifiant du nuplet du Club. Puis accéder à la table Club pour lire le n-uplet correspondant. (pour plus d'explications voir Lien
Documentation sur l'optimiseur d'Oracle
Aller vers BDR