fr
en
Outils pour utilisateurs
Outils du site
Panneau latéral
Table des matières
TME Index 2020
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 du TME
Lire la doc sur la Connexion au serveur Oracle 11
| commande | description |
|---|---|
| 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 |
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 |
Les schémas des deux tables sont indentiques et contiennent les attributs suivants :
| 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.
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.
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
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
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.
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
Question b) :
EXPLAIN plan FOR SELECT a.nom, a.prenom FROM BigAnnuaire a WHERE a.age BETWEEN 20 AND 29; @p3
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
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).
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
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 |
Question b) : Pour quel prédicat Oracle préfère-t-il évaluer la requête sans utiliser l'index IndexAge ? Pourquoi ?
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
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.
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(a IndexCP IndexAge) 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
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
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
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
b) Requêtes avec group by having
EXPLAIN plan FOR SELECT age, COUNT(*) FROM BigAnnuaire a GROUP BY age HAVING COUNT(*) > 200; @p3
c) Requete min max
EXPLAIN plan FOR SELECT MIN(cp), MAX(cp) FROM BigAnnuaire a; @p3
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
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
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
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
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.
Description d'un index : profondeur de l'arbre, nombre de valeurs indexées. Interroger user_indexes
SELECT index_name AS nom, blevel AS profondeur, distinct_keys AS nb_valeurs, leaf_blocks AS pages_de_Rowids FROM user_indexes;
Description d'une table : cardinalité, taille totale. Interroger user_tables
COLUMN nom format A20 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';
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
- 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.
SELECT /*+ directive */ * FROM WHERE
S'il y a une erreur de syntaxe dans une directive Oracle ignore la directive SANS vous avertir.
- directive pour utiliser deux index avec index_combine:
SELECT /*+ index_combine(a indexCP indexAge)*/ * FROM Annuaire a WHERE
- 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.
Divers
Outils de la page
