En 2018 aller vers le sujet suivant: TME Jointure

L'objectif de ce TME est de comprendre l'optimisation des requêtes qui contiennent des jointures.

• Ordre des jointures,
• Coût d'une requête de jointure,
• Forme des arbres de jointure (linéaire à gauche et autre forme),
• Avantage/inconvénient d'utiliser un index sur l'attribut de jointure et/ou sur d'autres attributs.

Le TME dure 2 séances. Faire l'ex1 pendant la première séance et l'ex2 pendant la deuxième. L'ex3 est facultatif.

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

Les tables d'un club de sport sont :

• Joueur (licence: integer, cnum : integer, salaire: integer, sport: char(20))
• Club (cnum: integer, nom: char(20), division: integer, ville : char(10))
• Finance (cnum: integer, budget: real, dépense: real, recette: real)

qu'on abrège dans ce TME en :

• J (licence, cnum, salaire, sport)
• C (cnum, nom, division, ville)
• F (cnum, budget, depense, recette)

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 :

   SET autotrace trace EXPLAIN

Combien de n-uplets ont chacune des relations J, C, F et BigJoueur?

SET autotrace trace EXPLAIN
SELECT * FROM J;

Quel est le coût d'accès à chaque table ?

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

On considère la requête R1.

   SELECT J.licence, C.nom
   FROM J, C
   WHERE J.cnum = C.cnum
   AND J.salaire > 10;

Ecrire en français ce que retourne la requête R1

Afficher le plan de la requête R1 et constater qu'il s'agit d'une jointure par hachage. Dessiner l'arbre du plan d'exécution proposé par le SGBD. Numéroter les opérateurs de 0 à 3 (valeur du champ Id).

La jointure par hachage entre deux tables s'effectue en deux étapes :

• Etape 1) lire entièrement la table de gauche (Table Access Full de C), la hacher sur la clé de jointure (ici c'est l'attribut cnum) pour créer en mémoire une HashMap<clé, {nuplet}>
• Etape 2) lire la relation de droite (Table Access Full de J) et pour chaque nuplet, effectuer la jointure (HASH_JOIN) en consultant la HashMap construite à l'étape 1.

a) Quel est le coût du plan ? Est-il égal à la somme des coûts du parcours séquentiel de J et C ?

Observer que le coût est le même lorsqu'on inverse l'ordre de lecture des relations : lire J avant C ou lire C avant J. Remarque, l'ordre est fixé par la clause FROM quand on utilise la directive ordered.

   SELECT /*+ ordered */ J.licence, C.nom
   FROM J, C 
   WHERE J.cnum = C.cnum
   AND J.salaire > 10;

   SELECT /*+ ordered */ J.licence, C.nom
   FROM C, J 
   WHERE J.cnum = C.cnum
   AND J.salaire > 10;

b) 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;

Le coût total de la jointure est la somme du coûts 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.

c) Observer que lorsque le prédicat de sélection sur le salaire n'est pas très sélectif, le plan est toujours le même. Répondre en remplaçant sal>10 par sal>100, …, sal>1000.

d) On étudie maintenant le cas où le prédicat de sélection est très sélectif. Expliquer le plan de la requête suivante. Détailler comment chaque valeur de la colonne Cost est calculée.

SELECT J.licence, C.nom
FROM C, j
WHERE J.cnum = C.cnum
AND salaire < 10050;

| Id  | Operation		     | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		   |	50 |  1300 |	60   (2)| 00:00:01 |
|*  1 |  HASH JOIN		     |		   |	50 |  1300 |	60   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| J	   |	50 |   700 |	52   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN	     | I_J_SALAIRE |	50 |	   |	 2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL	     | C	   |  5000 | 60000 |	 7   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information:
   1 - access("J"."CNUM"="C"."CNUM")
   3 - access("SALAIRE"<10050)

a) Expliquer le plan de la requête R2 suivante. Détailler comment chaque valeur de la colonne Cost est calculée.

SELECT J.licence, C.nom
FROM C, j
WHERE J.cnum = C.cnum
AND salaire < 10006;

| Id  | Operation		      | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	    |	  6 |	156 |	 14   (0)| 00:00:01 |
|   1 |  NESTED LOOPS		      | 	    |	    |	    |		 |	    |
|   2 |   NESTED LOOPS		      | 	    |	  6 |	156 |	 14   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| J	    |	  6 |	 84 |	  8   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN	      | I_J_SALAIRE |	  6 |	    |	  2   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN	      | I_C_CNUM    |	  1 |	    |	  0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | C	    |	  1 |	 12 |	  1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information :
   4 - access("SALAIRE"<10006)
   5 - access("J"."CNUM"="C"."CNUM")

b) On veut utiliser l'index sur l'attribut de jointure cnum pour évaluer R1. Pour cela, on utilise une jointure par boucles imbriquées. La directive USE_NL indique que la jointure doit être traitée 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;

Dessiner l'arbre obtenu. Quelle table est parcourue séquentiellement une seule fois ? L'index choisi sur cnum est-il celui de la table C ou J ?

Quel est le coût de ce plan ? Expliquer comment il est obtenu à partir des feuilles. Pourquoi est-il très supérieur au coût de la jointure par hachage pour laquelle on lit J et C une seule fois?

Inversement, on évalue R1 en utilisant l'index sur cnum de la table J en fixant l'ordre (on impose d'itérer sur C avant J).

   SELECT /*+ USE_NL(J,C) ordered*/ J.licence, C.nom
   FROM C,J
   WHERE J.cnum = C.cnum
   AND J.salaire > 10;

Dessiner l'arbre obtenu. Expliquer pourquoi le coût est plus grand. Ce plan fait combien d'accès à l'index ?

c) Jointure et sélection avec/sans index

Comparer les plans obtenus pour R2 avec ou sans index

   SELECT /*+ no_index(J I_J_salaire) */ J.licence, C.nom
   FROM J, C
   WHERE J.cnum = C.cnum
   AND J.salaire <10006;

Soit la requête R3 :

   SELECT /*+ use_nl(J,C,F) */ c.nom, f.budget 
   FROM J, C, F
   WHERE J.cnum = C.cnum AND C.cnum = F.cnum
   AND c.division=1 AND J.salaire > 59000  
   AND j.sport = 'sport1';

La directive USE_NL permet d'imposer des jointures par boucles imbriquées afin d'utiliser les index sur l'attribut de jointure cnum de C et J et F.

On sait (cf. cours) qu'il y a 6 ordres de jointure possibles pour évaluer cette requête.

a) Compléter le tableau des ordres possibles :

D'après les résultats du tableau, dessiner l'arbre correspondant à l'ordre de moindre coût. Quels index sont utilisés ?

Pourquoi l'ordre J, F, C n'utilise pas l'index sur F.cnum? Observer que le SGBD propose de faire un produit cartésien entre J et F au lieu d'une jointure. Remplacer le prédicat de jointure J.cnum = C.cnum par le prédicat équivalent J.cnum = F.cnum, déduit par transitivité, et observer que cela permet d'utiliser l'index sur F.cnum pour la jointure de J avec F. Que peut-on conclure concernant la capacité du SGBD à explorer tous les ordres de jointure équivalents ?

b) Modifier R3 en une requête R4 de jointure et de sélection avec index : modifier seulement la valeur du salaire (59000) pour que le plan obtenu choisisse l'index sur le salaire. Quel serait le coût de R4 sans utiliser l'index sur le salaire ?

c) On force les index sur les attributs “J.salaire” et “C.division” pour évaluer R3

   SELECT /*+ use_nl(J,C,F) index(C I_C_division) index(J I_J_salaire)*/ c.nom, f.budget 
   FROM J, C, F
   WHERE J.cnum = C.cnum AND C.cnum = F.cnum
   AND c.division=1 AND J.salaire > 59000
   AND j.sport = 'sport1'; 

Dessiner le plan obtenu. Quel est l'inconvénient d'utiliser l'index sur l'attribut division ?

a) Soit R4:

   SELECT /*+ use_nl(J,C,F) */ C.nom, F.budget, J.licence
   FROM J, C, F
   WHERE J.cnum = C.cnum AND C.cnum = F.cnum   ;

Dessiner l'arbre obtenu. Justifier l'odre choisit. Pourquoi lire F en premier et J en dernier ?

b) Compléter la clause WHERE de R4 pour obtenir R5 de telle sorte que l'ordre choisi par le SGBD soit C,F,J

c) Ajouter une 4ème table Z et proposer une requête de jointure entre les 4 tables de telle sorte que le plan de moindre coût ne soit pas un arbre linéaire à gauche. C'est à dire que le coût de P1 soit inférieur à P2 avec :

• P1 = (J join C) join (F join Z)
• P2 = ( (J join C) join F) join Z ou tout autre arbre linéaire à gauche.

• Dans l'affichage d'un plan, que signifie l'astérisque * devant un numéro d'opérateur ? Cela renvoie au paragraphe Predicate Information qui suit le plan. Cela sert à connaitre l'ordre d'évaluation des sélections.
• La directive use_nl semble être ignorée. Vérifier la syntaxe : pas d'espace entre le J et le C dans USE_NL(J,C)
• Comment sait-on quelle opérande de la jointure est à gauche (resp. à droite). Les deux opérandes sont ordonnées par Id croissant : l'Id de gauche est inférieur à l'Id de droite.
• Est ce que l'ordre des jointures dépend de la clause WHERE ? Non, l'ordre est indépendant de l'ordre des prédicats de la clause where, ou de l'ordre des paramètres de la fonction USE_NL. L'ordre dépend seulement de la clause FROM lorsque la directive ordered est mentionnée.
• Que signifie la directive dynamic_sampling ? Lorsque le SGBD ne dispose pas de statistiques sur la distribution des valeurs des attributs, il peut lire un échantillon de la base pour approximer la sélectivité des sélections ou des jointures. Dans ce TME, le SGBD dispose des statistiques donc il n'utilise pas le dynamic sampling.
• Le plan s'affiche sans la colonne Cost. Vous avez basculé dans l'ancien mode d'optimisation rule based. Ce mode n'est plus étudié en TME. Reconnectez vous à oracle.
• Pourquoi une seule jointure s'affiche parfois avec 2 opérateurs NESTED LOOPS ?

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