Bases de Données / Databases

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

Outils pour utilisateurs

Outils du site

Piste :
site:enseignement:master:bdr:tmeindex

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Les deux révisions précédentesRévision précédente
Prochaine révision		Révision précédente
site:enseignement:master:bdr:tmeindex [25/01/2017 12:36] – [Exercice 6. Comparaison de plans d'exécutions équivalents] hubertsite:enseignement:master:bdr:tmeindex [20/02/2019 15:32] (Version actuelle) – hubert
Ligne 1: Ligne 1:
 {{indexmenu_n>1}} {{indexmenu_n>1}}
-====== TME Index====== 
  
 +/** TODO
 +* remplacer 
 +**/
 +
 +
 +
 +
 +====== TME 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 : 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 et 3 +  * Séance 1 :  Exercices 1, 2, 3
   * Séance 2 :  Exercices 4 et suivants   * Séance 2 :  Exercices 4 et suivants
 +
 +
 ===== Préparation du TME ===== ===== Préparation du TME =====
 +
 +Lire la doc sur la [[site:enseignement:documentation:oracle:connexionoracle]]
  
 ^commande^description^ ^commande^description^
Ligne 14: Ligne 25:
 | emacs tmeIndex.sql & | éditer le fichier à compléter pendant le 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 [[site:enseignement:documentation:oracle:connexionoracle|ConnexionOracle]] | | **Alt-x** my/sql-oracle //ou// **Atl-x** sql-oracle | se connecter à  Oracle.  Voir [[site:enseignement:documentation:oracle:connexionoracle|ConnexionOracle]] |
-| aller sur la ligne  contenant @annuaire et faire  Ctrl-C Ctrl-C | définir la table Annuaire  et les synonymes pour les tables BigAnnuaireSimple et BigAnnuaire |+| aller sur la ligne  contenant @annuaire et faire  Ctrl-C Ctrl-C | définir la table Annuaire et un synonyme pour la table BigAnnuaire |
  
-La table **Annuaire**(nom, prénom, age,  cp, tel, profil) contient 1000 nuplets.  
-  * L'âge va de 1 à 100 
-  * Les codes postaux (cp) sont des multiples de 100 et sont compris entre 1000 et 100 900. Il y a 1000 valeurs distinctes. 
-  * Le numéro de téléphone est unique ; c'est une chaîne de 10 chiffres commençant par 0.  
-  * Il y a 90 prénoms et 100 noms différents.  
-  * Le profil est une chaine de 1500 caractères. 
  
-La table **<fc #ff0000>BigAnnuaire</fc>**(nom, prénom, age,  cp, tel, profil)  a un attribut //profil// pouvant contenir jusqu'à 4000 caratères. La table a 220 000 nuplets. +Dans ce TME on interroge les deux tables Annuaire et BigAnnuaire.  
 +^Table^Nbre de tuples^Taille (moyenne) d'un tuple^ 
 +|Annuaire|2000| 3846 | 
 +|BigAnnuaire |220 000| 3846 |
  
-La table **<fc #ff0000>BigAnnuaireSimple</fc>** est identique à la table BigAnnuaire (même schéma, autant de nuplets). La différence est que BigAnnuaireSimple ne possède **pas** d'index..+Les schémas des deux tables sont indentiques et contiennent les attributs suivants :
  
-Lire la section //Questions fréquentes// en bas de page. Répondre aux questions dans le fichier tmeIndex.sql+^Attribut^Valeurs distinctes^Domaine^Type^Index^ 
 +|âge|100|[1-100]| NUMBER(3)|IndexAge| 
 +|cp|1000|[1000,100 900], multiples de 100| NUMBER(3)|IndexCP| 
 +|nom|100|-|VARCHAR2(30)|-| 
 +|prénom|90|-|VARCHAR2(30)|-| 
 +|tel|100 000 pour BigAnnuaire|-|VARCHAR2(10)|-| 
 +|profil|90 000 pour BigAnnuaire|-|VARCHAR2(4000)|-|
  
 +Les deux tables sont indexées : **IndexAge** sur l'attribut //age// et **IndexCP** sur l'attribut //cp//.
  
 +/*
 +Dans le poly de TD,  **<fc #ff0000>lire l'énoncé</fc>** de l'exercice 2 : **Plan d’une requête utilisant un index**.
 +*/
 +
 +Lire la section [[#questions_frequentes|Questions fréquentes]] en bas de page. 
  
-===== Ex1: Requêtes avec prédicat de sélection ===== 
 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.  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. 
-Les requêtes seront posées sur la table <fc #ff0000>**BigAnnuaireSimple ou BigAnnuaire.**</fc>+ 
 +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 
 +<code ascii> 
 +   explain plan for SELECT ... 
 +</code> 
 +puis terminer chaque requête par 
 +    @p3 
 + 
 + 
 +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 
 + 
 +<showif isloggedin> 
 +<fs x-large><fc #008000>Les réponses sont insérées en VERT </fc></fs> 
 +</showif> 
 + 
 +===== 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 : 
 <code sql> <code sql>
-select * from BigAnnuaireSimple  +explain plan for 
-where ...+    select * from Annuaire; 
 +@p3
 </code> </code>
 +
 <code sql> <code sql>
-select * from BigAnnuaire  +explain plan for 
-where ...+    select * from BigAnnuaire; 
 +@p3
 </code> </code>
-Voici les requêtes utilisées dans les exercices suivants : 
-^  Nom  ^ Requête  SQL  ^  égalité  ^  inégalité  ^  intervalle  ^ 
-|  R1  |<code sql>select * from ...; -- sans WHERE </code>|  non  |  non  |  non  |  
-|  AgeEgal  |<code sql>... where age = 18;</code>  |     |     |    |  
-|  AgeInf  |<code sql>... where age < 25;  </code>|     |     |    |  
-|  AgeSup  |<code sql>... where age > 18;  </code>|     |     |    |  
-|  AgeEntre  |<code sql>... where age between 18 and 25;  </code>|     |     |    |  
-|  CodeEgal  |<code sql>... where cp = 75000;  </code>|     |     |    |  
-|  CodeInf  |<code sql>... where cp < 25000;  </code>|     |     |    |  
-|  CodeSup  |<code sql>... where cp > 75000;  </code>|     |     |    |  
-|  CodeEntre  |<code sql>... where cp between 15000 and 25000;  </code>|     |     |    |  
-|Age et Code postal :    | |     |     |    |  
-|  AgeEgalCodeEgal  |<code sql>... where age = 18 and cp = 75000;  </code>|     |     |    |  
-|  AgeEgalCodeInf  |<code sql>... where age = 18 and cp < 25000;  </code>|     |     |    |  
-|  AgeInfCodeEgal  |<code sql>... where age < 25 and cp = 75000;  </code>|     |     |    |  
-|  AgeInfCodeInf  |<code sql>... where age < 25 and cp < 25000;  </code>|     |     |    |  
-|  AgeInfCodeEntre  |<code sql>... where age < 25 and (cp between 15000 and 25000);  </code>|     |     |    |  
-|  AgeEntreCodeInf  |<code sql>... where (age between 18 and 25)  and cp < 25000;  </code>|     |     |    |  
-|Age puis dénombrement :  |  |     |     |    |  
-|  AgeInfCompte  |<code sql>select count(*) from ... where age < 60; </code>|     |     |    |  
  
-**Rmq**: la dernière requête AgeInfCompte est une sélection suivie d'une agrégation (avec le count). +Pour BigAnnuaire, afficher le nombre de valeurs distinctes de chaque attribut;
-Comprendre chaque requête et passer à l'exercice suivant.+
  
-===== Exercice 2. Plan sans index =====+<code sql> 
 +explain plan for 
 +    select distinct nom from BigAnnuaire; 
 +@p3 
 +</code>
  
-On s'intéresse à la durée d'une requête afin d'observer que son exécution est plus ou moins rapide. L'objectif est de comprendre que la durée dépend, entre autres, de la taille des données et de la méthode d'accès aux données. La durée estimée par l'optimiseur d'Oracle nous sert de mesure. C'est une bonne approximation de la durée réelle d'exécution de la requête.  
-Pour afficher la durée d'une requête, vous ajouterez toujours cette ligne juste avant la requête. 
 <code sql> <code sql>
-set autotrace trace explain+explain plan for 
 +   select distinct prenom from BigAnnuaire; 
 +@p3
 </code> </code>
-La durée estimée s'affiche dans la colonne **Time** du tableau représentant le plan d'exécution de la requête. 
  
 +et ainsi de suite pour les attributs âge, cp, tel et profil.
  
-Question/réponse : Quelle est la durée de la requête R1 affichant toutes les données de BigAnnuaireSimple ?+<showif isloggedin> 
 +<fc #008000>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. 
 +</fc> 
 + 
 +</showif> 
 + 
 + 
 + 
 +===== 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)** : 
 <code sql> <code sql>
-set autotrace trace explain +explain plan for 
-select * from BigAnnuaireSimple; +    select a.nom, a.prenom 
-</code>+    from BigAnnuaire a 
 +    where a.age = 18; 
 +@p3 
 +</code>     
 + 
 +<showif isloggedin> 
 +**<fc #008000>REPONSE</fc>** 
  
-La durée de totale la requête est la valeur de la **première** ligne de la colonne **Time**. Les valeurs des lignes suivantes sont des sous-totaux. 
 <code> <code>
---------------------------------------------------------------------------------------- +----------------------------------------------------------- 
-| Id  | Operation   | Name       | Rows  | Bytes | Cost (%CPU)| Time     | +| Id  | Operation     | Name   | Rows  | 
---------------------------------------------------------------------------------------- +----------------------------------------------------------- 
-|   0 | SELECT STATEMENT  |       |   220K|   846M| 59767 (1)| 00:11:58 | +|   0 | SELECT STATEMENT     |   |  2200 | 
-|   1 |  TABLE ACCESS FULL| BIGANNUAIRESIMPLE |   220K|   846M| 59767 (1)| 00:11:58 | +|   1 |  TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE |  2200 | 
----------------------------------------------------------------------------------------+|*  2 |   INDEX RANGE SCAN     | INDEXAGE   |  2200 | 
 +----------------------------------------------------------- 
 +Predicate Information  
 +---------------------- 
 +    2 - access(A.AGE=18)
 </code> </code>
  
-Réponse: la durée est de 11 minutes et 58 secondes. +  * 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.
-Rmq, une autre façon de procéder serait de chronométrer la durée réelle d'exécution d'une requête, mais cela n'est pas "pratique" pour un TME avec des requêtes longues pouvant durer plusieurs minutes.+
  
-=== Accès aux données : parcours séquentiel=== +</showif>
-Aucun index n'est défini pour la table BigAnnuaireSimple. Donc la seule façon d'accéder aux données est de lire entièrement la table. L'opération de parcours séquentiel s'appelle //TABLE ACCESS FULL//.+
  
-**Questions** : Pour la requête nommée AgeEgal, observer le plan de la requête et repérer le parcours séquentiel de la table BigAnnuaireSimple. 
  
 +**Question b)** : 
 <code sql> <code sql>
-    set autotrace trace explain +explain plan for 
-    select * from BigAnnuaireSimple where age=18;+    select a.nom, a.prenom 
 +    from BigAnnuaire a 
 +    where a.age between 20 and 29; 
 +@p3
 </code>     </code>    
  
-  *  Quelle est sa durée ? Est-ce la même durée que R1 qui n'a pas de sélection? Pourquoi ? 
-  *  Expliquer ce que fait l'opération **filter(age=18)** attachée à l'opération numéro 1 (Id=1) du plan. 
-  *  Mêmes questions idem pour les autres requêtes sur la table **BigAnnuaireSimple** 
  
-Remarque: **Problème d'affichage trop long**. si par erreur vous avez lancé l'exécution d'une requête en oubliant le mode //set autotrace trace explain// 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+<showif isloggedin> 
 +**<fc #008000>REPONSE</fc>** 
  
-===== Exercice 3. Plan avec index =====+<code> 
 +----------------------------------------------------------- 
 +| 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)
  
-On considère la table BigAnnuaire identique à BigAnnuaireSimple mais ayant deux index +Column Projection 
-  * IndexAge est un index sur BigAnnuaire(age) +----------------- 
-  * IndexCp est un index  sur BigAnnuaire(cp).+   1 - "A"."NOM"[VARCHAR2,30], "A"."PRENOM"[VARCHAR2,30] 
 +   2 - "A".ROWID[ROWID,10] 
 +</code> 
 +</showif>
  
-L'accès aux données peut se faire par index.  
  
-Question/réponse : quelle est la méthode d'accès utilisée pour la requête AgeEgal ? 
  
 +**Question c)** : 
 <code sql> <code sql>
-select * from BigAnnuaire where age = 18;+explain plan for 
 +   select a.nom, a.prenom 
 +   from BigAnnuaire a 
 +   where a.age < 70 and (a.cp = 93000 or a.cp = 75000); 
 +@p3 
 +</code>     
 + 
 +<showif isloggedin> 
 +**<fc #008000>REPONSE</fc>**  
 + 
 + 
 +<fc #008000> 
 +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. 
 +</fc> 
 + 
 +<code> 
 +------------------------------------------------------------ 
 +| 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)
 </code> </code>
 +</showif>
  
 +
 +
 +**Question d)** : 
 +<code sql>
 +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
 +</code>    
 +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).
 +
 +<showif isloggedin>
 +**<fc #008000>
 +REPONSE:
 +Ne PAS trop passer de temps à expliquer le détail de BITMAP conversion.
 +Dire que c'est simplement un intersection de ROWID.
 +</fc>** 
 <code> <code>
-------------------------------------------------------------------------------------------- +---------------------------------------------------------------- 
-| Id  | Operation     | Name   | Rows  | Bytes | Cost (%CPU)| Time   | +| Id  | Operation  | Name        | Rows  | 
-------------------------------------------------------------------------------------------- +---------------------------------------------------------------- 
-|   0 | SELECT STATEMENT     |   |  2200 |  8666K|  2206   (1)| 00:00:27 | +|   0 | SELECT STATEMENT  |        |     1 | 
-|   1 |  TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE |  2200 |  8666K|  2206   (1)| 00:00:27 | +|*  1 |  TABLE ACCESS BY INDEX ROWID  | BIGANNUAIRE |     1 | 
-|*  2 |   INDEX RANGE SCAN     | INDEXAGE   |  2200 |   | 5   (0)| 00:00:01 | +|   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) 
 +</code> 
 +</showif>
  
-Predicate Information (identified by operation id): +===== Exercice 2. Sélection AVEC OU SANS index =====
----------------------------------------------------+
  
-   2 - access("AGE"=18)+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. 
 + 
 +<code sql> 
 +explain plan for 
 +    select a.nom, a.prenom 
 +    from BigAnnuaire a 
 +    where a.age <= 10; 
 +@p4 
 +</code>     
 + 
 +<showif isloggedin> 
 +**<fc #008000>REPONSE</fc>**  
 + 
 +<code> 
 +-------------------------------------------------------------------------------- 
 +| 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)
 </code> </code>
 +</showif>
  
-Réponse:  
-  * Le traitement commence par 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. L'annotation **access**("AGE"=18) attachée à l'opération 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 a)** : Pour chacune des 6 requêtes suivantes:  AgeEgal, AgeInf, AgeEntre, CodeEgal, CodeInf, CodeSup.  +Compléter le tableau en indiquant la cardinalité, le coût et si l'index IndexAge est utilisé ou non.
-Quelle est la durée de la requête ?  Est ce que l'accès par index est plus rapide qu'un parcours séquentiel ?+
  
 +^ Prédicat ^ Rows ^ Index utilisé ^ Cout ^
 +|  age < = 10  |   |   oui  |  |
 +|  age < = 20  |   |        |  |
 +|  age < = 30  |   |        |  |
 +|  age < = 40  |   |        |  |
 +|  age < = 60  |   |        |  |
 +|  age < = 100  |   |        |  |
  
-**Question b)** :  Etudier le plan de la requête AgeEgalCodeEgal. Combien d'index sont utilisés ? Dessiner l'**arbre** des opérations avec l'opération racine (Id=0) en haut et les opérations feuilles (Id=5) et (Id=7) en bas. Aidez-vous de l'indentation des noms d'opération (dans la colonne //Operation//) pour connaitre l'opération parent d'une opération. Expliquer brièvement comment on peut obtenir le résultat de la requête en utilisant deux index. 
  
 +<showif isloggedin>
 +**<fc #008000>REPONSE</fc>** 
 +^ 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 c)** : Tester plusieurs variantes de la requête de AgeInf avec successivement les prédicats age<10 puis age<20 puis age<30. 
-Est-ce que la durée augmente? La durée est-elle proportionnelle au nombre de nuplets du résultat (affiché dans la colonne //Rows//) ? Compléter le tableau : 
-^Prédicat ^  Rows : Nbre de nuplets  ^  Time : Durée  ^  Accès  ^ 
-|age<10|  |    |  Index range scan  | 
-|age<20|  |    | 
-|age<30|  |    | 
-Si on suppose que la durée de AgeInf est proportionnelle au nombre de nuplets du résultat, quelle serait la durée pour //age<80// ? 
  
-**Question d)** : Observer que pour ''age < 80'',  la requête AgeInf est exécutée sans utiliser l'index mais en faisant un parcours séquentiel. Bien que l'index existe, il n'est pas utilisé. Quelle est la durée de cette requête ? Est-ce que la durée est plus courte que si l'index avait été utilisé ?+</showif>
  
 +**Question b)** : 
 +Pour quel prédicat Oracle préfère-t-il évaluer la requête sans utiliser l'index IndexAge ? Pourquoi ?
  
-===== Exercice 4. Coût de l'accès séquentiel aux données ===== +<showif isloggedin> 
-L'accès par index n'étant pas toujours plus rapide que le parcours séquentiel, il est important de choisir le plus rapide des deux modes d'accès.  +**<fc #008000>REPONSE</fc>**  
-Oracle estime le coût de plusieurs possibilités d'accès afin de choisir l'accès de moindre coût.+A partir de age < = 40 lire toute la table coute moins cher que de lire l'index.
  
-Le **coût d'un accès séquentiel** est proportionnel au nombre de pages (i.e., de blocs) à lire. On a la formule+</showif>
  
-coût(lecture sequentielle d'une table) = C * page(table) avec C étant une constante. En TD on suppose pour simplifier que C=1 mais en TME on veut connaitre la valeur de C. 
  
-Afficher le nombre de pages de la table Annuaire :+**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. 
 <code sql> <code sql>
-set autotrace off +explain plan for 
-column table_name format A20 +    select a.nom, a.prenom 
-select table_name, blocks, num_rows from user_tables;+    from BigAnnuaire a 
 +    where a.cp BETWEEN 50000 AND ....; 
 +@p4
 </code> </code>
  
-Afficher le coût d'une lecture séquentielle :+<showif isloggedin> 
 +**<fc #008000>REPONSE</fc>**  
 <code sql> <code sql>
-set autotrace trace explain +explain plan for 
-select * from Annuaire;+    select a.nom, a.prenom 
 +    from BigAnnuaire a 
 +    where a.cp BETWEEN 50000 AND 80000; 
 +@p4
 </code> </code>
  
-En déduire la valeur de C.+<code> 
 +-------------------------------------------------------------------------------- 
 +| 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)| 
 +-------------------------------------------------------------------------------- 
 +</code>
  
-On veut vérifier que C est constante. Pour cela calculer C à partir à partir du coût d'accès à la table BigAnnuaire. 
-Le nombre de pages de BigAnnuaire est : 
 <code sql> <code sql>
-set autotrace off +explain plan for 
-select table_name, blocks, num_rows from all_tables +    select a.nom, a.prenom 
-where table_name = 'BIGANNUAIRE';+    from BigAnnuaire a 
 +    where a.cp BETWEEN 50000 AND 90000; 
 +@p4
 </code> </code>
  
-Le coût d'une lecture séquentielle est : +<code> 
-<code sql> +---------------------------------------------------------------------- 
-set autotrace trace explain +| Id  | Operation   | Name | Rows | Bytes | Cost (%CPU)| 
-select * from BigAnnuaire;+---------------------------------------------------------------------- 
 +|   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)
 </code> </code>
  
-Est-ce que vous obtenez la même valeur pour C? 
  
-===== Exercice 5. Coût d'une sélection pour le choix d'un index  ===== 
-On étudie la stratégie reposant sur l'estimation du **<fc #ff0000>coût</fc>** d'une requête. 
  
-On veut observer pour quel prédicat de sélection les index sont utilisés. 
-Si un index est utilisé pour évaluer la sélection  AGE=18, la rubrique //Predicate Information//  
-du plan affiché par Oracle contient une ligne <fc #ff0000>access("AGE"=18).</fc> 
-Si, en revanche, la sélection AGE=18 était évaluée au vol, cette même rubrique contiendrait la ligne <fc #ff0000>filter("AGE"=18)</fc>. 
-Il est ainsi très simple de repérer les prédicats pour lesquels un index est utilisé. 
  
  
-**Question a)** Quelles sont les requêtes pour lesquelles aucun index n'est utilisé bien que des index soient définis sur les prédicats de la requête?  Répondre en expliquant le lien avec la sélectivité du prédicat de la requête. 
  
  
-**Question b)** Pour la requête qui utilise les deux index INDEXAGE et INDEXCP:+</showif> 
 + 
 + 
 + 
 + 
 +===== 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'' 
 + 
 +=== 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.
 <code sql> <code sql>
-set autotrace trace explain +explain plan for 
-select * from BigAnnuaire where age < 25 and cp < 12000;+   select /*+ index(a IndexAge) */  a.nom, a.prenom  
 +   from BigAnnuaire a where a.age < 7; 
 +@p4
 </code> </code>
  
-  * Remplacer la valeur <fc #800080>25</fc> une valeur X pour que seul l'index INDEXCP soit utilisé. Donner un exemple de X ?  +  * La directive ''no_index()'' empêche l'usage d'un index:
-  * A partir de la requête initiale //age < <fc #ff0000>25</fc> and cp < 12000//. Remplacer la valeur <fc #800080>12000</fc> une valeur Y pour que seul l'index INDEXAGE soit utilisé. Donner un exemple de Y ?  +
-  * Pour les valeurs X et Y que vous avez proposées, est-ce que la requête //age < X and cp < Y// utilise des index ? Observer les durées et expliquer.+
  
- 
-**Question c)** On étudie le cas d'une requête ayant un prédicat composé de plusieurs prédicats simples et peu sélectifs. 
-On se pose la question: Est-ce que l'accès par index est intéressant pour ce type de requête ? 
-Soit la requête AgeInfCodeInf composée de deux prédicats de sélection sur age et sur cp: 
 <code sql> <code sql>
-select * from BigAnnuaire where age < X and cp < Y;+explain plan for 
 +   select /*+  no_index(a IndexAge) */  a.nom, a.prenom 
 +   from BigAnnuaire a where a.age < 7; 
 +@p4
 </code> </code>
-Proposer des valeurs pour les nombres X et Y telles que chaque prédicat pris individuellement ne soit pas assez sélectif pour être évalué avec un index, mais que la conjonction des deux prédicats soit assez sélective pour utiliser un index. Autrement dit: +Vérifier que le plan de coût minimal est bien celui choisi sans aucune directive. 
-  * la requête //select * from BigAnnuaire where age < X;// est évaluée sans index.  +
-  * La requête //select * from BigAnnuaire where code < Y;// est évaluée sans index. +
-  * La requête AgeInfCodeInf utilise les deux index.+
  
-**Question d)** Pour les requêtes AgeEgal et AgeInf, on veut expliquer comment le SGBD détermine le coût des opérations //INDEX RANGE SCAN// et //TABLE ACCESS BY INDEX ROWID//. +**Question b)** : Idem pour la requête ''age > 19'' qui est peu sélective. 
-Préciser la taille des index :+ 
 +**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 :
 <code sql> <code sql>
-set autotrace off +explain plan for 
-column table_name A10 +    select /*+ index(a IndexAge) no_index(a IndexCp)  */  a.nom, a.prenom  
-column index_name A10 +    from BigAnnuaire a where a.age = 18 and a.cp = 75000; 
-select table_name, index_name, blevel, distinct_keys, leaf_blocks, +@p4
-avg_leaf_blocks_per_key, avg_data_blocks_per_key +
-from all_indexes +
-where table_name = 'BIGANNUAIRE'; +
-set autotrace trace explain+
 </code> </code>
-Expliquer (cf. le paragraphe Documentation ci-dessous) ce que représentent leaf_blocks et avg_leaf_blocks_per_key. 
  
-Combien de pages faut-il lire pour obtenir les ROWID des Personnes ayant 18 ans ? Quel est le coût correspondant ? 
  
-Combien de pages faut-il lire pour obtenir les nuplets des Personnes ayant 18 ans à partir de leur ROWID? Quel est le coût correspondant 
-Mêmes questions pour les Personnes ayant ''age<30''. 
  
 +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(a IndexCP IndexAge)'' ou ''index_join'' pour cela [[https://docs.oracle.com/cd/B10501_01/server.920/a96533/hintsref.htm#5215]]
  
-===== Exercice 6. Comparaison de plans d'exécutions équivalents ===== 
  
-Le but de cet exercice est de montrer que le plan choisi par l'optimiseur est le plus rapide. 
-Pour ce faire, il vous sera demandé d'énumérer, pour une requête donnée, tous les plans équivalents et d'estimer le cout de chacun d'entre eux. 
-Énumérer les plans équivalents revient à considérer toutes les combinaisons entre utiliser des index ou pas. 
-Dans Oracle, cela revient à utiliser des directives qui sont expliquées ci-dessous. 
  
-=== Directive pour forcer/empêcher l'usage d'un index ===+===== Exercice 4 (facultatif). Requête de jointure utilisant un index =====
  
-  * La directive **index()** force l'usage d'un index. Exple pour AgeSup+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)** : 
 <code sql> <code sql>
-   select /*+ index(BigAnnuaire IndexAge) */  *   +explain plan for 
-   from BigAnnuaire where age > 18;+    select a.nom, a.prenom, v.ville 
 +    from Annuaire a, Ville v 
 +    where a.cp = v.cp 
 +    and a.age=18; 
 +@p3 
 +</code>     
 + 
 +<showif isloggedin> 
 +**<fc #008000>REPONSE</fc>**  
 + 
 +<code> 
 +--------------------------------------------------------- 
 +| 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)
 </code> </code>
 +</showif>
 +
 +
  
-  * La directive **no_index()** empêche l'usage d'un index. Exple pour AgeEgal+**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 ?
 <code sql> <code sql>
-   select /*+  no_index(BigAnnuaire IndexAge) */  *  +explain plan for 
-   from BigAnnuaire where age = 18;+    select a.nom, a.prenom, v.ville 
 +    from BigAnnuaire a, Ville v 
 +    where a.cp = v.cp 
 +    and a.age=18; 
 +@p3 
 +</code>     
 + 
 +<showif isloggedin> 
 +**<fc #008000>REPONSE</fc>**  
 + 
 +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. 
 + 
 +<code> 
 +------------------------------------------------------------ 
 +| 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)
 </code> </code>
 +</showif>
 +
 +
  
-  * Une directive **index()** et **no_index()** peuvent être spécifiées dans la même requête pour forcer un index et empêcher un autre index +**Question c)** : 
 <code sql> <code sql>
-    select /*+ index(BigAnnuaire IndexAge) no_index(BigAnnuaire IndexCp)  */  *  +explain plan for 
-    from BigAnnuaire where age = 18 and cp = 75000;+    select a.nom, a.prenom, v.ville 
 +    from BigAnnuaire a, Ville v 
 +    where a.cp = v.cp 
 +    and v.population >= 985000; 
 +@p3 
 +</code>     
 + 
 +<showif isloggedin> 
 +**<fc #008000> 
 +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 
 + 
 +</fc>**  
 + 
 +<code> 
 +------------------------------------------------------------ 
 +| 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)
 </code> </code>
  
-  * La directive **index_combine()** force à utiliser deux index: +</showif> 
 + 
 + 
 + 
 + 
 + 
 +===== 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
 <code sql> <code sql>
-    select /*+ index_combine(BigAnnuaire IndexAge IndexCp)  */  *  +explain plan for 
-    from BigAnnuaire where age = 18 and cp < 75000;+    select age, count(*) 
 +    from BigAnnuaire a 
 +    group by age; 
 +@p3 
 +</code>     
 + 
 +<showif isloggedin> 
 +**<fc #008000>REPONSE</fc>**  
 + 
 +pas d'acces à la table pour cette requete. Seulement un parcours des feuilles de l'index. 
 + 
 +<code> 
 +| Id  | Operation       | Name | Rows  | 
 +-------------------------------------------------- 
 +|   0 | SELECT STATEMENT      | |   100 | 
 +|   1 |  HASH GROUP BY       | |   100 | 
 +|   2 |   INDEX FAST FULL SCAN| INDEXAGE |   220K| 
 +--------------------------------------------------
 </code> </code>
 +</showif>
  
 +b) Requêtes avec group by having
  
 +<code sql>
 +explain plan for
 +    select age, count(*)
 +    from BigAnnuaire a
 +    group by age
 +    having count(*) > 200;
 +@p3
 +</code>    
  
-** Rmq :** Spécifier plusieurs directives index() dans une même requête ne force **pas** à utiliser plusieurs index simultanément, mais force à utiliser le meilleur d'entre eux.  Voir la doc [[https://docs.oracle.com/cd/B10501_01/server.920/a96533/hintsref.htm#5215]]+<showif isloggedin> 
 +**<fc #008000>REPONSE</fc>** 
  
-**Question a)** : Pour la requête AgeEgalCodeEgal ci-dessus proposer 4 plans **équivalents** (i.e., IndexAge seul, IndexCP seul, aucun index, les deux index) pour évaluer cette requête et donner leur coût. Est-ce que le plan avec le plus petit coût est bien celui obtenu sans aucune directive ?+Operation nommée FILTER pour évaluer le having.
  
 +<code>
 +| 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)
 +</code>
 +</showif>
  
-**Question b)** : Mêmes questions pour les requêtes AgeInfCodeInf et AgeInfCodeEntre.+c) Requete min max
  
 +<code sql>
 +explain plan for
 +    select min(cp), max(cp)
 +    from BigAnnuaire a;
 +@p3
  
 +</code>    
  
 +<showif isloggedin>
 +**<fc #008000>REPONSE</fc>** 
  
 +Sort aggregate: trier et ne garder que les valeurs min et max
  
 +<code>
 +| Id  | Operation       | Name | Rows |
 +-------------------------------------------------
 +|   0 | SELECT STATEMENT      | |     1 |
 +|   1 |  SORT AGGREGATE       | |     1 |
 +|   2 |   INDEX FAST FULL SCAN| INDEXCP |   220K|
 +-------------------------------------------------
 +Column Projection
 +-----------------
 +    2 - "CP"
 +</code>
 +</showif>
  
 +d) Requête avec **not in**
  
-===== Exercice 7. Index  couvrant une requête ===== +<code sql> 
-Vérifier qu'il est possible d'évaluer R13 en lisant seulement l'index, sans accéder aux nuplets d'Annuaire. Expliquer pourquoi c'est possible. Proposer d'autres requêtes pouvant être traitées sans accéder aux nuplets : +explain plan for 
-  * avec un distinct +    select a.nom, a.prenom 
-  * avec un group by et une agrégation +    from BigAnnuaire a 
-  * avec un order by +    where a.prenom not in ( select b.prenom 
-  * avec les 2 attributs age et cp dans la clause select +                        from BigAnnuaire b 
 + where b.age<=7); 
 +@p3 
 +</code>    
  
 +<showif isloggedin>
 +**<fc #008000>REPONSE</fc>** 
  
 +OPeration n°1: ANTI join: si la condition a.prenom=b.prenom est fausse alors le nuplet est dans le résultat.
  
 +<code>
 +| 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
 +</code>
 +</showif>
  
-===== Exercice 8 (facultatif). Durée d'une requête ===== +e) Requête avec not exists
-On veut observer l'effet d'un index sur la durée d'exécution d'une requête de sélection. La durée est la valeur du champ //Ecoulé// qui s'affiche après avoir saisi la commande : +
-''set timing on''+
  
-On définit la requête M1(A) en fonction d'une valeur A de l'âge : 
 <code sql> <code sql>
-    select min(cp)  +explain plan for 
-    from BigAnnuaire +    select a.nom, a.prenom 
-    where age <= A;+    from BigAnnuaire a 
 +    where not exists ( select * 
 +                       from BigAnnuaire b 
 +        where b.prenom = a.prenom 
 +        and b.age < a.age); 
 +@p3 
 +</code>     
 + 
 +<showif isloggedin> 
 +**<fc #008000>REPONSE</fc>**  
 + 
 +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. 
 + 
 +<code> 
 +---------------------------------------------------- 
 +| 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 
 + 
 </code> </code>
-1. Temps total écoulé. Pour différentes valeurs de l'âge, chronométrer la requête M1. Observer que la durée dépend de A.+</showif>
  
-2. Temps de calcul et temps de transfert du résultat. Mesurer la durée d'exécution d'une requête dans le SGBD, sans inclure le temps pour transférer le résultat dans l'application cliente sqlplus. +f) Requête avec minus : les code spostaux des villes qui n'ont pas de centenaire.
-Lire le fichier //chrono.sql// (se trouvant dans votre dossier tmeIndex) et le comprendre. Proposer une méthode pour répondre aux questions : +
-  - Lorsque la sélection est suivie d'une projection (pour évaluer la clause select), quelle est la durée de la projection? +
-  - Quelle est la durée pour transférer 1 nuplet de l'Annuaire dans l'application sqlplus ? +
  
 +<code sql>
 +explain plan for
 +  select cp
 +  from BigAnnuaire a
 +  minus
 +   select cp
 +   from BigAnnuaire b
 +   where b.age>=100;
 +@p3
 +</code>    
  
 +<showif isloggedin>
 +**<fc #008000>REPONSE</fc>** 
  
-===== Questions fréquentes =====+2 index (indexage et indexcp) sont utilisés pour produire la liste des cp où résident des centenaires.
  
-- 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.+L'index indexcp est aussi utilisé pour obtenir la liste de tous les cp.
  
-- 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.+<code> 
 +| 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) 
 +</code> 
 +</showif>
  
-    select /*+ directive */ * +g) requête avec where age >= ALL (...)
-    from  +
-    where +
-      +
-S'il y a une erreur de syntaxe dans une directive Oracle ignore la directive **SANS** vous avertir.+
  
 +<code sql>
 +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
 +</code>    
  
-===== Documentation =====+<showif isloggedin> 
 +**<fc #008000>REPONSE</fc>**  
 + 
 +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). 
 + 
 +<code> 
 +| 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) 
 +</code> 
 +</showif> 
 + 
 + 
 +h) Requete avec UNION, avec UNION ALL, avec une division, ... 
 + 
 + 
 +===== Exercice 6: Documentation =====
  
 Vous pouvez interroger le dictionnaire du SGBD pour obtenir des informations détaillées sur vos tables et vos index. 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 [[http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1069.htm#i1578369|user_indexes]] Description d'un index : profondeur de l'arbre, nombre de valeurs indexées. Interroger [[http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1069.htm#i1578369|user_indexes]]
-<code sql> +<code sql>   
-   set autotrace off +   select index_name as nom, blevel as profondeur, distinct_keys as nb_valeurs, leaf_blocks as pages_de_Rowids 
-   select index_name, blevel, distinct_keys, leaf_blocks from user_indexes;+   from user_indexes;
 </code> </code>
  
 Description d'une table : cardinalité, taille totale. Interroger [[http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#REFRN20286|user_tables]] Description d'une table : cardinalité, taille totale. Interroger [[http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#REFRN20286|user_tables]]
-<code sql> +<code sql>  
-    set autotrace off +    column nom format A20 
-    select table_name, num_rows, blocks from user_tables;+    select table_name as nom, num_rows as cardinalite, blocks as nb_pages  
 +    from user_tables; 
 +     
 +    select table_name as nom, num_rows as cardinalite, blocks as nb_pages  
 +    from all_tables 
 +    where table_name = 'BIGANNUAIRE';
 </code> </code>
-     +
 Description d'un attribut : valeur min, max, nb de valeurs disctinctes.  Interroger [[http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2093.htm#I1020276|user_tab_cols]] Description d'un attribut : valeur min, max, nb de valeurs disctinctes.  Interroger [[http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2093.htm#I1020276|user_tab_cols]]
-<code sql> +<code sql>  
-    set autotrace off+
     column table_name format A20     column table_name format A20
     column column_name format A20     column column_name format A20
Ligne 375: Ligne 844:
 etc... de nombreuses autres informations sont disponibles tq par exemple l'histogramme représentant la distribution des valeurs d'un attribut. Voir la  [[http://docs.oracle.com/cd/B19306_01/nav/catalog_views.htm#index-USE|liste des vues]] que vous pouvez interroger. etc... de nombreuses autres informations sont disponibles tq par exemple l'histogramme représentant la distribution des valeurs d'un attribut. Voir la  [[http://docs.oracle.com/cd/B19306_01/nav/catalog_views.htm#index-USE|liste des vues]] que vous pouvez interroger.
  
 +
 +
 +===== Questions fréquentes =====
 +
 +- 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.
 +
 +- 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.
 +
 +<code sql>
 +    select /*+ directive */ *
 +    from 
 +    where
 +</code>
 +     
 +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. 
  
  
Ligne 382: Ligne 871:
 ===== Divers ===== ===== Divers =====
  
-Aller vers  [[site:enseignement:master:bdr:start | BDR]]+Aller vers  [[site:enseignement:master:bdr:start]]
  
  
site/enseignement/master/bdr/tmeindex.1485344219.txt.gz · Dernière modification : de hubert