TmeIndex

Tme sur les index

Objectifs: Définir des index soit non-plaçants, soit plaçants. Etudier l'impact des index sur les requêtes selon leur type : requête ciblée, requête multipoints, requête sur intervalle, requête min ou max. Etudier l'impact des index sur les mises à jour.

Préparation

• Récupérer l'archive tp-index-etu.tgz et la décompresser avec

  • tar zxvf tp-index-etu.tgz

• si nécessaire créer le rôle plustrace

  • sqlplus "sys/mot_de_passe@baseNN as sysdba" (l'utilisateur system ne convient pas)
  • puis exécuter @plustrce.sql
• obtenir un numéro de binome nn (de 01 à 20).

• créer un espace de stockage nommé ESPACEnn de 200M et un utilisateur ETUnn (preparation.sql)

  • donner le rôle DBA à ETUnn (le rôle DBA inclus de rôle plustrace).
• créer la structure de stockage des plans : (utlxplan10G.sql)

Documentation

• DocumentationOracle
• Les index non plaçants create index

• Les index plaçants:

  • arbre B+Tree IOT ou cluster
  • table de hachage hash cluster
• créer un index plaçant : create cluster et/ou create table|?
• Les vues du dictionnaire: DBA_EXTENTS, ...
• Les directives d'optimisation issues du livre Designing and Tuning for Performance.

Exercice 1

• 1.1 Créer un relation avec 1M d'employés.
• 1.2 Quelle est la taille de la relation en MO ? Peut-on estimer cette taille à partir de la taille des attributs et du taux de remplissage des blocs ?
• 1.3 Quelle requête effectue une lecture séquentielle de toute la relation, sans retourner tous les tuples en résultat ?
• 1.4 Combien de page sont lues (valeur physical reads) ? Sachant qu'un bloc mesure 8KO, cela est-il cohérent avec la valeur obtenue à la question 1.2 ?

Exercice 2

• Créer un index non plaçant sur l'attribut num. Quelle est la taille de cet index par rapport à la taille de la relation ?
• Combien de feuilles contient cet index ? Quelle est sa profondeur ?

Exercice 3

• Donner une requête bénéficiant au maximm de l'index créé.
• Donner une requête dont le prédicat contient la clé de l'index, mais pour laquelle l'accès par index est plus côuteux que la lecture séquentielle, en nombre de pages lues. Dans le cas le plus défavorable, quel est le rapport maximal entre les 2 modes d'accès.
• Donner une requête pour laquelle l'accès par index est quasi égal (en nombre de pages lues) au parcours séquentiel de toute la relation (moins de 5% d'écart entre les 2 modes d'accès).

Exercice 4

• Créer une relation Employe2 avec un index plaçant

  • create table Employe2(...) organization index ...;

• Remplir Employe2 à partir des tuples d'Employe

  • insert into Employ2 (select ... from Employe ...);
• Reprendre l'exercice 3 et comparer les résultats.

Exercice 5: Index et intégrité référentielle

• Créer les relations

  • Service(num, description) num est la clé de Service
  • Emp(num, ..., numServ) contrainte: Emp.numServ fait référence à Service.num

• Mesurer l'impact d'un index sur les mises à jour avec vérification d'une contrainte d'intégrité référentielle:

  • insertion de nouveaux emp dans des services existants
  • suppression de services avec restriction : valide seulement si aucun Emp dans le service à supprimer.

(Facultatif) Proposer d'autres exercices visant à appliquer les notions vues en cours : coût des mises à jour avec index, index multi-attributs, ...

LesTme