Tablespaces, Indexes , Clusters

1. Sur chaque OS, Oracle redéfinit son système de fichiers

Le niveau physique du SPARC, c'est cette dégringolade en cascade : Base → Tablespace → Fichier → Segment → Extension → Bloc. C'est six étages de granularité, comme une coupe géologique.

La pile de stockage Oracle, depuis la base entière jusqu'au bloc physique sur disque.

📐 Mnémo : « BTF SEB »
Base · Tablespace · Fichier · Segment · Extension · Bloc
Du plus gros au plus petit. Le bloc logique Oracle ne correspond pas toujours à un bloc physique de l'OS — Oracle redéfinit son propre système de fichiers par-dessus celui de l'OS.

Ce chapitre traite du niveau physique. Il aborde trois grandes questions :

Stockage — où vivent les données ? (segments, fichiers)
Accélérateurs — comment chercher plus vite ? (indexes, clusters)
Optimisation — comment Oracle choisit son plan de requêtes ?

2. Les cinq familles de segments

Un segment est le contenu d'un objet stocké : chaque table, chaque index, chaque cluster a son segment. Oracle en distingue cinq familles, chacune avec son rôle propre.

💾 Données

Tables utilisateurs et système + tables groupées (clusters). Un et un seul segment par table, créé auto à la création de la table.

🗂️ Index

Données des index. Peuvent être stockés dans un tablespace distinct de celui des données — précisable à la création.

⏱️ Temporaire

Pour les requêtes nécessitant un espace disque temporaire (tri, hash). Créés au besoin, supprimés ensuite. Défini sur l'user, sinon SYSTEM.

🔓 Amorçage

Créé dans le tablespace SYSTEM. Contient les définitions du dictionnaire de données, chargées à l'ouverture de la base.

↩️ Rollback

Contient les données avant modification. Permet d'annuler l'effet d'une transaction en cas de besoin.

-- Définir le tablespace temporaire d'un user
CREATE USER … TEMPORARY TABLESPACE …

3. Les trois fichiers d'Oracle

📄 Fichiers de données (.dbf)
Tout le contenu de la base : tables, vues, procédures stockées…

📜 Fichiers Redo Log (.rdo / .log)
Historique des modifications effectuées sur la BD. Indispensable à la reprise après panne.

🎛️ Fichiers de contrôle (.ctl, .dbf)
État de la base : emplacement des fichiers, dates de création…

Minimum requis Une base Oracle a besoin d'au moins 1 fichier de données + 2 fichiers Redo Log + 1 fichier de contrôle. Tous référencés dans un fichier d'initialisation, généralement init.ora.

4. Accélérateurs : les indexes

Une sélection sur une grande table peut être longue. Indexer une table sur une colonne, c'est créer une seconde structure triée qui pointe vers les lignes de la table principale.

✅ Avantage

Recherche bien plus rapide quand la clause WHERE fait intervenir la colonne indexée.

⚠️ Coût

À chaque MAJ de la table, l'index doit aussi être mis à jour. La mise à jour est donc plus longue.

La sélectivité — la métrique clé

Sélectivité = Nombre de valeurs distinctes de l'index Nombre total de lignes de la table

Sélectivité faible — peu de valeurs distinctes (ex. une colonne « sexe » avec H/F).
Sélectivité forte — beaucoup de valeurs distinctes.
Sélectivité = 1 — chaque valeur est unique (cas d'un index sur clé primaire).

Index B-TREE — l'arbre équilibré (défaut)

équilibré · les feuilles sont à la même profondeur

L'arbre balancé : tri hiérarchique des clés, accès en O(log n).

🚫 Deux faits cruciaux du B-tree
① Il est d'autant plus rentable que la sélectivité est élevée.
② Il ne gère pas les valeurs NULL. Donc il n'est pas utilisé pour rechercher des t-uples non renseignés sur les colonnes indexées.

Index BITMAP — la table de présences

L'idée du bitmap : une colonne binaire par valeur distincte. Comme un tableau de présences. Exemple du cours sur le Titanic, où la colonne Classe a 4 valeurs possibles :

L'index bitmap : une ligne par valeur distincte, un bit par tuple. Combinatoire ultra rapide en AND/OR.

🎯 Quand utiliser un bitmap ? (les 3 conditions ensemble)
① Faible sélectivité des colonnes indexées
② Beaucoup de lignes dans la table
③ Très peu d'activités de mise à jour

🆚 Différences clés avec le B-tree
• MAJ d'une ligne ⇒ reconstruction totale de l'index bitmap (coûteux !)
• NULL est une valeur comme une autre ⇒ l'index gère les NULL.

Syntaxes Oracle de création

-- Index B-TREE (par défaut)
CREATE [UNIQUE] INDEX nomIndex
  ON Nom_de_la_table (NomChamp [ASC/DESC], …)
TABLESPACE nomTablespace;
-- UNIQUE impose des valeurs distinctes d'une ligne à l'autre

-- Index B-TREE INVERSÉ : la valeur 'abc' est indexée avec 'cba'
CREATE [UNIQUE] INDEX nomIndex
  ON Nom_de_la_table (NomChamp …) REVERSE;

-- Annuler l'inversion
ALTER INDEX nomIndex REBUILD NOREVERSE;

-- Index par fonction
CREATE INDEX nomIndex
  ON Nom_de_la_table (f(NomChamp) [ASC/DESC], …);

⚠️ Attention — la fonction doit être déterministe Une série de valeurs en entrée ne correspond qu'à une seule valeur en sortie. À la déclaration : CREATE FUNCTION … RETURN xxx DETERMINISTIC. Et SYSDATE n'est pas déterministe.

-- Index BITMAP (uniquement en version Entreprise)
CREATE BITMAP INDEX nomIndex
  ON Nom_de_la_table (NomChamp …);

5. Accélérateurs : les clusters

Dans un MCD, les associations (1,1) (1,n) sont très fréquentes. En BD, elles sont traitées par une clé étrangère côté (1,1) qui est la clé primaire côté (1,n).

🧩 Les classiques
• Une facture est associée à un client
• Une ligne de commande est associée à une commande
Vocabulaire : on parle de relations Maître – Détails.

Le problème — et la solution

Jointure = aller-retour disque ⤵ Client id=1 · A. id=2 · B. id=3 · C. id=4 · D. Facture f1 · cli=2 f2 · cli=1 f3 · cli=3 f4 · cli=2 f5 · cli=4 f6 · cli=1 f7 · cli=3 f8 · cli=2 😵 7 sauts disque ! ✓ Avec cluster Les lignes maître+détails partageant
le même id sont stockées côte à côte. Cluster sur cli_id id=1 · A. f2 · cli=1 f6 · cli=1 id=2 · B. f1 · cli=2 f4 · cli=2 f8 · cli=2 id=3 · C. f3 · cli=3 f7 · cli=3 id=4 · D. f5 · cli=4 🚀 Lecture séquentielle

Cluster = stockage colocalisé des lignes Maître+Détails partageant la même clé.

Trois propriétés à retenir

Un cluster permet de stocker des tables ayant des colonnes en commun. Il remplace le tablespace.
Les lignes de tables différentes ayant les mêmes valeurs sur les colonnes communes sont proches sur le disque dur.
Les colonnes communes ne sont pas physiquement dupliquées. Un index de cluster sur ces colonnes est créé.

Syntaxe

CREATE CLUSTER Nom(col1 type1 [col2 type2 …])
  SIZE tailleBloc TABLESPACE nomTablespace;

CREATE TABLE nomTable(…) CLUSTER nomCluster(colT1, [colT2 …]);
-- où colTi sont des colonnes de la table

-- Crucial : créer l'index de cluster, sinon rien ne marche !
CREATE INDEX nomIndex ON CLUSTER nomCluster;

Mise en cluster d'une table existante

Créer une nouvelle table avec l'option CLUSTER
Copier le contenu de la table initiale
Supprimer l'ancienne table
Éventuellement renommer la nouvelle

⚠️ Sans index sur le cluster, rien ne fonctionne Une fois le cluster créé, il faut absolument créer l'index sur les colonnes communes, sinon on ne peut rien faire sur les tables.

★ Réviser le chapitre

Pour vérifier ta compréhension

Quelle est la différence concrète entre un tablespace et un segment ?

Le tablespace est un conteneur logique qui regroupe un ou plusieurs fichiers. Le segment est l'occupation effective d'un objet (table, index, cluster) à l'intérieur d'un tablespace. Un tablespace contient plusieurs segments ; un segment vit dans un seul tablespace.

Pourquoi un B-tree perd-il son intérêt sur une colonne du genre « sexe » (H/F) ?

La sélectivité est extrêmement faible (2 valeurs pour potentiellement des millions de lignes). Le B-tree va devoir descendre l'arbre puis remonter plein de rowids pointant vers ~50% de la table — pas plus rapide qu'un full scan. C'est typiquement le cas où on utilise un index bitmap.

Pourquoi déconseille-t-on un bitmap sur une table très souvent mise à jour ?

Parce qu'une modification d'une ligne entraîne la reconstruction totale de l'index bitmap. Sur une table OLTP avec milliers d'inserts/updates par minute, c'est un suicide en performance.

Quand peut-on décider de mettre en place un cluster ?

Quand on a une relation Maître–Détails (associations (1,1)–(1,n)) sur laquelle on fait souvent des jointures naturelles, et que ces jointures coûtent cher. Le cluster colocalise les lignes par clé commune, transformant une jointure aléatoire en lecture séquentielle.

Pourquoi SYSDATE ne peut pas servir dans un index par fonction ?

Parce que SYSDATE n'est pas déterministe : son résultat varie d'un instant à l'autre. Un index par fonction exige que la fonction produise toujours la même sortie pour la même entrée — sinon l'index pointe vers du vide.

🃏 Flashcards éclair

Clique pour retourner.

Hiérarchie stockage

Du plus gros au plus petit ?

tourne →

Base · Tablespace · Fichier · Segment · Extension · Bloc
(mnémo : BTF · SEB)

5 segments

Cite-les.

tourne →

Données · Index · Temporaire · Amorçage · Rollback (annulation)

3 fichiers Oracle

Avec extensions.

tourne →

Données .dbf · Redo Log .rdo/.log · Contrôle .ctl
Min : 1+2+1

Sélectivité

Formule.

tourne →

Nb valeurs distinctes / Nb lignes
1 = clé primaire (max)

B-tree

2 limites majeures.

tourne →

① Sélectivité doit être élevée
② Ne gère pas les NULL

Bitmap

3 conditions d'usage.

tourne →

① Faible sélectivité
② Beaucoup de lignes
③ Très peu de MAJ

Bitmap vs NULL

Le bitmap gère-t-il NULL ?

tourne →

Oui. NULL est une valeur comme une autre pour le bitmap.
(Contrairement au B-tree)

Index B-tree inversé

Que fait-il ?

tourne →

La valeur 'abc' est indexée comme 'cba'. Utile pour répartir les insertions séquentielles.

Index par fonction

Contrainte sur la fonction ?

tourne →

Elle doit être DETERMINISTIC.
SYSDATE n'est pas déterministe.

Cluster

Ce qu'il remplace, ce qu'il faut en plus.

tourne →

Remplace le tablespace.
Requiert un index de cluster sur les colonnes communes.

Mise en cluster

Les 4 étapes.

tourne →

① Nouvelle table CLUSTER
② Copier le contenu
③ Drop l'ancienne
④ Renommer

Créer un index

Syntaxe générique.

tourne →

CREATE [UNIQUE] INDEX nom
ON tbl(col [ASC/DESC])
TABLESPACE ts;

✎ Quiz éclair

test rapide · 6 questions

0 / 6

1.Une table contient une colonne statut avec 4 valeurs distinctes pour 2 millions de lignes, peu d'inserts. Quel index ?

B-tree
B-tree inversé
Bitmap
Index par fonction

Faible sélectivité (4/2M) + peu d'updates ⇒ Bitmap, exactement le profil défini par le cours.

2.Quelle granularité de stockage est la plus fine ?

Segment
Bloc physique
Extension
Tablespace

Hiérarchie : Base → Tablespace → Fichier → Segment → Extension → Bloc.

3.Un B-tree perd son intérêt si :

La colonne est de type chaîne
Le tablespace est plein
La requête contient un GROUP BY
La sélectivité est très faible

Le B-tree est d'autant plus rentable que la sélectivité est élevée. Faible sélectivité = pas mieux qu'un full scan.

4.Pour qu'une fonction puisse servir dans un index par fonction, elle doit :

Être déclarée dans un package
Retourner un type numérique
Être DETERMINISTIC
Être créée par le DBA

Une fonction DETERMINISTIC retourne toujours la même valeur pour la même entrée. SYSDATE par exemple ne l'est pas.

5.Quel est le minimum de fichiers requis pour une base Oracle ?

1 données · 1 redo · 1 contrôle
1 données · 2 redo · 1 contrôle
2 données · 1 redo · 2 contrôle
1 par utilisateur connecté

Minimum : 1 + 2 + 1. Les deux redo logs servent à la rotation.

6.Après avoir créé un cluster, que doit-on faire avant de pouvoir utiliser les tables ?

Lancer ANALYZE CLUSTER
Drop le tablespace par défaut
Forcer un COMMIT
Créer l'index sur le cluster

Sans CREATE INDEX ON CLUSTER …, aucune opération n'est possible sur les tables clustérisées.

📌 Pour la feuille recto-verso

Hiérarchie : Base → Tablespace → Fichier → Segment → Extension → Bloc (BTF·SEB)
5 segments : Données · Index · Temporaire · Amorçage · Rollback
3 fichiers : .dbf · .rdo/.log · .ctl — min 1+2+1
Sélectivité = nb valeurs distinctes / nb lignes
B-tree : forte sélectivité, ne gère pas NULL
Bitmap : faible sélectivité, beaucoup de lignes, peu de MAJ, gère NULL
MAJ d'une ligne ⇒ reconstruction totale d'un bitmap
Index par fonction : fonction DETERMINISTIC obligatoire (SYSDATE NON)
CREATE [UNIQUE] INDEX nom ON tbl(col) [REVERSE]
CREATE BITMAP INDEX — Enterprise Edition uniquement
Cluster = optimisation jointure Maître/Détails, remplace le tablespace
Cluster requiert un CREATE INDEX ON CLUSTER nomCluster
Mise en cluster d'une table existante : 4 étapes (nouv. table CLUSTER → copy → drop → rename)