Architecture physique d

1
Architecture physique dune base de données
(ORACLE)
2
Structure physique Base (ORACLE)
Objets Logiques
Objets Physiques
TABLESPACE
DATAFILE
SEGMENT
DB BLOCK
EXTENT
OS BLOCK
3
Paramètres création table (ORACLE)

• INITIAL La taille du premier extent de la table
  
• NEXT La taille du deuxième extent de la table
• MINEXTENTS Le nombre d'extents de la table à sa
  création
• MAXEXTENTS Le nombre maximal d'extents de la
  table.
• PCTINCREASE Le taux d'incrémentation en
  pourcentage de l'extent N par rapport à l'extent
  N- 1 ( Pour N gt 2). Si pctincrease est égal à
  zéro, alors tous les extents à partir du
  troisième sont égaux au deuxième extent.

4
Paramètres création table (suite)

• ExempleCREATE TABLE FOURNISSEUR(
  NO_FOURNISSEUR NUMBER(10) NOT NULL,NOM
  VARCHAR2(100),PRENOM VARCHAR2(100),MAIL
  VARCHAR2(100),TELEPHONE VARCHAR2(30),
  CONSTRAINT FOURNISSEUR_PK PRIMARY
  KEY(NO_FOURNISSEUR) )PCTFREE 30PCTUSED
  70STORAGE(INITIAL 2KNEXT 1KMINEXTENTS
  4MAXEXTENTS 6PCTINCREASE 50)Cette table est
  créée avec un espace de 6.75 k, et sa taille max
  sera de 15.1875

5
Paramètres DB Block (ORACLE)

• PctFree c'est le pourcentage que Oracle laisse
  de libre dans un bloc de données pour les mises à
  jour. Dès qu'il reste que ce pourcentage de libre
  dans le bloc de données, Oracle bloque les
  insertion dans ce bloc et il ne permet que les
  "UPDATE" et "DELETE".
• PctUsed C'est le pourcentage en dessous du quel
  Oracle autorise à nouveau les insertions dans un
  bloc pour le quel les insertions étaient bloquées.

6
Structure Index BTree

• Un arbre B dordre K est un arbre équilibré
• Chaque nœud est une page contenant au mois K et
  au plus 2K articles.
• Dans un nœud, lordre se fait par rapport à la
  clé.
• Les opérations possible sur arbre B sont
• Recherche
• Insertion
• Suppression

7
Index BTree INSERTION

• L'insertion dune clé dans un arbre dordre k
  nécessite les opérations suivantes
• Chercher la feuille où la nouvelle clé devrait
  être insérée.
• Insérer la clé dans la feuille.
• Si le noeud possède plus de 2k clés, on le
  transforme en deux nœuds, chacun possédant k
  clés, et on remonte la clé du milieu dans le nœud
  père.
• Le nœud père peut alors posséder trop de fils, le
  procédé continue jusqu'à ce que l'on atteigne la
  racine.
• Si la racine doit être divisée, on remonte la clé
  du milieu dans une nouvelle racine, qui aura
  alors pour fils les deux nœuds crées à partir de
  l'ancienne racine.

8
Exemple Arbre B

• On considère un index de type B dordre 2 sur la
  colonne durée de la table Projet.
• Construire larbre correspondant à linsertion
  des durées suivantes dans lordre indiqué ( ne
  mettre dans larbre que les clés ) 
• (1,9,5,11,43,61,13,23,89,41,119,25,27,29,31)

9
Solution
10
Optimisation requêtes SQL

• Le travail d'optimisation d'une requête SQL
  commence par la génération du plan d'exécution
  "EXPLAIN PLAN", ceci permet de connaître les
  différentes étapes de lexécution de la requête
  (si le système utilise des indexes ou passe par
  un FULL SCAN de la table ... )Pour générer le
  Plan d'exécution d'une table sous ORACLE il faut
  
• Créer la table PLAN_TABLE dans le schéma
  utilisateur si elle n'existe pas Pour créer
  cette table il faut exécuter le script
  UTLXPLAN.SQL, il existe dans "ORACLE_HOME/RDBMS/A
  DMIN" sous UNIX ou "ORACLE_HOME/RDBMS/ADMIN"
  sous Windows.
• Générer le plan d'exécution de la requête SQL
  avec la commande EXPLAIN PLAN EXPLAIN PLAN
  FORREQUETE_SQL
• Afficher le plan d'exécution de la requête SQL à
  partir de la table PLAN_TABLE.
• Dans un deuxième temps, il faut interpréter le
  plan dexécution et revoir le code SQL de la
  requête et les indexes des tables sil le faut.

11
Optimisation requêtes SQL (Suite)

• EXPLAIN PLAN FOR
• SELECT P.LIBELLE AS Projet, A.NUMERO_EMPLOYE AS
  Employé
• FROM PROJET P INNER JOIN AFFECTATION_PROJET A
• ON P.PROJET_CODE A.PROJET_CODE
• WHERE A.PRIORITE 1
• Le plan dexécution de la requête est le
  suivant 
• 0 SELECT STATEMENT
• 1 NESTED LOOPS
• 2 TABLE ACCESS
  AFFECTATION_PROJET FULL
  
• 3 TABLE ACCESS PROJET BY
  INDEX ROWID
• 4 INDEX PROJET_PK
  UNIQUE SCAN
• Le système fait une boucle imbriquée pour la
  jointure entre les tables AFFECTATION_PROJET et
  PROJET. Il commence par une lecture séquentiel de
  la table AFFECTATION_PROJET pour chercher les
  lignes ayant comme priorité 1. Pour chaque ligne,
  il extrait la valeur de PROJET_CODE et interroge
  lindex PROJET_PK de la table PROJET pour
  récupérer les adresses des lignes correspondantes
  dans la table PROJET, puis il récupère La ligne
  correspondante à chaque adresse.Enfin le système
  fait la jointure entre les lignes des deux tables
  et envoi les libelles des projets et les numéros
  demployés.

12
Exercices

• On considère un index de type B dordre 2 sur la
  colonne durée de la table Projet. Construire
  larbre correspondant à linsertion des durées
  suivantes dans lordre indiqué, ne mettre dans
  larbre que les clés (1,9,5,11,43,61,13,23,89,41
  ,119,25,27,29,31)
• Générer et expliquer le plan dexécution de la
  requête suivante
• SELECT P.LIBELLE AS Projet, T.DESCRIPTION AS
  Tache
• FROM PROJET P INNER JOIN TACHE T
• ON P.PROJET_CODE T.PROJET_CODE
• WHERE T.PCT_REALISATION 100
• Quest-ce que vous pouvez proposer pour optimiser
  la requête précédente ?

13
Solution

• Larbre B est le suivant

14
Solution (suite )

• 2. Le plan dexécution de la requête est le
  suivant 
• SELECT STATEMENT
• NESTED LOOPS
• TABLE ACCESS TACHE FULL
  
• TABLE ACCESS PROJET BY
  INDEX ROWID
• INDEX PROJET_PK
  UNIQUE SCAN
• Le système fait une boucle imbriquée pour la
  jointure entre les tables TACHE et PROJET. Il
  commence par une lecture séquentiel de la table
  TACHE pour chercher les lignes ayant un
  pourcentage de réalisation égal à 100. Pour
  chaque ligne, il extrait la valeur de PROJET_CODE
  et interroge lindex PROJET_PK de la table PROJET
  pour récupérer les adresses des lignes
  correspondantes dans la table PROJET, puis il
  récupère La ligne correspondante à chaque
  adresse.Enfin le système fait la jointure entre
  les lignes des deux tables et envoi les libelles
  des projets et les descriptions de tâches.
• 3. Pour améliorer la requête, on peut ajouter un
  index sur la colonne PCT_REALISATION de la table
  Tache.