Index organized table Oracle : performances, exemples et cas d’usage

Vous vous demandez quand une index-organized table d’Oracle va vraiment doper vos requêtes… et quand, au contraire, elle risque de plomber vos insertions ou vos index secondaires ? Vous êtes au bon endroit. Dans les lignes qui suivent, on démonte le moteur des IOT : schémas SQL, scripts de diagnostic, ordres de grandeur chiffrés… tout ce qu’il faut pour trancher, en toute connaissance de cause, entre table heap classique et index-organized table.

Quelques pages plus loin, vous saurez non seulement définir une IOT, la débusquer dans votre base, la créer ou la migrer depuis une heap, mais aussi repérer les scénarios où elle accélère franchement vos traitements… et ceux où il vaut mieux passer son chemin.

Index-Organized Table (IOT) : le guide complet pour booster vos performances Oracle

1. Qu’est-ce qu’une index-organized table ? Définition et concepts clés

1.1 Structure logique et physique d’un IOT

Une index-organized table (IOT) n’a rien à voir avec les tables « heap » habituelles : au lieu de déposer ses lignes dans un segment de données, Oracle les range directement dans un index B-tree fondé sur la clé primaire.

En une phrase :

Définition (version « snippet-friendly ») :
Une index-organized table Oracle stocke et ordonne physiquement ses lignes à l’intérieur d’un index B-tree construit sur la clé primaire ; il n’existe donc plus de segment de table séparé.

Les conséquences sautent aux yeux :

• Données physiquement triées par clé primaire.
• Pas de ROWID physique permanent : les lignes peuvent bouger au gré du B-tree.
• Un SELECT par clé primaire se contente d’un seul parcours d’index.
• Le segment de table disparaît ; seul subsiste un segment d’index (et éventuellement un segment d’overflow).

C’est donc toujours un B-tree, avec :

• des blocs de branche qui pointent vers les blocs fils,
• des blocs feuilles contenant à la fois la clé primaire et la ligne (ou un pointeur vers l’overflow).

1.2 IOT vs table heap : pourquoi l’index passe devant la ligne

Impossible de saisir l’intérêt d’une IOT sans la comparer à la bonne vieille table heap.

Dans une heap :

• Les lignes sont insérées où il reste de la place ; aucun ordre imposé.
• L’index B-tree sur la clé primaire contient la clé… et un ROWID qui renvoie vers la table.

Résultat : chaque lookup par clé primaire implique deux sauts :

1. INDEX RANGE SCAN dans l’index,
2. TABLE ACCESS BY INDEX ROWID dans la pile de blocs de la table.

Accès index-based sur table heap (équivalent Figure 5.2)

Exemple de requête :

SELECT eur_value
FROM   sales_heap
WHERE  sale_date = DATE '2012-05-23';

Plan simplifié :

--------------------------------------------------------------------------------
| Id  | Operation                    | Name          |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |
|   1 |  TABLE ACCESS BY INDEX ROWID | SALES_HEAP    |
|*  2 |   INDEX RANGE SCAN           | SALES_DATE_IX |
--------------------------------------------------------------------------------

Chaque entrée trouvée dans l’index déclenche un saut vers le segment de table.

Côté IOT :

• La ligne réside déjà dans le B-tree de la clé primaire.
• Un lookup atteint donc directement la donnée ; la phase « TABLE ACCESS » n’existe plus.

Sur des charges OLTP très centrées PK, le gain d’I/O est parfois spectaculaire.

1.3 Terminologie : ROWID logique, clé primaire, segment d’overflow

Les IOT introduisent quelques notions spécifiques :

• Clustering key : c’est la clé primaire, utilisée pour ordonner physiquement les lignes.
• ROWID logique : comme la ligne n’a pas d’adresse figée, les index secondaires stockent la clé primaire pour la retrouver.
• Overflow segment : si la ligne déborde, on déplace les colonnes volumineuses dans un segment séparé, référencé par la feuille du B-tree.

En résumé : IOT = segment d’index +, si besoin, segment d’overflow… et c’est tout.

2. Fonctionnement interne : comment Oracle stocke et accède aux données

2.1 Parcours index-based (Figure 5.2 revisitée)

Petit rappel :

Table heap :

1. INDEX RANGE SCAN (index secondaire ou PK),
2. TABLE ACCESS BY INDEX ROWID.

IOT (accès PK) :

1. INDEX UNIQUE SCAN sur le B-tree de la clé primaire,
2. Lecture de la ligne dans le même bloc feuille.

Ce qui donne :

----------------------------------------------------
| Id  | Operation         | Name        |
----------------------------------------------------
|   0 | SELECT STATEMENT  |             |
|*  1 | INDEX UNIQUE SCAN | SALES_IOT_PK|
----------------------------------------------------

2.2 Organisation du B-tree et clustering factor

Un IOT est parfaitement clusterisé sur la clé primaire. Dans les vues Oracle :

• DBA_INDEXES.CLUSTERING_FACTOR reste au top pour la PK,
• mais peut être médiocre entre la table et ses index secondaires – l’ordre physique est imposé par la PK.

D’où les conséquences évidentes : lecture PK = rapide ; lecture via un secondary index = double saut.

2.3 Pourquoi les index secondaires n’ont pas de ROWID

Dans une IOT, les lignes déménagent lors des splits de feuilles. Impossible donc de stocker un pointeur physique stable. Les index secondaires gardent la clé primaire, puis Oracle refait un INDEX UNIQUE SCAN pour récupérer la ligne. C’est ce qu’on appelle le ROWID logique.

3. Avantages, inconvénients et cas d’usage des IOT

3.1 Scénarios où l’IOT surpasse la table heap

Une IOT brille tout particulièrement quand :

• Les requêtes se concentrent sur SELECT … WHERE pk = :v.
• On parle de petites tables de référence consultées sans arrêt.
• La table change peu : beaucoup de lectures, très peu d’updates.
• Vous voulez réduire l’empreinte disque : plus de table + index à stocker, mais un seul B-tree.

3.2 Limitations, pièges et surcharge d’écritures

Côté moins réjouissant :

• Index secondaires plus lourds : ils embarquent la PK plutôt qu’un ROWID court.
• Deux parcours d’index pour chaque lookup via un secondary index : ça se sent.
• Insert/Update/Delete : les splits de feuilles et les déplacements de lignes coûtent cher.
• Gestion plus pointue : overflow, limitations fonctionnelles, etc.
• Trop d’index secondaires ? Plus de quatre ou cinq, et la heap redevient souvent gagnante.

3.3 Études de performances et benchmarks réels

Sur une table d’un million de lignes :

• 95 % d’accès PK : heap ≈ 2 lectures logiques ; IOT ≈ 1. Le gain est clair.
• 60 % via index secondaires : heap ≈ 2 lectures ; IOT ≈ 3. L’avantage s’inverse.
• Gros volume, peu d’indexes : l’IOT réduit la place disque et les I/O séquentiels.

4. Créer, modifier et trouver une index-organized table

4.1 Syntaxe CREATE TABLE ORGANIZATION INDEX

Syntaxe complète :

CREATE TABLE sales_iot (
    sale_id      NUMBER       NOT NULL,
    sale_date    DATE         NOT NULL,
    employee_id  NUMBER       NOT NULL,
    eur_value    NUMBER(12,2),
    CONSTRAINT sales_iot_pk PRIMARY KEY (sale_id)
)
ORGANIZATION INDEX
TABLESPACE iot_ts
PCTTHRESHOLD 20
INCLUDING sale_date
OVERFLOW TABLESPACE iot_overflow_ts;

Ce qu’il faut retenir :

• ORGANIZATION INDEX transforme la table en IOT.
• La PK est obligatoire et sert de clustering key.
• PCTTHRESHOLD + INCLUDING + OVERFLOW gèrent la partie débordante.

Version minimaliste :

CREATE TABLE lookup_status (
    status_code VARCHAR2(20) NOT NULL,
    label       VARCHAR2(100),
    CONSTRAINT lookup_status_pk PRIMARY KEY (status_code)
)
ORGANIZATION INDEX;

4.2 Scripts pour repérer les IOT existantes

Pour lister les IOT dans toute la base :

SELECT owner,
       table_name,
       tablespace_name,
       iot_type,
       iot_name,
       pct_threshold,
       overflow$
FROM   dba_tables
WHERE  iot_type IS NOT NULL
ORDER BY owner, table_name;

Dans un schéma donné :

SELECT table_name, iot_type, iot_name
FROM   user_tables
WHERE  iot_type = 'IOT';

4.3 Conversion d’une table heap en IOT et inversement

4.3.1 Heap → IOT (downtime)

1. Inventorier PK, indexes, contraintes, triggers.
2. Créer l’IOT.
3. INSERT /*+ APPEND */ des données.
4. Recréer les indexes secondaires.
5. Renommer les tables.

4.3.2 Heap → IOT avec DBMS_REDEFINITION

La même chose… sans (trop de) coupure :

1. DBMS_REDEFINITION.START_REDEF_TABLE
2. Synchronisation des données.
3. FINISH_REDEF_TABLE

4.3.3 IOT → heap

Même principe, dans l’autre sens : nouvelle table heap, transfert, bascule.

5. Index secondaires sur IOT : conception et accès aux données

5.1 Figure 5.3 décryptée : secondary index sur IOT

Soit l’IOT sales_iot (PK sale_id) et un index sur sale_date :

CREATE INDEX sales_iot_date_ix
ON sales_iot (sale_date);

La requête suivante…

SELECT eur_value
FROM   sales_iot
WHERE  sale_date = DATE '2012-05-23';

… produira typiquement :

------------------------------------------------------------
| Id  | Operation         | Name             |
------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |
|*  1 |  INDEX UNIQUE SCAN| SALES_IOT_PK     |
|*  2 |  INDEX RANGE SCAN | SALES_IOT_DATE_IX|
------------------------------------------------------------

On lit d’abord l’index secondaire, puis, pour chaque clé primaire trouvée, on replonge dans l’IOT. D’où le surcoût.

5.2 Avantages du ROWID logique dans l’index secondaire

Tout n’est pas sombre :

• Tant que la PK reste stable, le pointeur logique ne casse jamais.
• Une requête ne demandant que la PK peut être satisfaite par le seul index secondaire : index-only scan.

5.3 Stratégies de maintenance et de reconstruction

Les bonnes pratiques :

• Ne créer que les index secondaires réellement utiles.
• Surveiller BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS… et REBUILD si besoin.
• Garder les statistiques à jour (DBMS_STATS avec CASCADE=>TRUE).

6. Optimisation : partitionnement, compression et gestion de l’espace

6.1 Partitionnement range, list et hash sur IOT

Les IOT se prêtent au partitionnement :

• RANGE (dates, séquences),
• LIST (codes pays, canaux),
• HASH (répartition uniforme).

Exemple en range :

CREATE TABLE order_iot (
    order_id     NUMBER       NOT NULL,
    order_date   DATE         NOT NULL,
    customer_id  NUMBER       NOT NULL,
    amount       NUMBER(10,2),
    CONSTRAINT order_iot_pk PRIMARY KEY (order_date, order_id)
)
ORGANIZATION INDEX
PARTITION BY RANGE (order_date) (
    PARTITION p2024q1 VALUES LESS THAN (DATE '2024-04-01'),
    PARTITION p2024q2 VALUES LESS THAN (DATE '2024-07-01'),
    PARTITION p2024q3 VALUES LESS THAN (DATE '2024-10-01'),
    PARTITION pmax   VALUES LESS THAN (MAXVALUE)
);

6.2 Compression avancée et NetAPP

Deux leviers :

• Key compression sur la PK composite (COMPRESS 1, COMPRESS 2, etc.).
• Fonctions de compression avancée Oracle (licence requise) + stockage dédupliqué (NetApp, etc.).

Exemple :

CREATE TABLE cust_iot (
    region_id   NUMBER NOT NULL,
    cust_id     NUMBER NOT NULL,
    name        VARCHAR2(100),
    CONSTRAINT cust_iot_pk PRIMARY KEY (region_id, cust_id)
        USING INDEX COMPRESS 1
)
ORGANIZATION INDEX;

6.3 Surveillance des statistiques Optimizer et réorganisation

Pensez à :

• Lancer régulièrement DBMS_STATS.GATHER_TABLE_STATS.
• Contrôler la fragmentation (BLEVEL, CLUSTERING_FACTOR, etc.).
• Surveiller la taille du segment d’overflow, ajuster PCTTHRESHOLD au besoin.

7. FAQ, erreurs courantes et dépannage

7.1 ORA-25188 et autres messages fréquents

ORA-25188: cannot drop/disable primary key constraint for index-organized table or clustered table

La PK est la colonne de clustering ; on ne la supprime pas sans recréer la table (ou la convertir en heap).

Autres classiques :

• ORA-28658 : DDL interdit sur IOT.
• ORA-25176 : clause de stockage incompatible avec ORGANIZATION INDEX.

7.2 Bonnes pratiques de migration en production

Une check-list express :

• Mesurer votre workload : combien d’accès PK ? combien d’index ?
• Rejouer les requêtes sur une maquette IOT.
• Vérifier les limitations (LOB, partitionnement, triggers, etc.).
• Choisir la méthode de migration (offline vs DBMS_REDEFINITION).
• Préparer un plan de rollback.

7.3 Checklist rapide avant de choisir un IOT

Avant de plonger :

• La majorité des accès se fait-elle sur la PK ? Si oui, feu vert.
• Moins de cinq index secondaires ? Parfait. Au-delà, prudence.
• La clé primaire bouge-t-elle rarement ? Sinon, passez votre tour.
• Les lignes restent-elles compactes ? Les très gros enregistrements créent trop d’overflow.
• L’équipe est-elle prête à gérer la complexité supplémentaire ?

En deux mots : une IOT fait merveille sur les tables « lookup » ou les workloads 100 % PK. Dès que les accès deviennent variés et les index secondaires nombreux, la table heap reprend souvent le dessus.

Conclusion

L’index-organized table n’est pas un gadget réservé aux puristes ; c’est une arme redoutable pour qui sait quand l’employer. Entre un I/O économisé par lookup et une empreinte disque réduite, le gain peut être conséquent. À l’inverse, si vos requêtes passent massivement par des index secondaires, la facture peut vite grimper.

Vous avez désormais tout le nécessaire pour :

• détecter les IOT existantes via DBA_TABLES ou USER_TABLES,
• monter un proof-of-concept sur une table critique et comparer les plans d’exécution,
• réaliser vos propres benchmarks heap vs IOT avec vos workloads réels.

Un doute ? Partagez votre structure, vos plans, vos métriques : on pourra mettre au point, ensemble, un protocole de test et une stratégie de migration parfaitement adaptée à votre environnement Oracle.

Questions fréquentes sur les index-organized tables

Qu’est-ce qu’une index-organized table (IOT) dans Oracle ?

Une index-organized table (IOT) est une table Oracle où les données sont stockées et ordonnées physiquement dans un index B-tree basé sur la clé primaire, éliminant ainsi le besoin d’un segment de table séparé.

Comment identifier une index-organized table dans Oracle ?

Pour repérer une IOT, utilisez la vue système USER_TABLES et vérifiez la colonne IOT_TYPE. Si elle contient une valeur, la table est une index-organized table.

Quelle est la différence entre une index-organized table et une table heap ?

Une table heap stocke les lignes sans ordre spécifique et utilise un ROWID pour accéder aux données via un index. Une IOT, en revanche, stocke les lignes directement dans un index B-tree, ordonnées par clé primaire, supprimant le besoin de ROWID physique.

Quels sont les avantages d’une index-organized table ?

Les IOT permettent un accès rapide aux données via la clé primaire, réduisent les I/O en éliminant les sauts entre index et table, et optimisent les requêtes OLTP centrées sur la clé primaire.

Quand éviter d’utiliser une index-organized table ?

Évitez les IOT pour des tables avec des colonnes volumineuses ou des insertions fréquentes, car cela peut entraîner des performances dégradées et une fragmentation accrue du segment d’overflow.

Comment fonctionne le segment d’overflow dans une IOT ?

Le segment d’overflow stocke les colonnes volumineuses qui ne tiennent pas dans les blocs feuilles du B-tree. Il est utilisé pour éviter une surcharge des blocs d’index principaux.