Bases de Données / Databases

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 :

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

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

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 :

SELECT * FROM ...; -- sans WHERE 

... WHERE age = 18;

... WHERE age < 25;  

... WHERE age > 18;  

... WHERE age BETWEEN 18 AND 25;  

... WHERE cp = 75000;  

... WHERE cp < 25000;  

... WHERE cp > 75000;  

... WHERE cp BETWEEN 15000 AND 25000;  

... WHERE age = 18 AND cp = 75000;  

... WHERE age = 18 AND cp < 25000;  

... WHERE age < 25 AND cp = 75000;  

... WHERE age < 25 AND cp < 25000;  

... WHERE age < 25 AND (cp BETWEEN 15000 AND 25000);  

... WHERE (age BETWEEN 18 AND 25)  AND cp < 25000;  

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.

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.

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;

• 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

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

• IndexAge est un index sur BigAnnuaire(age)
• IndexCp est un index sur BigAnnuaire(cp).

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:

• 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. 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 :

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

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?

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;

• Remplacer la valeur 25 une valeur X pour que seul l'index INDEXCP soit utilisé. Donner un exemple de X ?
• A partir de la requête initiale age < 25 and cp < 12000. Remplacer la valeur 12000 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:

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:

• 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. 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.

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.

• La directive index() force l'usage d'un index. Exple pour AgeSup

   SELECT /*+ index(BigAnnuaire IndexAge) */  *  
   FROM BigAnnuaire WHERE age > 18;

• La directive no_index() empêche l'usage d'un index. Exple pour AgeEgal

   SELECT /*+  no_index(BigAnnuaire IndexAge) */  * 
   FROM BigAnnuaire WHERE age = 18;

• 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

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

• La directive index_combine() force à utiliser deux index:

    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.

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 :

• avec un distinct
• avec un group by et une agrégation
• avec un order by
• avec les 2 attributs age et cp dans la clause select

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 ?

- 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.

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.

Aller vers BDR