site:enseignement:master:bdr:tmeindex
Différences
Ci-dessous, les différences entre deux révisions de la page.
Les deux révisions précédentesRévision précédenteProchaine révision | Révision précédente | ||
site:enseignement:master:bdr:tmeindex [25/01/2017 12:23] – [Exercice 6. Comparaison de plans d'exécutions équivalents] hubert | site:enseignement:master:bdr:tmeindex [20/02/2019 15:32] (Version actuelle) – hubert | ||
---|---|---|---|
Ligne 1: | Ligne 1: | ||
{{indexmenu_n> | {{indexmenu_n> | ||
- | ====== TME Index====== | ||
+ | /** TODO | ||
+ | * remplacer | ||
+ | **/ | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ====== TME Index ====== | ||
L' | L' | ||
- | * 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: | ||
^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/ | | **Alt-x** my/ | ||
- | | aller sur la ligne contenant @annuaire et faire Ctrl-C Ctrl-C | définir la table Annuaire | + | | aller sur la ligne contenant @annuaire et faire Ctrl-C Ctrl-C | définir la table Annuaire et un synonyme |
- | La table **Annuaire**(nom, | ||
- | * 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</ | + | Dans ce TME on interroge les deux tables Annuaire et BigAnnuaire. |
+ | ^Table^Nbre de tuples^Taille | ||
+ | |Annuaire|2000| 3846 | | ||
+ | |BigAnnuaire |220 000| 3846 | | ||
- | La table **<fc # | + | Les schémas des deux tables sont indentiques et contiennent les attributs suivants : |
- | Lire la section //Questions fréquentes// | + | ^Attribut^Valeurs distinctes^Domaine^Type^Index^ |
+ | |âge|100|[1-100]| NUMBER(3)|IndexAge| | ||
+ | |cp|1000|[1000, | ||
+ | |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' | ||
+ | /* | ||
+ | Dans le poly de TD, **<fc # | ||
+ | */ | ||
+ | |||
+ | Lire la section [[# | ||
- | ===== 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' | 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' | ||
- | Les requêtes seront posées sur la table <fc #ff0000>**BigAnnuaireSimple ou BigAnnuaire.**</ | + | |
+ | Le SGBD transforme une requête en un //plan// avant de l' | ||
+ | Pour **afficher** le plan d'une requête, commencer chaque requête par | ||
+ | <code ascii> | ||
+ | | ||
+ | </ | ||
+ | puis terminer chaque requête par | ||
+ | @p3 | ||
+ | |||
+ | |||
+ | Remarque1 : pour régler l' | ||
+ | |||
+ | |||
+ | Remarque2 : **Problème d' | ||
+ | |||
+ | <showif isloggedin> | ||
+ | <fs x-large>< | ||
+ | </ | ||
+ | |||
+ | ===== Exercice préliminaire. Statistiques sur les tables ===== | ||
+ | |||
+ | Pour déterminer le plan d'une requête, le SGBD s' | ||
+ | Les statistiques sont, entre autres, la cardinalité d'une table et le nombre de valeurs distinctes d'un attribut. | ||
+ | |||
+ | Observer les statistiques qu' | ||
+ | |||
+ | La cardinalité d'une table : | ||
<code sql> | <code sql> | ||
- | select * from BigAnnuaireSimple | + | explain plan for |
- | where ... | + | |
+ | @p3 | ||
</ | </ | ||
+ | |||
<code sql> | <code sql> | ||
- | select * from BigAnnuaire | + | explain plan for |
- | where ... | + | |
+ | @p3 | ||
</ | </ | ||
- | Voici les requêtes utilisées dans les exercices suivants : | ||
- | ^ Nom ^ Requête | ||
- | | R1 |<code sql> | ||
- | | AgeEgal | ||
- | | AgeInf | ||
- | | AgeSup | ||
- | | AgeEntre | ||
- | | CodeEgal | ||
- | | CodeInf | ||
- | | CodeSup | ||
- | | CodeEntre | ||
- | |Age et Code postal : | | | ||
- | | AgeEgalCodeEgal | ||
- | | AgeEgalCodeInf | ||
- | | AgeInfCodeEgal | ||
- | | AgeInfCodeInf | ||
- | | AgeInfCodeEntre | ||
- | | AgeEntreCodeInf | ||
- | |Age puis dénombrement : | | | ||
- | | AgeInfCompte | ||
- | **Rmq**: la dernière requête AgeInfCompte est une sélection suivie d'une agrégation (avec le count). | + | Pour BigAnnuaire, |
- | Comprendre | + | |
- | ===== Exercice 2. Plan sans index ===== | + | <code sql> |
+ | explain plan for | ||
+ | select distinct nom from BigAnnuaire; | ||
+ | @p3 | ||
+ | </ | ||
- | On s' | ||
- | 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 |
+ | | ||
+ | @p3 | ||
</ | </ | ||
- | La durée estimée s' | ||
+ | et ainsi de suite pour les attributs âge, cp, tel et profil. | ||
- | Question/ | + | <showif isloggedin> |
+ | <fc # | ||
+ | Le fichier // | ||
+ | </ | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Exercice 1. Requête | ||
+ | Expliquer le plan des requêtes suivantes. Détailler chaque étape de l' | ||
+ | |||
+ | |||
+ | **Question a)** : | ||
<code sql> | <code sql> | ||
- | set autotrace trace explain | + | explain |
- | select | + | select |
- | </ | + | |
+ | where a.age = 18; | ||
+ | @p3 | ||
+ | </ | ||
+ | |||
+ | <showif isloggedin> | ||
+ | **<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. | ||
< | < | ||
- | --------------------------------------------------------------------------------------- | + | ----------------------------------------------------------- |
- | | Id | Operation | + | | Id | Operation |
- | --------------------------------------------------------------------------------------- | + | ----------------------------------------------------------- |
- | | 0 | SELECT STATEMENT | + | | 0 | SELECT STATEMENT |
- | | 1 | TABLE ACCESS | + | | 1 | TABLE ACCESS |
- | --------------------------------------------------------------------------------------- | + | |* 2 | INDEX RANGE SCAN |
+ | ----------------------------------------------------------- | ||
+ | Predicate Information | ||
+ | ---------------------- | ||
+ | 2 - access(A.AGE=18) | ||
</ | </ | ||
- | Réponse: la durée est de 11 minutes et 58 secondes. | + | * Evaluer l' |
- | + | * Puis le traitement se poursuit | |
- | 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 " | + | |
- | === 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' | + | |
- | **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 |
- | select | + | select |
+ | | ||
+ | | ||
+ | @p3 | ||
</ | </ | ||
- | * 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' | ||
- | * Mêmes questions idem pour les autres requêtes sur la table **BigAnnuaireSimple** | ||
- | Remarque: | + | <showif isloggedin> |
+ | **<fc # | ||
- | ===== Exercice 3. Plan avec index ===== | + | < |
+ | ----------------------------------------------------------- | ||
+ | | Id | Operation | ||
+ | ----------------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | ||
+ | | 1 | TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE | 24400 | | ||
+ | |* 2 | INDEX RANGE SCAN | INDEXAGE | ||
+ | ----------------------------------------------------------- | ||
+ | Predicate Information | ||
+ | --------------------- | ||
+ | 2 - access(A.AGE> | ||
- | 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 - " |
+ | 2 - " | ||
+ | </ | ||
+ | </ | ||
- | L' | ||
- | Question/ | ||
+ | **Question c)** : | ||
<code sql> | <code sql> | ||
- | select | + | explain plan for |
+ | select | ||
+ | from BigAnnuaire | ||
+ | where a.age < 70 and (a.cp = 93000 or a.cp = 75000); | ||
+ | @p3 | ||
+ | </ | ||
+ | |||
+ | <showif isloggedin> | ||
+ | **<fc # | ||
+ | |||
+ | |||
+ | <fc # | ||
+ | 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' | ||
+ | |||
+ | puis accès par rowid aux nuplets et filtrer age< | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | ------------------------------------------------------------ | ||
+ | | Id | Operation | ||
+ | ------------------------------------------------------------ | ||
+ | | 0 | SELECT STATEMENT | ||
+ | | 1 | INLIST ITERATOR | ||
+ | |* 2 | TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE | 307 | | ||
+ | |* 3 | INDEX RANGE SCAN | INDEXCP | ||
+ | ------------------------------------------------------------ | ||
+ | Predicate Information | ||
+ | --------------------- | ||
+ | 2 - filter(" | ||
+ | 3 - access(" | ||
</ | </ | ||
+ | </ | ||
+ | |||
+ | |||
+ | **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 ' | ||
+ | @p3 | ||
+ | </ | ||
+ | Remarque: Oracle utilise deux index pour l' | ||
+ | |||
+ | <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. | ||
+ | </ | ||
< | < | ||
- | ------------------------------------------------------------------------------------------- | + | ---------------------------------------------------------------- |
- | | Id | Operation | + | | Id | Operation | Name | Rows | |
- | ------------------------------------------------------------------------------------------- | + | ---------------------------------------------------------------- |
- | | 0 | SELECT STATEMENT | + | | 0 | SELECT STATEMENT | | |
- | | | + | |* |
- | |* | + | | 2 | |
- | ------------------------------------------------------------------------------------------- | + | | |
+ | | 4 | | ||
+ | |* | ||
+ | | 6 | BITMAP CONVERSION FROM ROWIDS| | ||
+ | |* 7 | INDEX RANGE SCAN | INDEXAGE | ||
+ | ---------------------------------------------------------------- | ||
+ | Predicate Information | ||
+ | --------------------- | ||
+ | | ||
+ | | ||
+ | | ||
+ | </ | ||
+ | </ | ||
- | Predicate Information (identified by operation id): | + | ===== Exercice 2. Sélection AVEC OU SANS index ===== |
- | --------------------------------------------------- | + | |
- | 2 - access(" | + | 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 '' | ||
+ | |||
+ | <code sql> | ||
+ | explain plan for | ||
+ | select a.nom, a.prenom | ||
+ | from BigAnnuaire a | ||
+ | where a.age <= 10; | ||
+ | @p4 | ||
+ | </ | ||
+ | |||
+ | <showif isloggedin> | ||
+ | **<fc # | ||
+ | |||
+ | < | ||
+ | -------------------------------------------------------------------------------- | ||
+ | | Id | Operation | ||
+ | -------------------------------------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | ||
+ | | 1 | TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE | 20000 | 351K| 20046 | ||
+ | |* 2 | INDEX RANGE SCAN | INDEXAGE | ||
+ | -------------------------------------------------------------------------------- | ||
+ | Predicate Information | ||
+ | --------------------- | ||
+ | 2 - access(" | ||
</ | </ | ||
+ | </ | ||
- | Réponse: | ||
- | * Le traitement commence par l' | ||
- | * Puis le traitement se poursuit avec l' | ||
- | **Question a)** : Pour chacune des 6 requêtes suivantes: | + | Compléter le tableau en indiquant la cardinalité, le coût et si l' |
- | 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 | | ||
+ | | age < = 20 | | ||
+ | | age < = 30 | | ||
+ | | age < = 40 | | ||
+ | | age < = 60 | | ||
+ | | age < = 100 | | ||
- | **Question b)** : Etudier le plan de la requête AgeEgalCodeEgal. Combien d' | ||
+ | <showif isloggedin> | ||
+ | **<fc # | ||
+ | ^ Prédicat ^ Rows ^ Index utilisé ^ Cout ^ | ||
+ | | age < = 10 | 22 200 | | ||
+ | | age < = 20 | 44 400 | | ||
+ | | age < = 30 | 66 600 | | ||
+ | | age < = 40 | 88 800 | | ||
+ | | age < = 60 | 133K | | ||
+ | | age < = 100 | 220K | | ||
- | **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 | ||
- | |age< | ||
- | |age< | ||
- | |age< | ||
- | Si on suppose que la durée de AgeInf est proportionnelle au nombre de nuplets du résultat, quelle serait la durée pour // | ||
- | **Question d)** : Observer que pour '' | + | </showif> |
+ | **Question b)** : | ||
+ | Pour quel prédicat Oracle préfère-t-il évaluer la requête sans utiliser l' | ||
- | ===== Exercice 4. Coût de l' | + | <showif isloggedin> |
- | L' | + | **<fc # |
- | Oracle estime le coût de plusieurs possibilités d' | + | A partir |
- | Le **coût d'un accès séquentiel** est proportionnel au nombre de pages (i.e., de blocs) à lire. On a la formule | + | </ |
- | 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 '' | ||
+ | * la première utilise l' | ||
+ | * la deuxième ne l' | ||
<code sql> | <code sql> | ||
- | set autotrace off | + | explain plan for |
- | column table_name format A20 | + | select |
- | select | + | |
+ | where a.cp BETWEEN 50000 AND ....; | ||
+ | @p4 | ||
</ | </ | ||
- | Afficher le coût d'une lecture séquentielle : | + | <showif isloggedin> |
+ | **<fc # | ||
<code sql> | <code sql> | ||
- | set autotrace trace explain | + | explain |
- | select | + | select |
+ | | ||
+ | where a.cp BETWEEN 50000 AND 80000; | ||
+ | @p4 | ||
</ | </ | ||
- | En déduire la valeur de C. | + | < |
+ | -------------------------------------------------------------------------------- | ||
+ | | Id | Operation | ||
+ | -------------------------------------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | ||
+ | | 1 | TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE | 66506 | 1233K| 66663 | ||
+ | |* 2 | INDEX RANGE SCAN | INDEXCP | ||
+ | -------------------------------------------------------------------------------- | ||
+ | </ | ||
- | On veut vérifier que C est constante. Pour cela calculer C à partir à partir du coût d' | ||
- | Le nombre de pages de BigAnnuaire est : | ||
<code sql> | <code sql> | ||
- | set autotrace off | + | explain plan for |
- | select | + | select |
- | where table_name = ' | + | |
+ | where a.cp BETWEEN 50000 AND 90000; | ||
+ | @p4 | ||
</ | </ | ||
- | Le coût d'une lecture séquentielle est : | + | < |
- | < | + | ---------------------------------------------------------------------- |
- | set autotrace trace explain | + | | Id | Operation |
- | select | + | ---------------------------------------------------------------------- |
+ | | 0 | SELECT STATEMENT | ||
+ | |* 1 | TABLE ACCESS FULL| BIGANNUAIRE | 88528 | 1642K| 70893 | ||
+ | ---------------------------------------------------------------------- | ||
+ | Predicate Information | ||
+ | --------------------- | ||
+ | 1 - filter(" | ||
</ | </ | ||
- | 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' | ||
- | 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 | ||
- | du plan affiché par Oracle contient une ligne <fc # | ||
- | Si, en revanche, la sélection AGE=18 était évaluée au vol, cette même rubrique contiendrait la ligne <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? | ||
- | **Question | + | </ |
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== Exercice 3. Comparaison de plans d' | ||
+ | |||
+ | Pour une requête donnée, on veut étudier plusieurs plans équivalents afin de comparer le coût de chacun d' | ||
+ | |||
+ | === Directive pour forcer/ | ||
+ | Énumérer les plans équivalents revient à considérer toutes les combinaisons entre utiliser des index ou pas en ajoutant les **directives** '' | ||
+ | |||
+ | **Question | ||
+ | |||
+ | * La directive '' | ||
<code sql> | <code sql> | ||
- | set autotrace trace explain | + | explain |
- | select * from BigAnnuaire where age < 25 and cp < 12000; | + | |
+ | from BigAnnuaire | ||
+ | @p4 | ||
</ | </ | ||
- | * Remplacer la valeur <fc # | + | * La directive |
- | * A partir de la requête initiale //age < <fc # | + | |
- | * 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. | + | |
+ | <code sql> | ||
+ | explain plan for | ||
+ | | ||
+ | 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 | + | **Question |
- | On se pose la question: Est-ce | + | |
- | Soit la requête AgeInfCodeInf composée de deux prédicats de sélection sur age et sur cp: | + | **Question c)** : Pour la requête '' |
+ | |||
+ | Pour forcer | ||
<code sql> | <code sql> | ||
- | select * from BigAnnuaire where age < X and cp < Y; | + | explain plan for |
+ | | ||
+ | | ||
+ | @p4 | ||
</ | </ | ||
- | 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: | ||
- | * 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 | + | |
- | Préciser la taille des index : | + | |
+ | Remarque: Déclarer plusieurs directives '' | ||
+ | |||
+ | |||
+ | |||
+ | ===== Exercice 4 (facultatif). 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 '' | ||
+ | |||
+ | **Question a)** : | ||
<code sql> | <code sql> | ||
- | set autotrace off | + | explain plan for |
- | column table_name A10 | + | select |
- | column index_name A10 | + | from Annuaire a, Ville v |
- | select | + | where a.cp = v.cp |
- | avg_leaf_blocks_per_key, avg_data_blocks_per_key | + | and a.age=18; |
- | from all_indexes | + | @p3 |
- | where table_name | + | </ |
- | set autotrace trace explain | + | |
+ | <showif isloggedin> | ||
+ | **<fc # | ||
+ | |||
+ | < | ||
+ | --------------------------------------------------------- | ||
+ | | Id | Operation | ||
+ | --------------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | ||
+ | |* 1 | HASH JOIN | ||
+ | | 2 | TABLE ACCESS BY INDEX ROWID| ANNUAIRE | 20 | | ||
+ | |* 3 | INDEX RANGE SCAN | INDEXAGE | 20 | | ||
+ | | 4 | TABLE ACCESS FULL | VILLE | | ||
+ | --------------------------------------------------------- | ||
+ | Predicate Information | ||
+ | --------------------- | ||
+ | 1 - access(" | ||
+ | 3 - access(" | ||
</ | </ | ||
- | 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 '' | ||
+ | **Question b)** : | ||
+ | Même requête mais en remplaçant Annuaire par BigAnnuaire. Pourquoi la table Ville est-elle lue avant l' | ||
+ | <code sql> | ||
+ | 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 | ||
+ | </ | ||
- | ===== Exercice 6. Comparaison de plans d' | + | <showif isloggedin> |
+ | **<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. | ||
+ | |||
+ | < | ||
+ | ------------------------------------------------------------ | ||
+ | | Id | Operation | ||
+ | ------------------------------------------------------------ | ||
+ | | 0 | SELECT STATEMENT | ||
+ | |* 1 | HASH JOIN | ||
+ | | 2 | TABLE ACCESS FULL | VILLE | ||
+ | | 3 | TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE | 2200 | | ||
+ | |* 4 | INDEX RANGE SCAN | INDEXAGE | ||
+ | ------------------------------------------------------------ | ||
+ | Predicate Information | ||
+ | --------------------- | ||
+ | 1 - access(" | ||
+ | 4 - access(" | ||
+ | </ | ||
+ | </ | ||
- | Le but de cet exercice est de montrer que le plan choisi par l' | ||
- | Pour ce faire, il vous sera demandé d' | ||
- | É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/ | ||
- | | + | **Question c)** : |
<code sql> | <code sql> | ||
- | | + | explain plan for |
- | from BigAnnuaire where age > 18; | + | |
+ | from BigAnnuaire a, Ville v | ||
+ | where a.cp = v.cp | ||
+ | and v.population >= 985000; | ||
+ | @p3 | ||
+ | </code> | ||
+ | |||
+ | <showif isloggedin> | ||
+ | **<fc # | ||
+ | REPONSE | ||
+ | |||
+ | Il y a 16,57 villes avec une population supérieure à 985000 | ||
+ | |||
+ | On a 220 personnes par ville en moyenne | ||
+ | |||
+ | Donc 16,57 * 220 = 3647 | ||
+ | |||
+ | </fc>** | ||
+ | |||
+ | < | ||
+ | ------------------------------------------------------------ | ||
+ | | Id | Operation | ||
+ | ------------------------------------------------------------ | ||
+ | | 0 | SELECT STATEMENT | ||
+ | | 1 | NESTED LOOPS | ||
+ | | 2 | | ||
+ | |* | ||
+ | |* 4 | INDEX RANGE SCAN | INDEXCP | ||
+ | | 5 | TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE | 220 | | ||
+ | ------------------------------------------------------------ | ||
+ | Predicate Information: | ||
+ | ---------------------- | ||
+ | 3 - filter(V.POPULATION>=985000) | ||
+ | 4 - access(A.CP=V.CP) | ||
</ | </ | ||
- | * Empêcher l' | + | </ |
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== Exercice 5: Autres requêtes ===== | ||
+ | Dans cet exercice on vous demande | ||
+ | |||
+ | a) Requêtes avec group by | ||
<code sql> | <code sql> | ||
- | | + | explain plan for |
- | | + | |
+ | from BigAnnuaire | ||
+ | group by age; | ||
+ | @p3 | ||
+ | </ | ||
+ | |||
+ | <showif isloggedin> | ||
+ | **<fc # | ||
+ | |||
+ | pas d' | ||
+ | |||
+ | < | ||
+ | | Id | Operation | ||
+ | -------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | ||
+ | | 1 | HASH GROUP BY | | 100 | | ||
+ | | 2 | INDEX FAST FULL SCAN| INDEXAGE | | ||
+ | -------------------------------------------------- | ||
</ | </ | ||
+ | </ | ||
+ | |||
+ | b) Requêtes avec group by having | ||
- | * Forcer un index et empêcher un autre index dans une même requête | ||
<code sql> | <code sql> | ||
- | | + | explain plan for |
- | | + | |
+ | from BigAnnuaire | ||
+ | group by age | ||
+ | having count(*) > 200; | ||
+ | @p3 | ||
+ | </ | ||
+ | |||
+ | <showif isloggedin> | ||
+ | **<fc # | ||
+ | |||
+ | Operation nommée FILTER pour évaluer le having. | ||
+ | |||
+ | < | ||
+ | | Id | Operation | ||
+ | --------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | ||
+ | |* 1 | FILTER | ||
+ | | 2 | HASH GROUP BY | | 5 | | ||
+ | | 3 | INDEX FAST FULL SCAN| INDEXAGE | | ||
+ | --------------------------------------------------- | ||
+ | Predicate Information | ||
+ | --------------------- | ||
+ | 1 - filter(COUNT(*)> | ||
</ | </ | ||
+ | </ | ||
- | ** Attention :** mettre plusieurs index(.., | + | c) Requete min max |
- | **Question a)** : Pour la requête AgeEgalCodeEgal ci-dessus proposer 4 plans **équivalents** | + | <code sql> |
+ | explain plan for | ||
+ | select min(cp), max(cp) | ||
+ | from BigAnnuaire a; | ||
+ | @p3 | ||
+ | </ | ||
- | **Question b)** : Mêmes questions pour les requêtes AgeInfCodeInf et AgeInfCodeEntre. | + | <showif isloggedin> |
+ | **<fc # | ||
+ | Sort aggregate: trier et ne garder que les valeurs min et max | ||
+ | < | ||
+ | | Id | Operation | ||
+ | ------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | ||
+ | | 1 | SORT AGGREGATE | ||
+ | | 2 | INDEX FAST FULL SCAN| INDEXCP | 220K| | ||
+ | ------------------------------------------------- | ||
+ | Column Projection | ||
+ | ----------------- | ||
+ | 2 - " | ||
+ | </ | ||
+ | </ | ||
+ | d) Requête avec **not in** | ||
+ | <code sql> | ||
+ | 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< | ||
+ | @p3 | ||
+ | </ | ||
+ | <showif isloggedin> | ||
+ | **<fc # | ||
+ | OPeration n°1: ANTI join: si la condition a.prenom=b.prenom est fausse alors le nuplet est dans le résultat. | ||
- | ===== Exercice 7. Index couvrant une requête ===== | + | < |
- | Vérifier qu'il est possible d' | + | | Id | Operation |
- | * avec un distinct | + | ------------------------------------------------------------ |
- | * avec un group by et une agrégation | + | | 0 | SELECT STATEMENT |
- | * avec un order by | + | |* 1 | HASH JOIN RIGHT ANTI |
- | * avec les 2 attributs age et cp dans la clause select | + | | 2 | TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE | 15533 | |
+ | |* 3 | INDEX RANGE SCAN | INDEXAGE | ||
+ | | 4 | TABLE ACCESS FULL | BIGANNUAIRE | | ||
+ | ------------------------------------------------------------ | ||
+ | Predicate Information | ||
+ | --------------------- | ||
+ | 1 - access(A.PRENOM=B.PRENOM) | ||
+ | 3 - access(B.AGE< | ||
+ | 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 | ||
+ | <code sql> | ||
+ | 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 | ||
+ | </ | ||
+ | <showif isloggedin> | ||
+ | **<fc # | ||
- | ===== Exercice 8 (facultatif). Durée d'une requête ===== | + | Opération n°2: Lire toute la table et indexer en mémoire les couples |
- | On veut observer | + | Puis relire toute la table: pour chaque personne p interroger |
- | '' | + | Si aucun couple trouvé: alors ajouter |
+ | |||
+ | < | ||
+ | ---------------------------------------------------- | ||
+ | | Id | Operation | ||
+ | ---------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | ||
+ | |* 1 | HASH JOIN RIGHT ANTI| | ||
+ | | 2 | TABLE ACCESS FULL | BIGANNUAIRE | | ||
+ | | 3 | TABLE ACCESS FULL | BIGANNUAIRE | | ||
+ | ---------------------------------------------------- | ||
+ | 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> | ||
+ | </showif> | ||
+ | |||
+ | f) Requête avec minus : les code spostaux des villes | ||
- | On définit la requête M1(A) en fonction d'une valeur A de l'âge : | ||
<code sql> | <code sql> | ||
- | | + | explain plan for |
- | from BigAnnuaire | + | |
- | where age <= A; | + | from BigAnnuaire |
+ | | ||
+ | | ||
+ | from BigAnnuaire b | ||
+ | where b.age>=100; | ||
+ | @p3 | ||
+ | </ | ||
+ | |||
+ | <showif isloggedin> | ||
+ | **<fc # | ||
+ | |||
+ | 2 index (indexage et indexcp) sont utilisés pour produire la liste des cp où résident des centenaires. | ||
+ | |||
+ | L' | ||
+ | |||
+ | < | ||
+ | | Id | Operation | Name | Rows | | ||
+ | ------------------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | | 220K| | ||
+ | | 1 | MINUS | | | | ||
+ | | 2 | SORT UNIQUE | | 220K| | ||
+ | | 3 | INDEX FAST FULL SCAN | INDEXCP | ||
+ | | 4 | SORT UNIQUE | | 2200 | | ||
+ | |* 5 | VIEW | index$_join$_002 | 2200 | | ||
+ | |* 6 | HASH JOIN | | | | ||
+ | |* 7 | INDEX RANGE SCAN | INDEXAGE | ||
+ | | 8 | INDEX FAST FULL SCAN| INDEXCP | ||
+ | ------------------------------------------------------------- | ||
+ | Predicate Information | ||
+ | --------------------- | ||
+ | 5 - filter(B.AGE> | ||
+ | 6 - access(ROWID=ROWID) | ||
+ | 7 - access(B.AGE> | ||
</ | </ | ||
- | 1. Temps total écoulé. Pour différentes valeurs de l' | + | </ |
- | 2. Temps de calcul et temps de transfert du résultat. Mesurer la durée d' | + | g) requête |
- | 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' | + | |
+ | <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 | ||
+ | </ | ||
+ | <showif isloggedin> | ||
+ | **<fc # | ||
- | ===== Questions fréquentes ===== | + | Opérations 3 et 2: trier les personnes par age croissant. |
- | - Emacs : avant d' | + | Opérations 6,5,4: accès aux personnes cp=75000 puis trier leur age. |
- | - Directives d' | + | 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 |
- | | + | ------------------------------------------------------------- |
- | + | | 0 | SELECT STATEMENT | |
- | S'il y a une erreur de syntaxe dans une directive Oracle ignore la directive **SANS** vous avertir. | + | | 1 | MERGE JOIN ANTI | | 2200 | |
+ | | 2 | SORT JOIN | ||
+ | | 3 | TABLE ACCESS FULL | BIGANNUAIRE | 220K| | ||
+ | |* 4 | SORT UNIQUE | ||
+ | | 5 | TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE | 220 | | ||
+ | |* 6 | | ||
+ | ------------------------------------------------------------- | ||
+ | Predicate Information | ||
+ | --------------------- | ||
+ | 4 - access(A.AGE< | ||
+ | 6 - access(B.CP=75000) | ||
+ | </ | ||
+ | </ | ||
- | ===== Documentation ===== | + | 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' | Description d'un index : profondeur de l' | ||
- | <code sql> | + | <code sql> |
- | set autotrace off | + | |
- | | + | from user_indexes; |
</ | </ | ||
Description d'une table : cardinalité, | Description d'une table : cardinalité, | ||
- | <code sql> | + | <code sql> |
- | | + | |
- | select table_name, num_rows, blocks from user_tables; | + | select table_name |
+ | | ||
+ | |||
+ | select table_name as nom, num_rows as cardinalite, | ||
+ | from all_tables | ||
+ | where table_name = ' | ||
</ | </ | ||
- | + | ||
Description d'un attribut : valeur min, max, nb de valeurs disctinctes. | Description d'un attribut : valeur min, max, nb de valeurs disctinctes. | ||
- | <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 367: | Ligne 844: | ||
etc... de nombreuses autres informations sont disponibles tq par exemple l' | etc... de nombreuses autres informations sont disponibles tq par exemple l' | ||
+ | |||
+ | |||
+ | ===== Questions fréquentes ===== | ||
+ | |||
+ | - Emacs : avant d' | ||
+ | |||
+ | - Directives d' | ||
+ | |||
+ | <code sql> | ||
+ | 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' | ||
Ligne 374: | Ligne 871: | ||
===== Divers ===== | ===== Divers ===== | ||
- | Aller vers [[site: | + | Aller vers [[site: |
site/enseignement/master/bdr/tmeindex.1485343380.txt.gz · Dernière modification : de hubert