LesIndex

TME : Les Index

L'objectif de ce TP est de mettre en évidence l'utilisation des index pour améliorer le temps de réponse des requêtes. Lire l'annexe PlanRequete

Installer les fichiers du TME

Ouvrir une fenêtre de terminal (xterm), pour exécuter les commandes suivantes :

Exercice

1) (préparation) Créer une relation Annuaire(nom, prenom, age, cp, tel) en utilisant le fichier annuaire.sql.

SQL> @annuaire

Elle contient 10000 tuples sur 90 prénoms et 100 noms différents. Les codes postaux (cp) sont des multiples de 100 et sont compris entre 1000 et 100000. Le numéro de téléphone est une chaîne de 10 chiffres commencant par 0. Le numéro de téléphone est une clé de l'annuaire.

Répondre aux questions suivantes dans le fichier tme2.sql

2) Proposer une ou plusieurs requêtes pour vérifier si la distribution de l'attribut age est uniforme, quasi uniforme ou fortement biaisée. Expliquer brièvement comment analyser le résultat de cette requête.

3) Proposer une ou plusieurs requêtes pour vérifier que les attributs age et prénom sont indépendants :

• Quel que soit l'age, y a-t-il autant de prénom différents pour un age donné ?
• Quel que soit le prénom, y-a-t-il autant d'âge différents pour un prénom donné ?
• Y-a-t-il tous les prénoms possibles pour chaque age, et tous les ages possibles pour chaque prénom ?

4) En utilisant les formules du cours, estimer la cardinalité des requêtes suivantes. Comparer ensuite la cardinalité estimée avec la cardinalité réelle des requêtes. Quel est le pourcentage d'erreur (réel/théorique) ?

• R1: select * from Annuaire
• R2: select * from Annuaire where age < 65;
• R3: select * from Annuaire where cp between 75000 and 78000;
• R4: select * from Annuaire where age=18 and cp < 1200;
• R5: select * from Annuaire where age=18 and cp = 1200;

* R6: select count(*) from Annuaire where age < 65;

5) Créer les index mono-attributs permettant d'améliorer les performances des requêtes précedentes. La syntaxe est :

• create index nom_index on nom_relation(nom_attribut) ;
• pour effacer un index : drop index nom_index;

5.1) Afin de visualiser les informations détaillées sur le traitement des requêtes :

• activer le mode de visualisation des plans :

  • set autotrace trace explain stat (pour désactiver ce mode : set autotrace off)
• Dans cette question, on demande à l'optimiseur de requêtes d'utiliser des règles heuristiques et non pas une estimation du coût. La session est modifiées en conséquence :
• alter session set optimizer_mode = RULE;
• puis re-exécuter chaque requête pour voir le plan d'exécution. Voir l'annexe PlanRequete.

Pour chaque requête, afficher les index utilisés par le moteur de requête.

Présenter vos réponses dans un tableau récapitulatif :

Analyser le tableau et suggérer des règles heuristiques que l'optimiseur utilise pour choisir un index. Si nécessaire, compléter le tableau avec d'autres requêtes.

5.2) Quelles sont les requêtes pour lesquelles le plan d'exécution construit par l'optimiseur ne vous semble pas optimal ? Donner un exemple.

6) Estimation du coût des accès. L'ordre ANALYZE ajoute des statistique dans le dictionnaire du SGBD pour permettre à l'optimiseur d'estimer le coût des requêtes :

• analyze table Annuaire compute statistics; (pour supprimer les statistiques: analyze table Annuaire delete statistics;)

6.1) En interrogant les vues user_tables, user_tab_columns et user_indexes, expliquer quelles sont les statistiques maintenues dans le dictionnaire. Désactiver le mode autotrace avant d'interroger les vues (set autrotrace off).

• parmi les attributs de ces vues, quels sont ceux qui représentent les statistiques présentées en cours ?

  • cardinalité d'une relation
  • description du domaine d'un attribut avec ses valeurs min et max et son nombre de valeurs distinctes,
  • etc...

6.2) Afin que le SGBD maintienne des statistiques sur vos index, exécutez la commande suivante pour chaque index :

• analyze index nom_index compute statistics;

*On demande maintenant à l'optimiseur de requêtes d'utiliser un modèle de coût (et non pas des règles heuristiques) pour choisir un plan d'exécution efficace. La session est modifiées en conséquence : ** alter session set optimizer_mode = CHOOSE; Pour chaque requête, afficher les index utilisés par le moteur de requêtes. Expliquer pourquoi les index utilisés ne sont plus les mêmes qu'en 5.1)

6.2.1) En déduire la méthode que l'optimiseur utilise pour choisir un index.

6.2.2) Est ce l'optimiseur estime avec précision la cardinalité des requêtes ? Si non, expliquer pourquoi.

6.3) On veut déterminer à partir de quel facteur de sélectivité, l'optimiseur choisit la lecture séquentielle plutôt que l'accès par index. On appelle S le seuil du facteur de sélectivité au delà duquel la lecture séquentielle est utilisée de préférence à l'index. Combien vaut S ?

• Méthode suggérée pour déterminer S :

  • Faire varier la sélectivité du prédicat cp < v en faisant varier v, puis déterminer par dichotomie la valeur v à partir de laquelle l'index n'est plus utilisé.
• Mesurer S pour une requête contenant 2 prédicats de sélection. S est-il toujours le même? pourquoi ?

6.4) Quelles sont les requêtes pour lesquelles le plan d'exécution construit par l'optimiseur n'est pas optimal, donner un exemple.

7) (facultatif) Proposer une méthode pour mesurer la dégradation de performance due à l'index lors de l'insertion de données dans l'annuaire.

retour vers: LesTravauxDirigés, LesCours, l'Accueil.