Bases de Données / Databases

Site Web de l'équipe BD du LIP6 / LIP6 DB Web Site

Outils pour utilisateurs

Outils du site


site:enseignement:master:sam:tmejointure

TME Jointure

version 2020

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.

Ne pas oublier de consulter les questions fréquentes en bas de cette page

Vous êtes connecté ! en tant que membre de l'équipe BD

Préparation du TME

Lire l'énoncé de l'exercice dans le poly de TD: Exercice 3: Club de joueurs

commandedescription
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 @baseJCF 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 :

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

   EXPLAIN plan FOR SELECT ...

puis terminer chaque requête par

  @p4

Les réponses sont insérées en VERT

Exercice préliminaire

Combien de n-uplets ont chacune des relations J, C, F et BigJoueur? 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 de l'opérateur racine (Id=0) sur la première ligne.

EXPLAIN plan FOR
SELECT * FROM J;
@p4

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.

Exercice 1 : Jointure entre 2 relations

Question 1)

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.

Pour les joueurs dont le salaire est supérieur à 1000: leur n° de licence et le nom de leur club.

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.

REPONSE:
----------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  | 50000 |  1269K|    76   (2)|
|*  1 |  HASH JOIN	   |	  | 50000 |  1269K|    76   (2)|
|   2 |   TABLE ACCESS FULL| C	  |  5000 | 60000 |	7   (0)|
|*  3 |   TABLE ACCESS FULL| J	  | 50000 |   683K|    68   (0)|
----------------------------------------------------------------

REPONSE: jointure HASH JOIN

REPONSE:
        0     0=Affichage
        | 
        1     1=Jointure et projection sur j.licence, c.nom
       / \
      2   3   3=Sélection des joueurs et proj sur cnum,licence
              2=Club et proj sur cnum,nom.       

c) Quelle table est lue en premier, J ou C, pourquoi ?

table Club car plus petite en octets (60Ko) que l'autre (683Ko). Cf la colonne bytes.

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.

égal à la somme des coûts du parcours séquentiel de C et J : Cout(P1) = cout(C) + cout(J)

Question 2)

On considère la requête R2.

le prédicat sur le salaire est maintenant assez sélectif pour que le nombre de Joueurs soit inférieur au nombre de Clubs (ça change l'ordre par rapport à la 1ère requête), mais pas assez sélectif pour utiliser l'index

   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.

| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |  1000 | 26000 |    76   (2)|
|*  1 |  HASH JOIN	   |	  |  1000 | 26000 |    76   (2)|
|*  2 |   TABLE ACCESS FULL| J	  |  1000 | 14000 |    68   (0)|
|   3 |   TABLE ACCESS FULL| C	  |  5000 | 60000 |	7   (0)|
----------------------------------------------------------------

Predicate Information 
----------------------
    1 - access(J.CNUM=C.CNUM)
    2 - filter(SALAIRE<11000)

Réponse: 2=joueurs et selection et proj 3=club et proj

b) Quelle table est lue en premier J ou C, pourquoi ?

la table Joueur car on ne sélectionne que 1000 Joueurs (sur 50 000 Joueurs) pour la jointure. La taille des 1000 Joueurs (14Ko) est plus petite celle des Clubs (60Ko).

c) Quel est le coût de P2 ?

le coût de P2 est le même que P1 bien qu'on inverse l'ordre de lecture des relations. cout(C) + cout(J) = cout(J) + cout(C)

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.

Question 3)

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 ?

| Id  | Operation		     | Name	| Rows	| Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		|    15 |   360 |    29   (0)|
|   1 |  NESTED LOOPS		     |		|	|	|	     |
|   2 |   NESTED LOOPS		     |		|    15 |   360 |    29   (0)|
|*  3 |    TABLE ACCESS FULL	     | C	|     2 |    30 |     7   (0)|
|*  4 |    INDEX RANGE SCAN	     | I_J_CNUM |    10 |	|     1   (0)|
|   5 |   TABLE ACCESS BY INDEX ROWID| J	|    10 |    90 |    11   (0)|
------------------------------------------------------------------------------

Predicate Information :
    3 - filter(C.NOM='Barca' OR C.NOM='PSG')
    4 - access(J.CNUM=C.CNUM)

Reponse: NESTED LOOP

b) Détailler les étapes de l'évaluation

REPONSE :

  • Lire la table Club et sélectionner les 2 clubs. Proj sur division et cnum
  • Itération pour chaque club c (parmi les 2 clubs):
    • accéder à l'index pour obtenir la liste des rowid des joueurs du club c
    • Pour chaque rowid
      • lire le nuplet de joueur correspondant et proj sur licence

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.

REPONSE:

  • lire la table C: cout(lire C)=7 et sélectionner 2 clubs
  • pour chaque club sélectionné:
    • lire l'index : cout(lire index)=1 et obtenir 10 rowid
    • pour chaque rowid
      • lire un joueur: cout(lire nuplet) =1

cout(P3) = cout(C) + 2 * ( cout(index) + 10 * cout(lire nuplet) ) = 7 + 2 * (1 + 10*1) = 29

Question 4)

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.

| Id  | Operation		      | Name	    | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	    |	  3 |	 63 |	  8   (0)|
|   1 |  NESTED LOOPS		      | 	    |	    |	    |		 |
|   2 |   NESTED LOOPS		      | 	    |	  3 |	 63 |	  8   (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| J	    |	  3 |	 42 |	  5   (0)|
|*  4 |     INDEX RANGE SCAN	      | I_J_SALAIRE |	  3 |	    |	  2   (0)|
|*  5 |    INDEX UNIQUE SCAN	      | I_C_CNUM    |	  1 |	    |	  0   (0)|
|   6 |   TABLE ACCESS BY INDEX ROWID | C	    |	  1 |	  7 |	  1   (0)|
----------------------------------------------------------------------------------

Predicate Information
---------------------
    4 - access(J.SALAIRE>=10000 AND J.SALAIRE<=10001)
    5 - access(J.CNUM=C.CNUM)

REPONSE:

  • Accès à l'index sur le salaire (coût=2) pour obtenir les rowid des 3 joueurs qui ont le salaire demandé
  • Pour chaque rowid,
    • Lire le nuplet correspondant de la table Joueur et projeter sur cnum et licence
    • Le cnum permet d'accéder a l'index sur le cnum de Club pour obtenir le rowid du club
    • Le rowid permet de lire le nuplet correpondant de la table Club et projeter sur l'attribut division

Exercice 2: Directives USE_NL et USE_HASH pour une jointure

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.

  • La directive USE_NL indique que la jointure doit être traitée par boucles imbriquées.
  • La directive 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.

Question 1

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.

Question 2

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.

REPONSE

| Id  | Operation		     | Name	| Rows	| Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		| 50000 |  1269K| 50083   (1)|
|   1 |  NESTED LOOPS		     |		|	|	|	     |
|   2 |   NESTED LOOPS		     |		| 50000 |  1269K| 50083   (1)|
|*  3 |    TABLE ACCESS FULL	     | J	| 50000 |   683K|    68   (0)|
|*  4 |    INDEX UNIQUE SCAN	     | I_C_CNUM |     1 |	|     0   (0)|
|   5 |   TABLE ACCESS BY INDEX ROWID| C	|     1 |    12 |     1   (0)|
------------------------------------------------------------------------------
Predicate Information 
-----------------------
   3 - filter("SALAIRE">10)
   4 - access("J"."CNUM"="C"."CNUM")

Question 3

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

REPONSE

| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |    15 |   360 |    76   (2)|
|*  1 |  HASH JOIN	   |	  |    15 |   360 |    76   (2)|
|*  2 |   TABLE ACCESS FULL| C	  |	2 |    30 |	7   (0)|
|   3 |   TABLE ACCESS FULL| J	  | 50000 |   439K|    68   (0)|
----------------------------------------------------------------
Predicate Information 
----------------------
   1 - access("J"."CNUM"="C"."CNUM")
   2 - filter("C"."NOM"='Barca' OR "C"."NOM"='PSG')

Question 4

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.

REPONSE

| Id  | Operation		     | Name	   | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		   |	 3 |	63 |	13   (8)|
|*  1 |  HASH JOIN		     |		   |	 3 |	63 |	13   (8)|
|   2 |   TABLE ACCESS BY INDEX ROWID| J	   |	 3 |	42 |	 5   (0)|
|*  3 |    INDEX RANGE SCAN	     | I_J_SALAIRE |	 3 |	   |	 2   (0)|
|   4 |   TABLE ACCESS FULL	     | C	   |  5000 | 35000 |	 7   (0)|
---------------------------------------------------------------------------------
Predicate Information
---------------------
   1 - access("J"."CNUM"="C"."CNUM")
   3 - access("J"."SALAIRE">=10000 AND "J"."SALAIRE"<=10001)

Exercice 3. ORDRE des jointures entre 3 relations

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

REPONSE

CFJ:

-----------------------------------------------------------------
| Id  | Operation	    | Name | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	 1 |	40 |	82   (3)|
|*  1 |  HASH JOIN	    |	   |	 1 |	40 |	82   (3)|
|*  2 |   TABLE ACCESS FULL | J    |	 5 |	90 |	68   (0)|
|*  3 |   HASH JOIN	    |	   |  2500 | 55000 |	13   (8)|
|*  4 |    TABLE ACCESS FULL| C    |  2500 | 37500 |	 7   (0)|
|   5 |    TABLE ACCESS FULL| F    |  5000 | 35000 |	 5   (0)|
-----------------------------------------------------------------

CJF :

------------------------------------------------------------------------------
| Id  | Operation		     | Name	| Rows	| Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		|     1 |    40 |    81   (2)|
|   1 |  NESTED LOOPS		     |		|	|	|	     |
|   2 |   NESTED LOOPS		     |		|     1 |    40 |    81   (2)|
|*  3 |    HASH JOIN		     |		|     5 |   165 |    76   (2)|
|*  4 |     TABLE ACCESS FULL	     | C	|  2500 | 37500 |     7   (0)|
|*  5 |     TABLE ACCESS FULL	     | J	|     5 |    90 |    68   (0)|
|*  6 |    INDEX UNIQUE SCAN	     | I_F_CNUM |     1 |	|     0   (0)|
|   7 |   TABLE ACCESS BY INDEX ROWID| F	|     1 |     7 |     1   (0)|
------------------------------------------------------------------------------

FCJ :

-----------------------------------------------------------------
| Id  | Operation	    | Name | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	 1 |	40 |	82   (3)|
|*  1 |  HASH JOIN	    |	   |	 1 |	40 |	82   (3)|
|*  2 |   TABLE ACCESS FULL | J    |	 5 |	90 |	68   (0)|
|*  3 |   HASH JOIN	    |	   |  2500 | 55000 |	13   (8)|
|   4 |    TABLE ACCESS FULL| F    |  5000 | 35000 |	 5   (0)|
|*  5 |    TABLE ACCESS FULL| C    |  2500 | 37500 |	 7   (0)|
-----------------------------------------------------------------

FJC :

------------------------------------------------------------------------------
| Id  | Operation		     | Name	| Rows	| Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		|     1 |    40 |    79   (2)|
|   1 |  NESTED LOOPS		     |		|	|	|	     |
|   2 |   NESTED LOOPS		     |		|     1 |    40 |    79   (2)|
|*  3 |    HASH JOIN		     |		|     5 |   125 |    74   (2)|
|   4 |     TABLE ACCESS FULL	     | F	|  5000 | 35000 |     5   (0)|
|*  5 |     TABLE ACCESS FULL	     | J	|     5 |    90 |    68   (0)|
|*  6 |    INDEX UNIQUE SCAN	     | I_C_CNUM |     1 |	|     0   (0)|
|*  7 |   TABLE ACCESS BY INDEX ROWID| C	|     1 |    15 |     1   (0)|
------------------------------------------------------------------------------

JCF :

--------------------------------------------------------------------------------
| Id  | Operation		       | Name	  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	  |	1 |    40 |    78   (0)|
|   1 |  NESTED LOOPS		       |	  |	  |	  |	       |
|   2 |   NESTED LOOPS		       |	  |	1 |    40 |    78   (0)|
|   3 |    NESTED LOOPS 	       |	  |	5 |   165 |    73   (0)|
|*  4 |     TABLE ACCESS FULL	       | J	  |	5 |    90 |    68   (0)|
|*  5 |     TABLE ACCESS BY INDEX ROWID| C	  |	1 |    15 |	1   (0)|
|*  6 |      INDEX UNIQUE SCAN	       | I_C_CNUM |	1 |	  |	0   (0)|
|*  7 |    INDEX UNIQUE SCAN	       | I_F_CNUM |	1 |	  |	0   (0)|
|   8 |   TABLE ACCESS BY INDEX ROWID  | F	  |	1 |	7 |	1   (0)|
--------------------------------------------------------------------------------

JFC :

--------------------------------------------------------------------------------
| Id  | Operation		       | Name	  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	  |	1 |    40 |    78   (0)|
|   1 |  NESTED LOOPS		       |	  |	  |	  |	       |
|   2 |   NESTED LOOPS		       |	  |	1 |    40 |    78   (0)|
|   3 |    NESTED LOOPS 	       |	  |	5 |   125 |    73   (0)|
|*  4 |     TABLE ACCESS FULL	       | J	  |	5 |    90 |    68   (0)|
|   5 |     TABLE ACCESS BY INDEX ROWID| F	  |	1 |	7 |	1   (0)|
|*  6 |      INDEX UNIQUE SCAN	       | I_F_CNUM |	1 |	  |	0   (0)|
|*  7 |    INDEX UNIQUE SCAN	       | I_C_CNUM |	1 |	  |	0   (0)|
|*  8 |   TABLE ACCESS BY INDEX ROWID  | C	  |	1 |    15 |	1   (0)|
--------------------------------------------------------------------------------

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.

REPONSE

-----------------------------------------------------------------------------------
| Id  | Operation		       | Name	     | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	     |	   1 |	  40 |	1007   (0)|
|   1 |  NESTED LOOPS		       |	     |	     |	     |		  |
|   2 |   NESTED LOOPS		       |	     |	   1 |	  40 |	1007   (0)|
|   3 |    NESTED LOOPS 	       |	     |	   5 |	 125 |	1002   (0)|
|*  4 |     TABLE ACCESS BY INDEX ROWID| J	     |	   5 |	  90 |	 997   (0)|
|*  5 |      INDEX RANGE SCAN	       | I_J_SALAIRE |	 997 |	     |	   4   (0)|
|   6 |     TABLE ACCESS BY INDEX ROWID| F	     |	   1 |	   7 |	   1   (0)|
|*  7 |      INDEX UNIQUE SCAN	       | I_F_CNUM    |	   1 |	     |	   0   (0)|
|*  8 |    INDEX UNIQUE SCAN	       | I_C_CNUM    |	   1 |	     |	   0   (0)|
|*  9 |   TABLE ACCESS BY INDEX ROWID  | C	     |	   1 |	  15 |	   1   (0)|
-----------------------------------------------------------------------------------

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.

REPONSE

------------------------------------------------------------------------------------
| Id  | Operation		       | Name	      | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	      |     1 |    40 |    97	(2)|
|   1 |  NESTED LOOPS		       |	      |       |       | 	   |
|   2 |   NESTED LOOPS		       |	      |     1 |    40 |    97	(2)|
|*  3 |    HASH JOIN		       |	      |     5 |   165 |    92	(2)|
|*  4 |     TABLE ACCESS FULL	       | J	      |     5 |    90 |    68	(0)|
|   5 |     TABLE ACCESS BY INDEX ROWID| C	      |  2500 | 37500 |    23	(0)|
|*  6 |      INDEX RANGE SCAN	       | I_C_DIVISION |  2500 |       |     5	(0)|
|*  7 |    INDEX UNIQUE SCAN	       | I_F_CNUM     |     1 |       |     0	(0)|
|   8 |   TABLE ACCESS BY INDEX ROWID  | F	      |     1 |     7 |     1	(0)|
------------------------------------------------------------------------------------

Questions fréquentes

  • 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

Divers

site/enseignement/master/sam/tmejointure.txt · Dernière modification: 29/01/2020 11:54 par hubert