14.3.2. Insertion

3/2 Les mécanismes d'optimisation de gestion d'une base Oracle 8i
Plus le volume des données à gérer augmente, plus il devient nécessaire d'intervenir physiquement
sur la façon dont le SGBD va stocker et organiser les données. Certaines bases de données
décisionnelles peuvent dépasser des volumes de l'ordre de plusieurs dizaines de téraoctets (un
téraoctet est égal à mille gigaoctets), par exemple dans le domaine de la grande distribution où les
systèmes décisionnels historisent sur de longues périodes le détail des tickets de caisses, afin de
mener des études sur le comportement des clients.
Avec les versions antérieures d'Oracle
Il fallait gérer l'optimisation physique des données en adaptant le modèle de données logique :


– le partitionnement était réalisé en créant des tables distinctes, dont on pouvait avoir une
vision globale à l'aide de vues ;
– les tables agrégées étaient gérées par l'administrateur de la base de données, qui devait
créer des procédures spécifiques pour les alimenter et les rafraîchir.
Le serveur Oracle 8i
Il permet d'automatiser toutes ces tâches et les rendre transparentes pour les utilisateurs. Pour
optimiser la gestion des bases de données décisionnelles, le serveur Oracle 8i offre plusieurs types de
mécanismes, dont trois vont être illustrés ci-dessous :



– l'indexation ;
– les vues matérialisées ;
– le partitionnement.
3/2.1 L'indexation
L'indexation des données devient absolument indispensable dès que le volume d'une table atteint un
millier de lignes. En effet, interroger une table en posant une condition sur une colonne non indexée
provoque une lecture séquentielle totale de la table. Le moteur Oracle 8i offre désormais plusieurs
stratégies d'indexation, à sélectionner en fonction du type de données et du type d'application.
3/2.1.1 Les index « B-Tree »
Format d'index par défaut
Les index « B-Tree » sont le format d'index par défaut offert par le moteur Oracle. Ils sont construits
sur le principe des « arbres balancés », structure organisant de manière hiérarchique les pages
d'index.
Schéma de la structure d'un index
Le schéma ci-dessous représente la structure d'un index unique posé sur l'attribut « numéro de
produit » d'une table « produits » :
Si une requête interroge le produit numéro 21800, le serveur Oracle ira lire le sommet de la hiérarchie
(le bloc « racine ») afin de déterminer le bloc de second niveau à lire par la suite. La lecture du bloc de
second niveau « approprié » indiquera selon le même principe le bloc de troisième niveau, et ainsi de
suite jusqu'à l'obtention de la clé du produit demandé.
Chaque page d'un B-arbre doit contenir entre n et 2n noeuds, n étant une constante arbitraire.
Cela signifie qu'une page doit toujours être au moins à moitié remplie. Seule la première
(racine) peut faire exception à cette règle et peut même, éventuellement, ne contenir qu'un
seul noeud.
En respectant cette contrainte, nous allons formuler des algorithmes simples pour la
recherche, l'insertion et la suppression d'éléments telles que la structure de données ainsi
définie (B-arbre) possède les caractéristiques suivantes (où n est l'ordre du B-arbre) :
1. chaque page contient, au plus, 2n éléments
2. chaque page, sauf la racine, contient au moins n éléments
3. chaque page est soit une feuille (pas de descendants), soit elle possède m+1
descendants (si elle contient m éléments)
4. toutes les pages feuilles sont au même niveau.
La figure suivante illustre un B-arbre d'ordre 2 avec 3 niveaux :
Fig. 14.9. Exemple de B-arbre d'ordre 2.
Chaque page contient 2, 3 ou 4 éléments, sauf la page racine qui, dans cet exemple, n'en
contient qu'un seul. Toutes les pages feuilles sont au niveau 3.
14.3.2. Insertion
D'une manière générale, l'insertion d'un nouvel élément dans un B-arbre est relativement
simple. On effectue les mêmes étapes que pour la recherche d'un élément jusqu'à ce que l'on
arrive à une page feuille où l'élément à insérer aurait pu se trouver s'il était présent dans la
structure. On essayera alors d'insérer l'élément dans la page (feuille) en question.
Si un élément doit être inséré dans une feuille contenant moins de 2n éléments, l'insertion
s'effectuera à l'intérieur de la page, sans autre difficulté. Si la page est pleine et qu'une feuille
adjacente possède une place libre, on effectuera un transfert entre les deux feuilles, en tenant
compte de l'élément charnière qui se trouve dans la page parente. Pour prendre un exemple,
supposons que nous ayons le B-ar bre de la figure 14.10 :
Fig. 14.10. Exemple de B-arbre d'ordre 2.
et que l'on désire insérer la valeur 12. Cette valeur devrait être insérée dans la page B. On va
donc déplacer l'élément charnière (20) de la page parente (A) vers la page adjacentes à la page
B et possédant une place libre (page C). La place libérée en page A par le déplacement de
l'élément charnière va maintenant accueillir l'élément de la page B qui lui est directement
inférieur (18). La page B contient alors une place libre qui peut recevoir la nouvelle valeur à
insérer (12). On aboutit enfin à la structure suivante :
Fig.14.11. Insertion par rocade d'éléments
Par contre, l'insertion d'un nouvel élément dans une feuille pleine avec des pages adjacentes
pleines également a des conséquences sur la structure globale, dans la mesure où il faut
allouer de nouvelles pages.
Pour bien comprendre ce qui se passe dans un tel cas, considérons le B-arbre d'ordre 2 de la
figure 14.12 :
Fig. 14.12. B-arbre de départ pour insertion dans une page pleine.
On désire insérer l'élément 22 dans ce B-arbre. L'insertion dans la page C (page devant
contenir 22) n'est pas possible, car la page est déjà pleine (elle contient 2n éléments). On
effectue alors les opérations suivantes :
1. on prépare de la place en créant une nouvelle page (D) au même niveau que B et C.
2. on répartit les 2n+1 éléments (les 2n de C et le nouveau) de la manière suivante :
a. l'élément "milieu" (c'est-à-dire le n+1-ème des 2n+1 éléments, soit 30 dans
notre exemple) est déplacé vers la page parente (A), où il joue le rôle de
charnière entre la page C et la page D
b. les éléments plus petits que l'élément milieu (22 et 26) sont placés dans la page
C
c. les éléments plus grands que l'élément milieu (35 et 40) sont placés dans la
page D.
On aboutit ainsi au B-arbre suivant :
Fig. 14.13. Insertion par création d'une nouvelle page.
Ce mécanisme préserve toutes les propriétés des B-arbres. En particulier, la division d'une
page aboutit à la création de 2 pages contenant exactement n éléments. Il est possible que
l'insertion de l'élément "milieu" dans la page parente puisse, à son tour, provoquer un
dépassement de capacité (si la page parente était pleine) et nécessiter un autre découpage. Le
découpage peut ainsi se propager de niveau en niveau et, éventuellement, atteindre la racine
elle-même. C'est d'ailleurs la seule façon, pour un B-arbre, d'augmenter de profondeur (nb. de
niveaux). Contrairement aux autres structures arborescentes, un B-arbre croît donc à partir des
feuilles, vers sa racine.
Exercice B :
Construire un index sur la date de naissance des musicien (arbre B, ordre 2) :
Monteverdi
Couperin
Bach
Rameau
Debussy
Ravel
Mozart
Faure
1589
1668
1685
1684
1862
1875
1756
1856
Exercice C :
Construire un index sur les noms des musicien (arbre B, ordre 2).
3/2.1.2 Les index « Bitmap »
Avantages des index « Bitmap »
Les index « Bitmap » sont issus de la technologie des bases de données documentaires. Leur
utilisation est préconisée quand il s'agit d'indexer des données dont les valeurs ne prennent qu'un
petit nombre de valeurs discrètes. Dans ce cas, leur structure spécifique offre plusieurs avantages :



– faible occupation d'espace physique ;
– création et réorganisation rapides ;
– optimisation du temps de réponse des requêtes portant sur ces index.
Alors qu'un index « B-tree » contiendra autant de lignes que la table de données sur laquelle il
s'applique, le nombre de lignes d'un index « Bitmap » correspondra au nombre de valeurs distinctes
de la valeur indexée.
Technique de stockage des index « Bitmap »
Physiquement, un index « Bitmap » stocke pour chaque valeur distincte de l'index un champ binaire
contenant autant de bits que de lignes dans la table de données. Ce champ binaire est constitué d'une
suite de bits, chaque bit correspondant à une ligne de la table de données (avec la valeur 1 si la ligne
contient la valeur de l'index, 0 sinon). De plus, la taille de ce champ binaire est optimisée par le
moteur Oracle afin que des longues suites de zéros puissent être compressées.
Exemple :
L'exemple ci-dessous permet d'illustrer cette technique :
Voici une table « employés », avec une clé primaire, le no d'employé, et quelques attributs à faible
cardinalité (les colonnes Sexe, Statut, Temps partiel et Service).
No
Nom
Sexe
Statut
Temps partiel
Service
1234
Martin
H
Cadre
N
Comptabilité
5845
Dupond
F
Employé
N
Informatique
4785
Dupont
F
Employé
O
Commercial
6985
Peron
H
Employé
N
Comptabilité
4115
Durand
F
Cadre
O
Commercial
8021
Michel
H
Directeur
N
Informatique
325
Thomas
H
Cadre
O
Informatique
8921
Loiseau
F
Employé
N
Commercial
8010
Boileau
H
Employé
N
Comptabilité
401
Maréchal
F
Employé
N
Comptabilité
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Si l'on décide d'indexer ces attributs, l'utilisation des index « Bitmap » s'impose naturellement.
On obtiendrait alors les quatre index ci-dessous :

l'index Sexe :
H
1001011010 ...
F
0110100100 ...


l'index Statut :
Cadre
1000101000 ...
Employé
0111000111 ...
Directeur
0000010000 ...


l'index Temps partiel :
Oui
0010101000 ...
Non
1101011111 ...


l'index Service
Informatique
0100011000 ...
Comptabilité
1001000011 ...
Commercial
0010100100 ...

Ainsi, lors de l'exécution d'une requête mettant en jeu des conditions portant sur ces attributs, le
serveur Oracle utilisera directement ces index « Bitmap ».
Par exemple, si l'on demande « Quels sont les salariés Hommes, de statut cadre et travaillant à temps
partiel ? », le plan d'exécution de la requête s'appuiera sur l'opération suivante :
Le résultat de la requête indique que c'est le tuple correspondant à la 7e ligne de la table qui satisfait
la condition demandée.
Si ces mêmes index avaient été structurés de manière standard (format « B-Tree »), le serveur Oracle
aurait dû parcourir une grande partie des branches de l'arborescence de chacun des index avant de
fournir le résultat. Cette méthode de résolution est bien évidemment bien plus consommatrice en
ressources machines.
Manipulation de tables volumineuses
Les index « Bitmap » sont fréquemment utilisés dans le cadre d'applications décisionnelles qui
manipulent fréquemment des tables volumineuses (plusieurs dizaines de millions de lignes).
Taille des index « bitmap » et « B-tree »
Le graphe suivant (cf. Fig. 1) indique la taille physique des index « bitmap » et « B-tree » pour une
table d'un million de lignes, en fonction de la cardinalité de la valeur indexée :
Occupation des index « B-tree » et « Bitmap ».
Alors que dans une structure « B-tree », l'espace physique occupé par l'index est quasiment invariant
quelle que soit la cardinalité de la valeur indexée, dans la structure « Bitmap », la taille de l'index est
directement corrélée au nombre de valeurs distinctes.
On considère généralement qu'un index « Bitmap » peut être utilisé à profit jusqu'à un degré de
cardinalité d'environ 10 % (100 000 valeurs distinctes sur une table d'un million de lignes).
Syntaxe SQL :
3/2.1.3 Les tables « index »
Le principe de stockage
Le principe est de stocker les données de la table directement dans l'arbre « B-tree » de l'index. Ainsi,
au lieu de gérer deux espaces de stockage (un pour la table et un pour l'index), le serveur Oracle
effectue tous les traitements SQL sur un unique index de type « B-tree », qui contient à la fois la
valeur de la clé primaire et les valeurs des attributs correspondant à la ligne de données.
Avantages
Cette structure de stockage offre deux grands avantages :

– les requêtes portant sur la clé primaire s'exécutent plus rapidement (le serveur Oracle
n'accède qu'à l'index) ;

– l'espace de stockage requis est moindre (la clé primaire n'est pas dupliquée à la fois dans la
table et dans l'index).
Manipulation de la table « index »
D'un point de vue utilisateur (ou développeur), la table « index » se manipule comme une table
ordinaire. Sa principale contrainte est de ne pas autoriser de clés secondaires.
Syntaxe SQL :