Table des matières

Ancien TME Index 2017

En 2018 aller vers le sujet suivant: 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 :

Préparation du TME

commandedescription
cd mon_répertoire aller dans votre répertoire de travail
tar zxvf /Infos/bd/public/tmeIndex.tgz installer l'archive dans votre répertoire principal
cd tmeIndex aller dans le répertoire du TME
emacs tmeIndex.sql & éditer le fichier à compléter pendant le TME
Alt-x my/sql-oracle ou Atl-x sql-oracle se connecter à Oracle. Voir ConnexionOracle
aller sur la ligne contenant @annuaire et faire Ctrl-C Ctrl-C définir la table Annuaire et les synonymes pour les tables BigAnnuaireSimple et BigAnnuaire

La table Annuaire(nom, prénom, age, cp, tel, profil) contient 1000 nuplets.

La table BigAnnuaire(nom, prénom, age, cp, tel, profil) a un attribut profil pouvant contenir jusqu'à 4000 caratères. La table a 220 000 nuplets.

La table BigAnnuaireSimple est identique à la table BigAnnuaire (même schéma, autant de nuplets). La différence est que BigAnnuaireSimple ne possède pas d'index..

Lire la section Questions fréquentes en bas de page. Répondre aux questions dans le fichier tmeIndex.sql

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. Les requêtes seront posées sur la table BigAnnuaireSimple ou BigAnnuaire.

SELECT * FROM BigAnnuaireSimple 
WHERE ...
SELECT * FROM BigAnnuaire 
WHERE ...

Voici les requêtes utilisées dans les exercices suivants :

Nom Requête SQL égalité inégalité intervalle
R1
SELECT * FROM ...; -- sans WHERE 
non non non
AgeEgal
... WHERE age = 18;
AgeInf
... WHERE age < 25;  
AgeSup
... WHERE age > 18;  
AgeEntre
... WHERE age BETWEEN 18 AND 25;  
CodeEgal
... WHERE cp = 75000;  
CodeInf
... WHERE cp < 25000;  
CodeSup
... WHERE cp > 75000;  
CodeEntre
... WHERE cp BETWEEN 15000 AND 25000;  
Age et Code postal :
AgeEgalCodeEgal
... WHERE age = 18 AND cp = 75000;  
AgeEgalCodeInf
... WHERE age = 18 AND cp < 25000;  
AgeInfCodeEgal
... WHERE age < 25 AND cp = 75000;  
AgeInfCodeInf
... WHERE age < 25 AND cp < 25000;  
AgeInfCodeEntre
... WHERE age < 25 AND (cp BETWEEN 15000 AND 25000);  
AgeEntreCodeInf
... WHERE (age BETWEEN 18 AND 25)  AND cp < 25000;  
Age puis dénombrement :
AgeInfCompte
SELECT COUNT(*) FROM ... WHERE age < 60; 

Rmq: la dernière requête AgeInfCompte est une sélection suivie d'une agrégation (avec le count). Comprendre chaque requête et passer à l'exercice suivant.

Exercice 2. Plan sans index

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.

SET autotrace trace EXPLAIN

La durée estimée s'affiche dans la colonne Time du tableau représentant le plan d'exécution de la requête.

Question/réponse : Quelle est la durée de la requête R1 affichant toutes les données de BigAnnuaireSimple ?

SET autotrace trace EXPLAIN
SELECT * FROM BigAnnuaireSimple;

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	  | Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |		      |   220K|   846M| 59767	(1)| 00:11:58 |
|   1 |  TABLE ACCESS FULL| BIGANNUAIRESIMPLE |   220K|   846M| 59767	(1)| 00:11:58 |
---------------------------------------------------------------------------------------

Réponse: la durée est de 11 minutes et 58 secondes.

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

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.

    SET autotrace trace EXPLAIN
    SELECT * FROM BigAnnuaireSimple WHERE age=18;

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

Exercice 3. Plan avec index

On considère la table BigAnnuaire identique à BigAnnuaireSimple mais ayant deux index

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 ?

SELECT * FROM BigAnnuaire WHERE age = 18;
-------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		  |  2200 |  8666K|  2206   (1)| 00:00:27 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIGANNUAIRE |  2200 |  8666K|  2206   (1)| 00:00:27 |
|*  2 |   INDEX RANGE SCAN	    | INDEXAGE	  |  2200 |	  |	5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("AGE"=18)

Réponse:

Question a) : Pour chacune des 6 requêtes suivantes: AgeEgal, AgeInf, AgeEntre, CodeEgal, CodeInf, CodeSup. 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 ?

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.

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é ?

Exercice 4. Coût de l'accès séquentiel aux données

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. Oracle estime le coût de plusieurs possibilités d'accès afin de choisir l'accès de moindre coût.

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 :

SET autotrace off
COLUMN TABLE_NAME format A20
SELECT TABLE_NAME, blocks, num_rows FROM user_tables;

Afficher le coût d'une lecture séquentielle :

SET autotrace trace EXPLAIN
SELECT * FROM Annuaire;

En déduire la valeur de C.

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 :

SET autotrace off
SELECT TABLE_NAME, blocks, num_rows FROM all_tables
WHERE TABLE_NAME = 'BIGANNUAIRE';

Le coût d'une lecture séquentielle est :

SET autotrace trace EXPLAIN
SELECT * FROM BigAnnuaire;

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 coût 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 access(“AGE”=18). Si, en revanche, la sélection AGE=18 était évaluée au vol, cette même rubrique contiendrait la ligne filter(“AGE”=18). 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:

SET autotrace trace EXPLAIN
SELECT * FROM BigAnnuaire WHERE age < 25 AND cp < 12000;

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:

SELECT * FROM BigAnnuaire WHERE age < X AND cp < Y;

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:

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. Préciser la taille des index :

SET autotrace off
COLUMN TABLE_NAME format A10
COLUMN index_name format A10
SELECT TABLE_NAME, index_name, blevel, distinct_keys, leaf_blocks,
avg_leaf_blocks_per_key, avg_data_blocks_per_key
FROM all_indexes
WHERE TABLE_NAME = 'BIGANNUAIRE';
SET autotrace trace EXPLAIN

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.

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

   SELECT /*+ index(BigAnnuaire IndexAge) */  *  
   FROM BigAnnuaire WHERE age > 18;
   SELECT /*+  no_index(BigAnnuaire IndexAge) */  * 
   FROM BigAnnuaire WHERE age = 18;
    SELECT /*+ index(BigAnnuaire IndexAge) no_index(BigAnnuaire IndexCp)  */  * 
    FROM BigAnnuaire WHERE age = 18 AND cp = 75000;
    SELECT /*+ index_combine(BigAnnuaire IndexAge IndexCp)  */  * 
    FROM BigAnnuaire WHERE age = 18 AND cp < 75000;

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.

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 ?

Question b) : Mêmes questions pour les requêtes AgeInfCodeInf et AgeInfCodeEntre.

Exercice 7. Index couvrant une requête

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 :

Exercice 8 (facultatif). Durée d'une requête

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 :

    SELECT MIN(cp) 
    FROM BigAnnuaire
    WHERE age <= A;

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.

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. Lire le fichier chrono.sql (se trouvant dans votre dossier tmeIndex) et le comprendre. Proposer une méthode pour répondre aux questions :

  1. Lorsque la sélection est suivie d'une projection (pour évaluer la clause select), quelle est la durée de la projection?
  2. Quelle est la durée pour transférer 1 nuplet de l'Annuaire dans l'application sqlplus ?

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.

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

   SET autotrace off
   SELECT index_name, blevel, distinct_keys, leaf_blocks FROM user_indexes;

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

    SET autotrace off
    SELECT TABLE_NAME, num_rows, blocks FROM user_tables;

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

    SET autotrace off
    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.

Divers

Aller vers BDR