Oracle – 5) Indexing & partitionnement

5.1 Types d’index : B-tree vs Bitmap

Comparatif rapide

Type	Idéal pour	Éviter si
B-tree	OLTP, DML, lookup sélectif	colonne très faible NDV (souvent)
Bitmap	DW/OLAP, colonnes faible NDV, combinaisons	DML fréquent / concurrence forte

Règle :
B-tree = généraliste et sûr en OLTP.
Bitmap = très puissant en OLAP, dangereux en écriture.

OLTP : pourquoi bitmap peut “tuer”

Bitmap verrouille des “ranges” logiques → contention DML.
Sur tables très modifiées, tu risques des waits/concurrency.

Si tu as INSERT/UPDATE massifs + bitmap
=> forte probabilité de contention.

OLAP : bitmap = arme de sélection

Colonnes à faible NDV (status, type, région) : combinaisons rapides.
And/Or de bitmaps : très efficace pour filtrer gros volumes.

Pièges DBA

Créer un bitmap “parce que NDV faible” sans regarder DML.
Créer trop d’index (coût write / maintenance).
Index sur colonne jamais filtrée (inutile).

5.2 Index composites : ordre, sélectivité, covering

Ordre des colonnes (règle simple)

WHERE a = :a
  AND b = :b
  AND c BETWEEN :x AND :y
=> index (a,b,c)

Égalité(s) d’abord, puis range.
Si la 1ère colonne n’est pas filtrée, l’index sert moins (selon requêtes).

Sélectivité & cardinalité

Cas	Effet	Conseil
NDV élevé	bon filtre	index utile
NDV faible	filtre faible	OLTP: souvent non
Skew	ça dépend valeur	histo / ACS

Covering index (réduire table lookups)

Si les colonnes SELECT sont dans l’index → moins de bloc table.
Mais attention : index plus gros = DML plus cher.

Objectif :
réduire “table access by rowid”
quand c’est le vrai coût.

Checklist avant création

SQL_ID / plan prouve que l’accès est le problème ?
Colonne(s) vraiment filtrantes ?
Coût DML acceptable ?
Index redondant existant ? (prefixes)
Rollback prévu (drop) ?

5.3 Partitionnement : stratégies & design

Types de partition

Type	Quand	Exemple
RANGE	time-series / historisation	par mois
LIST	catégories stables	par région
HASH	répartir uniformément	par customer_id
Composite	range + hash/list	time + customer

Choisir la clé de partition

Doit apparaître dans les predicates des requêtes critiques (sinon pas de pruning).
Stabilité : une clé qui change = opérations coûteuses.
Volume équilibré (éviter partitions géantes).

Local vs Global indexes

Index	Avantage	Inconvénient
LOCAL	maintenance simple	parfois moins optimal cross-partition
GLOBAL	peut servir plusieurs partitions	fragile lors maintenance partitions

Si tu fais beaucoup d’opérations partition (drop/split/merge),
LOCAL est souvent plus “safe”.

Lifecycle : purge & archivage

Drop partition (purge) = très efficace.
Archive : exchange partition / tablespaces dédiés.
Compression selon âge (hot/warm/cold).

5.4 Partition pruning : le vrai accélérateur

Principe

Si le predicate filtre la clé de partition,
Oracle peut scanner seulement les partitions utiles
=> I/O drastiquement réduit.

Pruning statique (idéal)

Predicate simple sur clé : order_date >= :d AND order_date < :d+1
Résolution au parse → plan stable & efficace.

Pruning dynamique

Quand la valeur dépend d’un join/subquery → pruning déterminé à l’exécution.
Parfois moins efficace / plus variable.

Pourquoi ça ne prune pas

Fonction sur clé (TRUNC(date) = …) → predicate non sargable.
Conversion implicite (types)
Predicate absent sur clé (requêtes “globales”)

Astuce :
réécrire les predicates sur la clé de partition
pour garantir le pruning.

5.5 Volumétrie & maintenance (rebuild/coalesce/stats)

Fragmentation : le mythe “rebuild tout”

Un rebuild systématique est souvent inutile et coûteux.
Mesurer : taille segment, performance, clustering, leaf blocks, etc.

Rebuild “par habitude” = dette.
Rebuild “par preuve” = engineering.

Rebuild vs Coalesce

Opération	Avantage	Limite
COALESCE	moins intrusif	ne compresse pas tout
REBUILD	réorganise complètement	coûteux, impact DML

Stats : partitions & global

Sur partitioned tables : stats partition + global cohérentes.
Après maintenance partitions : recalcul stats nécessaires.

Online operations

Privilégier opérations online quand prod critique.
Mais mesurer le coût : online = pas “gratuit”.

5.6 Checklist “Schema tuning” (safe)

Avant de toucher au schéma

SQL_ID + plan + métriques (preuve)
Identifier l’opérateur dominant (I/O, join, sort)
Vérifier index existants (redondance)
Vérifier prédicats (sargables / pruning)

Change (planifié)

Créer index en ONLINE si possible (sinon fenêtre).
Partition ops (split/merge/drop) en fenêtre contrôlée.
Documenter “pourquoi” (sinon dette).

Valider

Comparer :
- plan hash
- A-Rows / buffers
- elapsed / cpu / reads
- impact DML (si OLTP)

Rollback

Rollback options :
- drop index (si inutile)
- revert partitions change (si possible)
- restore stats
- disable baseline/profile si lié

A · Diagrammes (Mermaid-ready)

Décision index (simplifiée)

flowchart TD
  Q[SQL lent] --> P[Lire plan + métriques]
  P --> A{Opérateur dominant = accès ?}
  A -- Non --> O[Autre piste: join/sort/waits]
  A -- Oui --> S{Predicate sargable ?}
  S -- Non --> R[Réécrire predicate]
  S -- Oui --> C{Sélectivité bonne ?}
  C -- Non --> N[Éviter index / autre stratégie]
  C -- Oui --> I[Créer index ciblé (composite)]
  I --> V[Valider avant/après + DML impact]

Partitions & pruning

flowchart TD
  T[Table volumineuse] --> K{Filtres sur date/clé ?}
  K -- Oui --> R[Partition RANGE sur date]
  R --> P[Predicate sargable sur clé]
  P --> PR[Pruning -> scan réduit]
  K -- Non --> H[Hash/List selon usage]

B · Commandes & vues (index / partitions / tailles)

Index : inventory & stats

SELECT owner, index_name, table_name, uniqueness, status, partitioned
FROM dba_indexes
WHERE owner = UPPER(:owner)
ORDER BY table_name, index_name;

SELECT owner, index_name, num_rows, distinct_keys, clustering_factor, last_analyzed
FROM dba_ind_statistics
WHERE owner = UPPER(:owner)
ORDER BY last_analyzed DESC NULLS LAST;

Partitions : état & volumétrie

SELECT table_owner, table_name, partition_name, high_value, num_rows, blocks, last_analyzed
FROM dba_tab_partitions
WHERE table_owner = UPPER(:owner)
  AND table_name  = UPPER(:table)
ORDER BY partition_position;

SELECT index_owner, index_name, partition_name, status
FROM dba_ind_partitions
WHERE index_owner = UPPER(:owner)
  AND index_name  = UPPER(:index)
ORDER BY partition_position;

Tailles segments (tables/index)

SELECT owner, segment_name, segment_type,
       ROUND(bytes/1024/1024) AS mb
FROM dba_segments
WHERE owner = UPPER(:owner)
ORDER BY bytes DESC FETCH FIRST 30 ROWS ONLY;

Usage index (à confirmer selon config)

-- Exemple d’approche :
-- 1) identifier top SQL
-- 2) DBMS_XPLAN + predicates
-- 3) vérifier si index apparaît réellement dans les plans exécutés

L’“index monitoring” peut dépendre de la version/options : rester pragmatique (plans exécutés + AWR/ASH).

C · Runbook : index / partition change (safe)

Hypothèse (à écrire noir sur blanc)

- SQL_ID / transaction :
- Problème prouvé : accès / pruning / DML cost ?
- Changement proposé : index / partition / local/global
- Risque : DML, locks, space, maintenance

Test

1) Capturer plan + métriques AVANT
2) Appliquer changement en staging
3) Rejouer workload représentatif
4) Vérifier :
   - plan hash
   - A-Rows / buffers
   - elapsed / cpu / reads
   - impact DML

Déploiement

Fenêtre / online operations
Surveillance : waits, redo, CPU, I/O
Vérifier que le plan exécuté utilise réellement l’index/pruning

Rollback

- DROP index (si safe)
- Revert partition ops (si possible)
- Restore stats si modifiées
- Désactiver baseline/profile si associé

🗂️ 5) Indexing & partitionnement

Types d’index : B-tree vs Bitmap

Index composites : ordre & sélectivité

Partitionnement : stratégies & design

Partition pruning : le vrai “gain”

Volumétrie & maintenance

Checklist “schema tuning” (safe)

Diagrammes (Mermaid-ready)

Commandes & vues (index/partitions)

Runbook (template) : index / partition change