Bases de Données / Databases

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

Outils pour utilisateurs

Outils du site

Piste: • TME 4-5 Plan et Index

Panneau latéral

English version

site:enseignement:licence:3i009:tmeindex

Table des matières

TME 4-5 Plan et Index

L'objectif de ce TME est de comprendre l'utilisation des index pour évaluer des sélections: création d'un index, choix d'un ou plusieurs index pour évaluer une requête, avantages/inconvénients d'un index. Ce TME dure 2 séances :

  • Séance 1 : Exercices 1, 2, 3
  • Séance 2 : Exercices 4 et suivants

Préparation

Salle PPTI et client sqlplus

Commandes utiles:

commandedescription
cd mon_répertoire aller dans votre répertoire de travail
tar zxvf /Infos/bd/public/tmeIndex.tgz installer l'archive dans votre répertoire principal
cd tmeIndex aller dans le répertoire du TME
emacs tmeIndex.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 la ligne contenant @annuaire et faire Ctrl-C Ctrl-C définir la table Annuaire et un synonyme pour la table BigAnnuaire

SQLWorkbench

Pour ceux qui font le TME avec SQLWorkbench, relire les instructions pour se connecter à Oracle avec SQLWorkbench. Tous les fichiers utiles sont dans l'archive ci-dessous :

  • télécharger et décompresser tmeIndex.zip
  • ouvrir et exécuter le fichier annuaire.sql dans SQL Workbench
  • ouvrir le fichier tmeIndexWb.sql

Données interrogées

Dans ce TME on interroge les deux tables Annuaire et BigAnnuaire.

TableNbre de tuplesTaille (moyenne) d'un tuple
Annuaire2000 3846
BigAnnuaire 220 000 3846

Les schémas des deux tables sont indentiques et contiennent les attributs suivants :

AttributValeurs distinctesDomaineTypeIndex
âge100[1-100] NUMBER(3)IndexAge
cp1000[1000,100 900], multiples de 100 NUMBER(3)IndexCP
nom100-VARCHAR2(30)-
prénom90-VARCHAR2(30)-
tel100 000 pour BigAnnuaire-VARCHAR2(10)-
profil90 000 pour BigAnnuaire-VARCHAR2(4000)-

Les deux tables sont indexées : IndexAge sur l'attribut age et IndexCP sur l'attribut cp.

Exercice (préparation et explications)

Dans le poly de TD, TD4-5, lire l'énoncé de l'exercice 2 : Plan d’une requête utilisant un index.

Lire la section Questions fréquentes en bas de page.

On étudie des requêtes de sélection sur 1 ou 2 attributs. Il y a trois types de prédicats de sélection : l'égalité, l'inégalité et l'inclusion dans un intervalle.

Salle PPTI avec client sqlplus

Le SGBD transforme une requête en un plan avant de l'évaluer. Pour afficher le plan d'une requête, commencer chaque requête par 

   explain plan for SELECT ...

puis terminer chaque requête par 

  @p3

cela invoque le fichier p3.sql contenant les instructions pour afficher le plan de la requête.

Remarque1 : pour régler l'affichage plus ou moins détaillé d'un plan, remplacer @p3 par le niveau de détail souhaité allant de 1 à 5 : de @p1 (plan peu détaillé) jusqu'à @p4 (plan avec son coût) ou @p5 (plan avec tous les détails).

Remarque2: Problème d'affichage trop long. si par erreur vous avez lancé l'exécution d'une requête en oubliant l'entête explain plan for vous pourriez être gêné par l'affichage de plusieurs milliers de nuplets. Vous pouvez stopper la requête : cliquer dans la fenêtre nommée *SQL* puis cliquer sur le menu Signals→BREAK

SQL Workbench

Configurer la police de caractères pour SQLWorkbench

Pour afficher proprement les plans il faut utiliser la police de caractère “Courrier New”. Pour cela, aller dans le menu Tools → Options → Fonts et modifier le champ Data Font pour sélectionner Courrier New, 12

Ensuite le plan s'affiche avec la bonne indentation pour voir l'arbre des opérations : une opération fille est indentée d'un espace à droite par rapport à son parent. Par exemple, l'opération INDEX est sous TABLE ACCESS qui est elle même sous SELECT :

Macros

Avec SQL Workench vous pouvez créer les plans des requêtes avec des macros. Pour cela, il faut d'abord charger le fichier WbMacros-optimize.xml dans le menu Macros → Load Macros…

puis

Vous pouvez ensuite générer les plans en vous positionnant sur la requête et en choisissant la macro correspondant dans le menu des macros.

Exemple: Au lieu d'écrire : 

EXPLAIN plan FOR
    SELECT * FROM Annuaire;
@p3

vous écrivez la requête : 

SELECT * FROM Annuaire;

et vous exécutez la macro p3 (touche F1 du clavier) avec le curseur dans la requête.

Les requêtes du TME se trouvent dans le fichier tmeindexWb.sql.

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

Exercice préliminaire. Statistiques sur les tables

Pour déterminer le plan d'une requête, le SGBD s'appuie sur des statistiques décrivant les données. Les statistiques sont, entre autres, la cardinalité d'une table et le nombre de valeurs distinctes d'un attribut.

Observer les statistiques qu'utilise le SGBD (lire la valeur dans la colonne ROWS).

La cardinalité d'une table : 

EXPLAIN plan FOR
    SELECT * FROM Annuaire;
@p3
EXPLAIN plan FOR
    SELECT * FROM BigAnnuaire;
@p3

Pour BigAnnuaire, afficher le nombre de valeurs distinctes de chaque attribut; 

EXPLAIN plan FOR
    SELECT DISTINCT nom FROM BigAnnuaire;
@p3
EXPLAIN plan FOR
   SELECT DISTINCT prenom FROM BigAnnuaire;
@p3

et ainsi de suite pour les attributs âge, cp, tel et profil.

remarque : Pour accéder aux valeurs d'attributs indexés (comme par exemple age), le système préfère souvent de lire l'index (plus petit) au lieu de la table. Le fichier analyse.sql contient les instructions invoquant le pré-calcul des statistiques.

Exercice 1. Requête de sélection utilisant un index

Expliquer le plan des requêtes suivantes. Détailler chaque étape de l'évaluation d'un plan.

Question a) : 

EXPLAIN plan FOR
    SELECT a.nom, a.prenom
    FROM BigAnnuaire a
    WHERE a.age = 18;
@p3

REPONSE 

-----------------------------------------------------------
| Id  | Operation		    | Name	  | Rows  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT	    |		  |  2200 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE |  2200 |
|*  2 |   INDEX RANGE SCAN	    | INDEXAGE	  |  2200 |
-----------------------------------------------------------
Predicate Information 
----------------------
    2 - access(A.AGE=18)
  • Evaluer l'opération Id=2 nommée INDEX RANGE SCAN: traverser l'index IndexAge pour accéder aux identifiants des nuplets des personnes ayant 18 ans. Le prédicat access(AGE=18) attachée à l'opération Id=2 précise qu'on accède à l'index pour atteindre la feuille contenant 18.
  • Puis le traitement se poursuit avec l'opération Id=1 nommée TABLE ACCESS BY INDEX ROWID : pour chaque personne, lire le nuplet de cette personne.

Question b) : 

EXPLAIN plan FOR
    SELECT a.nom, a.prenom
    FROM BigAnnuaire a
    WHERE a.age BETWEEN 20 AND 29;
@p3

REPONSE 

-----------------------------------------------------------
| Id  | Operation		    | Name	  | Rows  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT	    |		  | 24400 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE | 24400 |
|*  2 |   INDEX RANGE SCAN	    | INDEXAGE	  | 24400 |
-----------------------------------------------------------
Predicate Information
---------------------
   2 - access(A.AGE>=20 AND A.AGE<=29)

Column Projection
-----------------
   1 - "A"."NOM"[VARCHAR2,30], "A"."PRENOM"[VARCHAR2,30]
   2 - "A".ROWID[ROWID,10]

Question c) : 

EXPLAIN plan FOR
   SELECT a.nom, a.prenom
   FROM BigAnnuaire a
   WHERE a.age < 70 AND (a.cp = 93000 OR a.cp = 75000);
@p3

REPONSE

INLIST ITERATOR: Le OR est reformulé en un IN (75000, 93000) le INLIST évalue le prédicat IN: Pour chaque cp dans la liste de codepostaux: accès à l'index cp, puis accès par rowid aux nuplets et filtrer age<70. 

------------------------------------------------------------
| Id  | Operation		     | Name	   | Rows  |
------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		   |   307 |
|   1 |  INLIST ITERATOR	     |		   |	   |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE |   307 |
|*  3 |    INDEX RANGE SCAN	     | INDEXCP	   |   440 |
------------------------------------------------------------
Predicate Information
---------------------
   2 - filter("A"."AGE"<70)
   3 - access("A"."CP"=75000 OR "A"."CP"=93000)

Question d) : 

EXPLAIN plan FOR
    SELECT a.nom, a.prenom
    FROM BigAnnuaire a
    WHERE a.age = 20 AND a.cp = 13000 AND a.nom LIKE 'T%';
@p3

Remarque: Oracle utilise deux index pour l'évaluation de cette requête et transforme la conjonction SQL (AND) en une intersection des adresses n-uplets (ROWID). Cette intersection est calculée par l'opérateur BITMAP AND sur un encodage binaire des ensembles d'adresses (en BITMAP).

REPONSE: Ne PAS trop passer de temps à expliquer le détail de BITMAP conversion. Dire que c'est simplement un intersection de ROWID. 

----------------------------------------------------------------
| Id  | Operation			 | Name        | Rows  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT		 |	       |     1 |
|*  1 |  TABLE ACCESS BY INDEX ROWID	 | BIGANNUAIRE |     1 |
|   2 |   BITMAP CONVERSION TO ROWIDS	 |	       |       |
|   3 |    BITMAP AND			 |	       |       |
|   4 |     BITMAP CONVERSION FROM ROWIDS|	       |       |
|*  5 |      INDEX RANGE SCAN		 | INDEXCP     |   220 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|	       |       |
|*  7 |      INDEX RANGE SCAN		 | INDEXAGE    |   220 |
----------------------------------------------------------------
Predicate Information
---------------------
   1 - filter("A"."NOM" LIKE 'T%')
   5 - access("A"."CP"=13000)
   7 - access("A"."AGE"=20)

Exercice 2. Sélection AVEC OU SANS index

Dans les plans suivants, on affiche son coût (voir la colonne Cost)

Question a) : Etudiez plusieurs variantes de la requête sélectionnant les personnes dont l'âge est inférieur à une valeur donnée. Pour cela, testez les prédicats de la forme age < = A avec A valant 10, 30, 40, 60 et 80. 

EXPLAIN plan FOR
    SELECT a.nom, a.prenom
    FROM BigAnnuaire a
    WHERE a.age <= 10;
@p4

REPONSE 

--------------------------------------------------------------------------------
| Id  | Operation		    | Name	  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		  | 20000 |   351K| 20046   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE | 20000 |   351K| 20046   (1)|
|*  2 |   INDEX RANGE SCAN	    | INDEXAGE	  | 20000 |	  |    41   (0)|
--------------------------------------------------------------------------------
Predicate Information
---------------------
   2 - access("A"."AGE"<10)

Compléter le tableau en indiquant la cardinalité, le coût et si l'index IndexAge est utilisé ou non.

Prédicat Rows Index utilisé Cout
age < = 10 oui
age < = 20
age < = 30
age < = 40
age < = 60
age < = 100

REPONSE

Prédicat Rows Index utilisé Cout
age < = 10 22 200 oui 20 046
age < = 20 44 400 oui 42 317
age < = 30 66 600 oui 64 587
age < = 40 88 800 NON 70 893
age < = 60 133K NON 70 893
age < = 100 220K NON 70 893

Question b) : Pour quel prédicat Oracle préfère-t-il évaluer la requête sans utiliser l'index IndexAge ? Pourquoi ?

REPONSE A partir de age < = 40 lire toute la table coute moins cher que de lire l'index.

Question c) : Proposer deux requêtes BETWEEN 50000 AND … sélectionnant un intervalle de valeurs du code postal comprises entre 50000 et N.

  • la première utilise l'index IndexCP,
  • la deuxième ne l'utilise pas.
EXPLAIN plan FOR
    SELECT a.nom, a.prenom
    FROM BigAnnuaire a
    WHERE a.cp BETWEEN 50000 AND ....;
@p4

REPONSE 

EXPLAIN plan FOR
    SELECT a.nom, a.prenom
    FROM BigAnnuaire a
    WHERE a.cp BETWEEN 50000 AND 80000;
@p4
--------------------------------------------------------------------------------
| Id  | Operation		    | Name	  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		  | 66506 |  1233K| 66663   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE | 66506 |  1233K| 66663   (1)|
|*  2 |   INDEX RANGE SCAN	    | INDEXCP	  | 66506 |	  |   140   (0)|
--------------------------------------------------------------------------------
EXPLAIN plan FOR
    SELECT a.nom, a.prenom
    FROM BigAnnuaire a
    WHERE a.cp BETWEEN 50000 AND 90000;
@p4
----------------------------------------------------------------------
| Id  | Operation	  | Name	| Rows	| Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT  |		| 88528 |  1642K| 70893   (1)|
|*  1 |  TABLE ACCESS FULL| BIGANNUAIRE | 88528 |  1642K| 70893   (1)|
----------------------------------------------------------------------
Predicate Information
---------------------
   1 - filter("A"."CP">=50000 AND "A"."CP"<=90000)

Exercice 3. Comparaison de plans d'exécutions équivalents

Pour une requête donnée, on veut étudier plusieurs plans équivalents afin de comparer le coût de chacun d'entre eux. Rappel pour afficher le coût, utiliser @p4 ou la macro associée à la touche F2 dans SQLWorkbench

Directive pour forcer/empêcher l'usage d'un index

Énumérer les plans équivalents revient à considérer toutes les combinaisons entre utiliser des index ou pas en ajoutant les directives index(Table Index) et no_index(Table Index) dans les requêtes SQL. Le 1er paramètre est le nom d'une table ou d'un alias déclaré dans la clause FROM. Le 2eme paramètre est le nom d'un index. La syntaxe est détaillée ci-dessous.

Question a) : Comparez deux plans équivalents pour la requête age < 7 qui est très sélective.

  • La directive index() force l'usage d'un index.
EXPLAIN plan FOR
   SELECT /*+ index(a IndexAge) */  a.nom, a.prenom 
   FROM BigAnnuaire a WHERE a.age < 7;
@p4
  • La directive no_index() empêche l'usage d'un index:
EXPLAIN plan FOR
   SELECT /*+  no_index(a IndexAge) */  a.nom, a.prenom
   FROM BigAnnuaire a WHERE a.age < 7;
@p4

Vérifier que le plan de coût minimal est bien celui choisi sans aucune directive.

Question b) : Idem pour la requête age > 19 qui est peu sélective.

Question c) : Pour la requête age = 18 and cp = 75000 proposer 4 plans équivalents et donner leur coût. Vérifier que le plan de coût minimal est bien celui choisi sans aucune directive.

Pour forcer l'index IndexAge et empêcher l'index IndexCP dans une même requête : 

EXPLAIN plan FOR
    SELECT /*+ index(a IndexAge) no_index(a IndexCp)  */  a.nom, a.prenom 
    FROM BigAnnuaire a WHERE a.age = 18 AND a.cp = 75000;
@p4

Remarque: Déclarer plusieurs directives index(..,…) ne force pas à utiliser les plusieurs index simultanément, mais force à en utiliser un (le meilleur). Voir plutôt index_combine ou index_join pour cela https://docs.oracle.com/cd/B10501_01/server.920/a96533/hintsref.htm#5215

Exercice 4. Requête de jointure utilisant un index

Il existe une table Ville (cp, ville, population) qui contient le nom de la ville pour chaque code postal cp.

Décrire les plans suivants en rédigeant le pseudo-code correspondant au plan. Mettre en évidence les itérations foreach en détaillant sur quel ensemble se fait l'itération et le contenu d'une itération.

Question a) : 

EXPLAIN plan FOR
    SELECT a.nom, a.prenom, v.ville
    FROM Annuaire a, Ville v
    WHERE a.cp = v.cp
    AND a.age=18;
@p3

REPONSE 

---------------------------------------------------------
| Id  | Operation		     | Name	| Rows	|
---------------------------------------------------------
|   0 | SELECT STATEMENT	     |		|    20 |
|*  1 |  HASH JOIN		     |		|    20 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ANNUAIRE |    20 |
|*  3 |    INDEX RANGE SCAN	     | INDEXAGE |    20 |
|   4 |   TABLE ACCESS FULL	     | VILLE	|  1000 |
---------------------------------------------------------
Predicate Information
---------------------
   1 - access("A"."CP"="V"."CP")
   3 - access("A"."AGE"=18)

Question b) : Même requête mais en remplaçant Annuaire par BigAnnuaire. Pourquoi la table Ville est-elle lue avant l'annuaire alors que dans la requête a) c'est la table Annuaire qui est lue avant les villes ? 

EXPLAIN plan FOR
    SELECT a.nom, a.prenom, v.ville
    FROM BigAnnuaire a, Ville v
    WHERE a.cp = v.cp
    AND a.age=18;
@p3

REPONSE

La table Ville (2000 villes) est plus petite que la sélection des personnes qui ont 18 ans (2200 personnes). Donc oracle préfère charger en mémoire les villes plutot que les personnes ayant 18 ans. 

------------------------------------------------------------
| Id  | Operation		     | Name	   | Rows  |
------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		   |  2200 |
|*  1 |  HASH JOIN		     |		   |  2200 |
|   2 |   TABLE ACCESS FULL	     | VILLE	   |  1000 |
|   3 |   TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE |  2200 |
|*  4 |    INDEX RANGE SCAN	     | INDEXAGE    |  2200 |
------------------------------------------------------------
Predicate Information
---------------------
   1 - access("A"."CP"="V"."CP")
   4 - access("A"."AGE"=18)

Question c) : 

EXPLAIN plan FOR
    SELECT a.nom, a.prenom, v.ville
    FROM BigAnnuaire a, Ville v
    WHERE a.cp = v.cp
    AND v.population >= 985000;
@p3

REPONSE Il y a 16,57 villes avec une population supérieure à 985000 (valeur arrondie à 17 dans l'affichage) On a 220 personnes par ville en moyenne Donc 16,57 * 220 = 3647 

------------------------------------------------------------
| Id  | Operation		     | Name	   | Rows  |
------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		   |  3647 |
|   1 |  NESTED LOOPS		     |		   |	   |
|   2 |   NESTED LOOPS		     |		   |  3647 |
|*  3 |    TABLE ACCESS FULL	     | VILLE	   |	17 |
|*  4 |    INDEX RANGE SCAN	     | INDEXCP	   |   220 |
|   5 |   TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE |   220 |
------------------------------------------------------------
Predicate Information:
----------------------
   3 - filter(V.POPULATION>=985000)
   4 - access(A.CP=V.CP)

Exercice 5: Autres requêtes

Dans cet exercice on vous demande d'expliquer “par vous même” un plan contentant un opérateur qui n'a pas été détaillé en cours.

a) Requêtes avec group by 

EXPLAIN plan FOR
    SELECT age, COUNT(*)
    FROM BigAnnuaire a
    GROUP BY age;
@p3

REPONSE

pas d'acces à la table pour cette requete. Seulement un parcours des feuilles de l'index. 

| Id  | Operation	      | Name	 | Rows  |
--------------------------------------------------
|   0 | SELECT STATEMENT      | 	 |   100 |
|   1 |  HASH GROUP BY	      | 	 |   100 |
|   2 |   INDEX FAST FULL SCAN| INDEXAGE |   220K|
--------------------------------------------------

b) Requêtes avec group by having 

EXPLAIN plan FOR
    SELECT age, COUNT(*)
    FROM BigAnnuaire a
    GROUP BY age
    HAVING COUNT(*) > 200;
@p3

REPONSE

Operation nommée FILTER pour évaluer le having. 

| Id  | Operation	       | Name	  | Rows  |
---------------------------------------------------
|   0 | SELECT STATEMENT       |	  |	5 |
|*  1 |  FILTER 	       |	  |	  |
|   2 |   HASH GROUP BY        |	  |	5 |
|   3 |    INDEX FAST FULL SCAN| INDEXAGE |   220K|
---------------------------------------------------
Predicate Information
---------------------
    1 - filter(COUNT(*)>200)

c) Requete min max 

EXPLAIN plan FOR
    SELECT MIN(cp), MAX(cp)
    FROM BigAnnuaire a;
@p3

REPONSE

Sort aggregate: trier et ne garder que les valeurs min et max 

| Id  | Operation	      | Name	| Rows	|
-------------------------------------------------
|   0 | SELECT STATEMENT      | 	|     1 |
|   1 |  SORT AGGREGATE       | 	|     1 |
|   2 |   INDEX FAST FULL SCAN| INDEXCP |   220K|
-------------------------------------------------
Column Projection
-----------------
    2 - "CP"

d) Requête avec not in 

EXPLAIN plan FOR
    SELECT a.nom, a.prenom
    FROM BigAnnuaire a
    WHERE a.prenom NOT IN ( SELECT b.prenom
                        FROM BigAnnuaire b
			WHERE b.age<=7);
@p3

REPONSE

OPeration n°1: ANTI join: si la condition a.prenom=b.prenom est fausse alors le nuplet est dans le résultat. 

| Id  | Operation		     | Name	   | Rows  |
------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		   |  2444 |
|*  1 |  HASH JOIN RIGHT ANTI	     |		   |  2444 |
|   2 |   TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE | 15533 |
|*  3 |    INDEX RANGE SCAN	     | INDEXAGE    | 15533 |
|   4 |   TABLE ACCESS FULL	     | BIGANNUAIRE |   220K|
------------------------------------------------------------
Predicate Information
---------------------
   1 - access(A.PRENOM=B.PRENOM)
   3 - access(B.AGE<=7)
Column Projection
-----------------
   1 - (#keys=1) A.PRENOM, A.NOM
   2 - B.PRENOM
   3 - B.ROWID
   4 - A.NOM, A.PRENOM

e) Requête avec not exists 

EXPLAIN plan FOR
    SELECT a.nom, a.prenom
    FROM BigAnnuaire a
    WHERE NOT EXISTS ( SELECT *
                       FROM BigAnnuaire b
		       WHERE b.prenom = a.prenom
		       AND b.age < a.age);
@p3

REPONSE

Opération n°2: Lire toute la table et indexer en mémoire les couples (prenom,age) sur le prenom. Puis relire toute la table: pour chaque personne p interroger l'index construit à l'étape précédente pour chercher si un couple a le meme prenom et un age plus petit. Si aucun couple trouvé: alors ajouter la personne p dans le résultat. 

----------------------------------------------------
| Id  | Operation	     | Name	   | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT     |		   |  4620 |
|*  1 |  HASH JOIN RIGHT ANTI|		   |  4620 |
|   2 |   TABLE ACCESS FULL  | BIGANNUAIRE |   220K|
|   3 |   TABLE ACCESS FULL  | BIGANNUAIRE |   220K|
----------------------------------------------------
Predicate 
----------
   1 - access(B.PRENOM=A.PRENOM) filter(B.AGE < A.AGE)
Column Projection
-----------------
   1 - A.PRENOM, A.NOM
   2 - B.PRENOM B.AGE
   3 - A.NOM, A.PRENOM, A.AGE

f) Requête avec minus : les code spostaux des villes qui n'ont pas de centenaire. 

EXPLAIN plan FOR
  SELECT cp
  FROM BigAnnuaire a
  minus
   SELECT cp
   FROM BigAnnuaire b
   WHERE b.age>=100;
@p3

REPONSE

2 index (indexage et indexcp) sont utilisés pour produire la liste des cp où résident des centenaires.

L'index indexcp est aussi utilisé pour obtenir la liste de tous les cp. 

| Id  | Operation		 | Name 	    | Rows  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT	 |		    |	220K|
|   1 |  MINUS			 |		    |	    |
|   2 |   SORT UNIQUE		 |		    |	220K|
|   3 |    INDEX FAST FULL SCAN  | INDEXCP	    |	220K|
|   4 |   SORT UNIQUE		 |		    |  2200 |
|*  5 |    VIEW 		 | index$_join$_002 |  2200 |
|*  6 |     HASH JOIN		 |		    |	    |
|*  7 |      INDEX RANGE SCAN	 | INDEXAGE	    |  2200 |
|   8 |      INDEX FAST FULL SCAN| INDEXCP	    |  2200 |
-------------------------------------------------------------
Predicate Information
---------------------
   5 - filter(B.AGE>=100)
   6 - access(ROWID=ROWID)
   7 - access(B.AGE>=100)

g) requête avec where age >= ALL (…) 

EXPLAIN plan FOR
    SELECT a.nom, a.prenom
    FROM BigAnnuaire a
    WHERE a.age >= ALL (SELECT b.age 
                       FROM BigAnnuaire b
		       WHERE b.cp = 75000);
@p3

REPONSE

Opérations 3 et 2: trier les personnes par age croissant.

Opérations 6,5,4: accès aux personnes cp=75000 puis trier leur age.

Opération 1: Fusion (ANTI signifie que la condition de jointure access(a.age<b.age) doit être fausse pour que le nuplet soit dans le résultat). 

| Id  | Operation		      | Name	    | Rows  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	    |  2200 |
|   1 |  MERGE JOIN ANTI	      | 	    |  2200 |
|   2 |   SORT JOIN		      | 	    |	220K|
|   3 |    TABLE ACCESS FULL	      | BIGANNUAIRE |	220K|
|*  4 |   SORT UNIQUE		      | 	    |	220 |
|   5 |    TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE |	220 |
|*  6 |     INDEX RANGE SCAN	      | INDEXCP     |	220 |
-------------------------------------------------------------
Predicate Information
---------------------
   4 - access(A.AGE<B.AGE)  filter(A.AGE<B.AGE)
   6 - access(B.CP=75000)

h) Requete avec UNION, avec UNION ALL, avec une division, …

Exercice 6: Documentation

Facultatif en 2017.

Vous pouvez interroger le dictionnaire du SGBD pour obtenir des informations détaillées sur vos tables et vos index.

Description d'un index : profondeur de l'arbre, nombre de valeurs indexées. Interroger user_indexes 

   SELECT index_name, blevel, distinct_keys FROM user_indexes;

Description d'une table : cardinalité, taille totale. Interroger user_tables 

    SELECT TABLE_NAME, num_rows, blocks FROM user_tables;

Description d'un attribut : valeur min, max, nb de valeurs disctinctes. Interroger user_tab_cols 

    COLUMN TABLE_NAME format A20
    COLUMN column_name format A20
    SELECT TABLE_NAME, column_name, utl_raw.cast_to_number(low_value) AS borneInf,  utl_raw.cast_to_number(high_value) AS borneSup, num_distinct, histogram
    FROM user_tab_cols
    WHERE data_type = 'NUMBER';

etc… de nombreuses autres informations sont disponibles tq par exemple l'histogramme représentant la distribution des valeurs d'un attribut. Voir la liste des vues que vous pouvez interroger.

Questions fréquentes

- Directives d'optimisation : attention à la syntaxe. Ne pas confondre les caractères étoile * du commentaire et celui du select étoile. La ligne contient 3 caractères étoiles. Le caractère plus + est collé au premier caractère étoile. 

    SELECT /*+ directive */ *
    FROM 
    WHERE

S'il y a une erreur de syntaxe dans une directive Oracle ignore la directive SANS vous avertir.

- Error: cannot fetch last explain plan from plan_table : il y a une erreur de syntaxe dans votre requête. La requête n'a pas pu être analysée par oracle. Corriger la requête.

- La cardinalité estimée (ROWS) d'un opérateur semble être celle de l'opérateur parent. Pour certaines opérations binaires, la cardinalité de l'opération est indiquée sur le fils de droite plutôt que sur l'opération elle même.

- Emacs : avant d'exécuter une requête (avec Ctrl-C Ctrl-C) vérifier qu'elle est bien suivie d'une ligne entièrement vide ne contenant aucun espace.

Divers

TME 4-5 (ancien exercice 2016)

site/enseignement/licence/3i009/tmeindex.txt · Dernière modification: 17/11/2020 00:34 par hubert

Outils de la page

Sauf mention contraire, le contenu de ce wiki est placé sous les termes de la licence suivante : CC Attribution-Noncommercial-Share Alike 3.0 Unported
CC Attribution-Noncommercial-Share Alike 3.0 Unported Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki