Administration des bases de données relationnelles Part I

Administration Base de Données 2011-2012
P.1
Administration des bases de données relationnelles
Part I
L’administration des bases de données requiert une bonne connaissance
- de l’organisation et du fonctionnement interne du SGBDR : structures logiques et physiques,
architecture fonctionnelle
- des outils de surveillance et d’administration disponibles
1 - Structures logiques et organisationnelles
1.1 - Le Dictionnaire de données
Une fonction essentielle du SGBDR est de permettre la description des 3 niveaux préconisés par l’architecture
ANSI/SPARC : logique, interne, externe. La description de ces différents niveaux et la correspondance entre eux
se fait via le Dictionnaire de Données.
Le Dictionnaire de Données recense tous les objets ainsi que toutes les informations relatives à la définition et à
l’exploitation de la base de données; il fournit des informations telles que :
- le nom des utilisateurs
- les privilèges et rôles attribués à chaque utilisateur
- le nom des différents objets logiques de la base (tables, vues, index, clusters, ...)
- des informations relatives aux contraintes d’intégrité
- les valeurs par défaut à utiliser dans les colonnes
- espaces alloués aux différents objets, espace couramment utilisé par les objets
- informations d’audit telles que ‘qui a accédé ou modifié un objet’.
Les tables et vues du dictionnaire des données ne sont accessibles qu’en lecture.
Exemple de Oracle Principaux types de vue du dictionnaire de données
Préfixe de la vue Description Exemples
all Informations sur tous les objets accessibles à l’utilisateur all_users : liste de tous les
utilisateurs de la base
user Informations sur tous les objets appartenant à un utilisateur user_tables : informations sur les
tables de l’utilisateur
dba Similaire à « user » mais pour tous les utilisateurs dba_tables : toutes les tables de
la base
v$ Vues dynamiques générées par le SGBDR au cours de son
fonctionnement : informations sur les performances, l’état
des fichiers, la mémoire utilisée
v$SGA : description de la SGA
1.2 - Les structures logiques d’une base de données
1.2.1 - Objets logiques d’une base de données relationnelle
Table : structure relationnelle qui décrit une entité ou une relation entre entités d’un univers réel. C’est la
représentation d’une relation au sens du modèle relationnel.
D’un point de vue logique la table est l’unité de stockage de base. Elle contient les données accessibles à
l’utilisateur.
Vue : table virtuelle générée à partir des tables de la base de données par une requête. Elément du schéma externe,
la vue n’a pas d’existence physique, ce qui ne l’empêche pas d’être accédée de la même façon qu’une table.
Utilisations les plus fréquentes des vues :
- fournir un niveau supplémentaire de sécurité en restreignant l’accès d’une table à certaines colonnes
- masquer la complexité de la base, en regroupant des données provenant de plusieurs tables
- présenter les données de façon personnalisée par rapport à l’usage
- stocker des requêtes complexes

Administration Base de Données 2011-2012
P.2
Séquences : objet de la base de données générant des numéros uniques pour des colonnes numériques de tables de
la base. Chaque rangée de table possède un numéro unique pour la colonne concernée.
L'ordre de création d'une séquence ( CREATE SEQUENCE) permet de définir optionnellement: une
valeur de départ, une valeur d'incrément ou de décrément, une valeur maximum (pour une séquence
incrémenté) ou minimum (pour une séquence décrémenté), la taille du cache contenant plusieurs
valeurs d'avance, la possibilité de revenir à la valeur de départ (CYCLE) après avoir atteint la valeur
max ou min. Quand ces options ne sont pas utilisées, le SGBDR utilise ses valeurs par défaut.
On utilise une séquence en la référençant via les ordres SQL de mise à jour INSERT, UPDATE..
Exemple: Création sous PostgreSql
CREATE SEQUENCE serial START 101 ; crée la séquence "serial" commençant à 101
Utilisation sous PostgreSql
SELECT CURRVAL(serial); la fonction CURRVAL fournit la valeur courante de la séquence
INSERT INTO ……VALUES(... NEXTVAL(serial)); la fonction NEXTVAL permet d’obtenir
une nouvelle valeur dans la séquence
Création sous Oracle :
CREATE SEQUENCE serial START WITH 101 ; crée la séquence "serial" commençant à 101
Utilisation sous Oracle :
select serial.currval from dual; la pseudo-colonne "currval" fournit la valeur courante de la
séquence (la table "dual" est l'emplacement dans lequel Oracle stocke les différentes séquences)
INSERT INTO ……VALUES(... serial.NEXTVAL); la pseudo-colonne "nextval" permet
d’obtenir une nouvelle valeur dans la séquence
Procédure : ensemble nommé de commandes stockées dans la base
Fonction : ensemble nommé de commandes stockées dans la base et retournant une valeur
Triggers : programme associé à une table. C’est une procédure stockée dans la base de données qui est exécutée
soit à l’occurrence d’un événement donné, soit à l’occurrence d’une condition donnée sur la table.
L'ordre CREATE TRIGGER permet de spécifier :
- la table à laquelle est attaché le trigger
- l'instruction sur laquelle se déclenche le trigger (INSERT, UPDATE, DELETE)
- le moment, par rapport à l'instruction, d'éxécuter le code associé : avant l'instruction (BEFORE),
après l'instruction (AFTER), à la place de l'instruction (INSTEAD OF, utilisable uniquement sur des
vues)
- la condition d'exécution du trigger (WHEN expression_logique)
- le code à exécuter, soit en le spécifiant directement dans l'ordre, soit en référençant une procédure
existante
Le code à exécuter est écrit en PL/SQL, le langage procédural du SGBDR.
Index : structure contenant pour chaque ligne d’une table l’adresse physique à laquelle se trouvent les valeurs de
cette ligne. L’index permet un accès direct à l’information, donc un accès plus rapide.
Le SGBDR crée automatiquement un index associé à la clé primaire d'une table.
L'ordre CREATE INDEX permet à l'administrateur de créer explicitement de nouveau index.
Le type d’index généralement utilisé dans les SGBDR est le BTREE et ses variantes.
Principe du BTREE (Balance Tree = arbre équilibré) : il s'agit d'un arbre dont toutes les feuilles sont au
même niveau; chaque nœud contient des clés triées par ordre croissant et des pointeurs; un pointeur
interne désigne un fils dans l’arbre; un pointeur externe désigne les données (adresse physique). La
recherche se fait en partant de la racine, et en comparant la valeur recherchée aux valeurs de clé du
noeud. Si la valeur recherchée est inférieur à la première valeur du noeud on poursuit via le premier
pointeur interne, sinon, si elle est comprise entre la première valeur et la suivante, on poursuit en utilisant
le pointeur correspondant; et ainsi de suite. Cette technique permet de retrouver beaucoup plus
rapidement une clé qu'en accès séquentiel.

Administration Base de Données 2011-2012
P.3
Exemple d’arbre B d’ordre 2:
L' arbre B+ est une variante de l'arbre B qui limite les pointeurs externes au seuls noeuds terminaux
(feuille de l'arbre); les noeuds intermédiaires n'ayant que des pointeurs internes. Par ailleurs des pointeurs
internes sont définis entre noeud de même niveau. Les principes de recherche et d'insertion sont les
même que pour l'arbre B.
Exemple d’arbre B+ d’ordre 4 :
« Ordre » d’un arbre B
Dans un arbre B d’ordre N, à l’exception de la racine, le nombre de clés par nœud est compris entre N et
2N ; et le nombre de fils d’un nœud est compris entre N+1 et 2N+1. Pour la racine le nombre de fils est
compris entre zéro et 2N+1.

Administration Base de Données 2011-2012
P.4
« Ordre » d’un arbre B+
Dans un arbre B+ d’ordre N, tout nœud interne possède un nombre de fils compris entre N/2 et N ; le
nombre de clés est toujours égal au nombre de fils -1.
Mise à jour du BTREE
En contre-partie, l'insertion d'une nouvelle valeur peut être relativement complexe et lourde. On cherche
à insérer la nouvelle clé dans le noeud terminal adéquate; si après cette insertion le nombre de clés du
noeud devient supérieur au maximum déterminé par l'ordre de l'arbre, il faut migrer la clé intermédiaire
au niveau supérieur de l'arbre et reprendre le processus d'insertion. La suppression d'une clé est d'une
complexité équivalente.
Autres types d’index dans les SGBDR
Le « hachage » (ou hashing en anglais) est une autre technique utilisée pour l’indexation. Elle consiste à
appliquer à la clé une fonction de calcul qui détermine une adresse relative pour stocker l’enregistrement
correspondant. Une méthode de chaînage vient compléter la technique en cas de collision (même résultat
de hachage pour des valeurs de clé différentes). Ce type d’index n’est pas adapté aux clés triées, et n’est
pas adapté à la recherche multicritère.
La technique du bitmap est destinée à indexer sur des attributs ayant peu de valeurs distinctes. On
associe un indice à chaque valeur possible. Le bitmap est constitué d’une matrice dans laquelle la
position (i,j), valorisée à 1, indique que l’enregistrement i possède la valeur d’indice j. L’index bitmap
est plus particulièrement utilisé pour indexer des attributs multivalués, pour indexer sur plusieurs
attributs, ou sur plusieurs valeurs d’un même attribut.
PostgreSql implémente 3 méthodes d’accès: BTREE par défaut, RTREE, et HASH
Oracle utilise l’arbre-B
+
, ainsi que le bitmap dans la mise en œuvre du datawarehouse (=entrepôt de
données à visée décisionnelle dans lequel les données ne sont plus modifiées).
Cluster : regroupement physique de lignes de plusieurs tables ayant un lien entre elles. L’objectif est de rendre
plus rapide l’accès à des données fréquemment accédées ensemble (jointure).
Le cluster apporte également un gain en stockage.
Exemple de regroupement de deux tables Pilote(numpil, nompil, prenpil, villpil, salairpil) et Vol(numvol,
numpil, numavion, villdep, villarr, heurdep, heurarr) sur l’attribut numpil
100 DUPONT PAUL NICE 4500
256 100 50 NICE PARIS 1100 1230
305 100 60 BORDEAUX PARIS 0900 1030
266 100 50 PARIS NICE 1900 2030
278 100 58 PARIS TOULOUSE 2000 2100
200 DUPONT PAUL NICE 4500
285 200 59 LYON PARIS 1100 1230
345 200 68 RENNES PARIS 0900 1030
286 200 59 PARIS LYON 1900 2030
208 200 99 PARIS MARSEILLE 2000 2100
Depuis la version10, un « hash cluster » relatif à une table unique peut être créé pour stocker ensemble
les lignes correspondant à une même valeur de la fonction de hashing associé. L’objectif demeure
toujours le même : accélérer l’accès aux données.
Schéma : c’est l’ensemble des objets logiques d’un utilisateur ou groupe d’utilisateurs
1 Bloc de
cluster

Administration Base de Données 2011-2012
P.5
1.2.2 - Structures logiques du SGBDR Oracle
Ces différentes structures logiques sont :
- les tablespaces
- les segments
- les extensions
- les blocs
Ces éléments permettent de définir la façon dont sont organisées les objets de la base de données.
Ils sont le lien entre le niveau physique et le niveau purement logique de la base de données.
Les Tablespaces
Une base de données Oracle est composée d’un certain nombre de partitions logiques appelées
Tablespace.
Un tablespace regroupe un ensemble d’objets logiques (table, index,...).Chaque objet logique de la base
doit être associé à un seul tablespace.
Une base de données doit avoir au moins un tablespace appelé SYSTEM qui contient le dictionnaire de
données.
Il est fortement conseillé de créer au moins un deuxième tablespace pour stocker les objets de la base.
Depuis la version 9, les tablespaces suivants sont fortement recommandés : TEMP, TOOLS, UNDO
Le tablespace est constitué d’au moins un fichier de données (Data File).
Depuis la version 8, la gestion des tablespaces peut être soit local (informations de stockage en en-tête du
tablespace), soit par le dictionnaire (informations de stockage dans le dictionnaire).
Exemples :
Créer un tablespace
Create tablespace data01
Datafile `g:\oracle\oradata\orafrance\data01.dbf` size 100M ;
Associer une table à un tablespace
Create table T-ex1 ( … ) tablespace data01;
Les Blocs
Oracle permet un contrôle fin de l’espace disque selon 3 niveaux de granularité : le bloc de données,
l’extension, et le segment.
BASE DE DONNEES
SYSTEM
Tablespace APPLI_1 TableSpace APPLI_2 TableSpace
SEGMENT
EXTENSION_1 EXTENSION_2
Bloc
Bloc
Bloc
Bloc
Bloc Bloc
Bloc Bloc
Bloc Bloc