INDEX Oracle SQL : Optimiser les performances des requêtes

INDEX Oracle SQL : Optimiser les performances de vos requêtes

L’index Oracle SQL est l’un des objets les plus puissants pour améliorer les performances d’une base de données. En permettant au moteur Oracle de localiser rapidement les données sans parcourir l’intégralité d’une table, un index Oracle peut réduire drastiquement le temps d’exécution des requêtes. Dans ce cours, vous apprendrez à créer, utiliser et optimiser vos index dans un environnement Oracle professionnel.

Définition et utilisation de l’index Oracle SQL

Un index Oracle SQL est une structure de données associée à une table (ou à un cluster) qui permet d’accélérer la récupération des lignes. Sans index, Oracle effectue un full table scan : il lit chaque ligne de la table pour trouver les données demandées. Avec un index, le moteur peut accéder directement aux blocs concernés, à l’image d’un index en fin de livre qui vous permet de retrouver un sujet sans tout relire.

Cas d’usage en entreprise

Les index sont omniprésents dans les systèmes d’information professionnels :

• Applications e-commerce : indexer la colonne EMAIL de la table CLIENTS pour accélérer les connexions utilisateurs.
• ERP et CRM : indexer les colonnes de jointure (CLIENT_ID, COMMANDE_ID) pour optimiser les rapports multi-tables.
• Datawrehouse : utiliser des index bitmap sur des colonnes à faible cardinalité (ex. : STATUT, REGION) pour des requêtes analytiques.
• Applications financières : indexer les colonnes de date pour des recherches sur des plages temporelles.

Types d’index Oracle

Oracle propose plusieurs types d’index adaptés à différents contextes :

• B-Tree (par défaut) : adapté aux colonnes à forte cardinalité (valeurs très variées).
• Bitmap : adapté aux colonnes à faible cardinalité dans un contexte décisionnel (OLAP).
• Unique : garantit l’unicité des valeurs dans une ou plusieurs colonnes.
• Composite : porte sur plusieurs colonnes combinées.
• Function-Based : construit sur le résultat d’une expression ou d’une fonction.

Syntaxe de la commande INDEX Oracle SQL

La syntaxe de base pour créer un index Oracle SQL est la suivante :

CREATE [UNIQUE | BITMAP] INDEX nom_index
ON nom_table (colonne1 [ASC | DESC], colonne2, ...)
[TABLESPACE nom_tablespace]
[ONLINE]
[COMPRESS n]
[PARALLEL n]
[INVISIBLE];

Explication des paramètres essentiels

• UNIQUE : garantit que deux lignes ne peuvent pas avoir la même valeur indexée.
• BITMAP : crée un index bitmap, recommandé pour les colonnes à faible cardinalité.
• nom_index : nom unique de l’index dans le schéma.
• nom_table : table sur laquelle l’index est créé.
• colonne1, colonne2 : colonnes indexées. L’ordre est important pour les index composites.
• ASC | DESC : ordre de tri (ascendant par défaut).
• TABLESPACE : espace de stockage dédié à l’index (bonne pratique de séparer index et données).
• ONLINE : permet la création de l’index sans bloquer les opérations DML en cours.
• COMPRESS : active la compression des entrées d’index pour économiser de l’espace.
• PARALLEL : utilise plusieurs threads pour accélérer la construction de l’index.
• INVISIBLE : l’index existe mais l’optimiseur Oracle ne l’utilise pas (utile pour les tests).

Pour supprimer un index :

DROP INDEX nom_index;

Pour rendre un index visible ou invisible :

ALTER INDEX nom_index INVISIBLE;
ALTER INDEX nom_index VISIBLE;

Exemples pratiques d’index Oracle SQL

Exemple 1 – Index B-Tree simple sur une table COMMANDES

Contexte métier : Dans une application de gestion commerciale, la table COMMANDES contient plusieurs millions de lignes. Les équipes commerciales filtrent très fréquemment par CLIENT_ID pour retrouver l’historique d’un client. Sans index, chaque requête provoque un full scan coûteux.

-- Création d'un index B-Tree sur la colonne CLIENT_ID de la table COMMANDES
-- Cet index accélérera les recherches et jointures sur CLIENT_ID

CREATE INDEX IDX_COMMANDES_CLIENT
ON COMMANDES (CLIENT_ID)
TABLESPACE INDX;

-- Vérification de la création de l'index dans le dictionnaire Oracle
SELECT INDEX_NAME, INDEX_TYPE, STATUS, UNIQUENESS
FROM USER_INDEXES
WHERE TABLE_NAME = 'COMMANDES';

-- Requête bénéficiant de l'index (Oracle utilisera un INDEX RANGE SCAN)
SELECT COMMANDE_ID, DATE_COMMANDE, MONTANT_TOTAL
FROM COMMANDES
WHERE CLIENT_ID = 10245;

Grâce à cet index, Oracle effectuera un Index Range Scan au lieu d’un Full Table Scan, ce qui peut diviser le temps d’exécution par 100 sur une table volumineuse.

Exemple 2 – Index composite et index basé sur une fonction

Contexte métier : Dans un système RH, les DRH recherchent fréquemment des employés par département ET par statut. Par ailleurs, des recherches insensibles à la casse sur le nom de famille sont récurrentes. On va créer deux index adaptés.

-- Index composite sur DEPARTEMENT_ID et STATUT
-- L'ordre des colonnes doit correspondre aux filtres les plus fréquents
-- DEPARTEMENT_ID en premier car il est utilisé seul dans certaines requêtes

CREATE INDEX IDX_EMP_DEPT_STATUT
ON EMPLOYES (DEPARTEMENT_ID, STATUT)
TABLESPACE INDX
COMPRESS 1;  -- Compression sur la première colonne pour réduire l'espace

-- Index basé sur une fonction pour les recherches insensibles à la casse
-- Utile lorsque les utilisateurs saisissent des noms en minuscules ou majuscules

CREATE INDEX IDX_EMP_NOM_UPPER
ON EMPLOYES (UPPER(NOM_FAMILLE))
TABLESPACE INDX;

-- Requête utilisant l'index composite (INDEX RANGE SCAN sur DEPARTEMENT_ID)
SELECT EMPLOYE_ID, NOM_FAMILLE, PRENOM, STATUT
FROM EMPLOYES
WHERE DEPARTEMENT_ID = 30
  AND STATUT = 'ACTIF';

-- Requête utilisant l'index function-based
-- IMPORTANT : la fonction dans WHERE doit correspondre exactement à celle de l'index

SELECT EMPLOYE_ID, NOM_FAMILLE, PRENOM
FROM EMPLOYES
WHERE UPPER(NOM_FAMILLE) = 'MARTIN';

Remarque importante : Pour qu’Oracle utilise un index basé sur une fonction, le paramètre QUERY_REWRITE_ENABLED doit être activé (valeur par défaut depuis Oracle 10g).

Erreurs courantes avec les index Oracle SQL

Erreur : L’index n’est pas utilisé par l’optimiseur

Problème : Vous avez créé un index sur la colonne NOM_FAMILLE, mais l’optimiseur Oracle continue d’effectuer un full table scan. En examinant le plan d’exécution (EXPLAIN PLAN), vous constatez que l’index est ignoré.

Causes fréquentes et solutions :

• Utilisation d’une fonction sur la colonne indexée : Si vous écrivez WHERE UPPER(NOM_FAMILLE) = 'MARTIN' mais que l’index est créé sur NOM_FAMILLE (sans fonction), Oracle ne peut pas utiliser cet index. Solution : créer un index basé sur la fonction UPPER(NOM_FAMILLE) comme montré dans l’exemple 2.
• Statistiques obsolètes : Si les statistiques de la table n’ont pas été recalculées après un chargement massif de données, l’optimiseur peut prendre une mauvaise décision. Solution :

  EXEC DBMS_STATS.GATHER_TABLE_STATS('MON_SCHEMA', 'EMPLOYES');

• Sélectivité insuffisante : Si la colonne indexée contient très peu de valeurs distinctes (ex. : SEXE avec seulement ‘M’ et ‘F’), l’optimiseur peut juger qu’un full scan est plus efficace. Dans ce cas, envisagez un index bitmap.
• Conversion implicite de type : Comparer une colonne NUMBER avec une chaîne de caractères provoque une conversion qui invalide l’utilisation de l’index. Veillez à toujours utiliser des types compatibles dans vos filtres.

Résumé des points clés sur l’index Oracle SQL

2 bonnes pratiques Oracle indispensables

1. Séparez les index et les données dans des tablespaces distincts. Stockez vos index dans un tablespace dédié (ex. : INDX) séparé du tablespace de données (USERS ou DATA). Cela facilite la maintenance, améliore les performances I/O et simplifie les sauvegardes.
2. N’indexez pas tout : privilégiez la qualité à la quantité. Chaque index consomme de l’espace disque et ralentit les opérations d’écriture (INSERT, UPDATE, DELETE), car Oracle doit maintenir tous les index à jour. Créez des index uniquement sur les colonnes réellement filtrées, triées ou utilisées dans des jointures fréquentes. Analysez vos plans d’exécution avec EXPLAIN PLAN pour valider l’utilité d’un index avant de le créer en production.

Aller plus loin avec Oracle SQL

Maintenant que vous maîtrisez les index Oracle SQL, approfondissez vos connaissances avec ces sujets complémentaires essentiels :

• EXPLAIN PLAN Oracle SQL : Apprenez à analyser les plans d’exécution de vos requêtes pour vérifier si vos index sont bien utilisés et identifier les goulets d’étranglement de performance.
• PARTITION Oracle SQL : Découvrez comment le partitionnement de tables et d’index (index partitionnés locaux et globaux) complète votre stratégie de performance sur les très grandes volumétries.
• Contraintes Oracle SQL (CONSTRAINT) : Comprenez la relation entre les contraintes d’unicité (UNIQUE, PRIMARY KEY) et la création automatique d’index par Oracle, pour éviter les doublons inutiles.