Index organised table : rôle et utilisation

Oracle Database appelle Index-Organised Table une table dont les lignes sont stockées directement dans un B-tree trié par clé primaire. En pratique, l’index devient la table : on gagne en lecture sur la clé primaire, mais pas dans tous les scénarios.

1. Qu’est-ce qu’une Index-Organised Table ? (IOT)

Principe de stockage : quand l’index devient la table

Imaginez une table dont les données ne dorment plus dans un bloc séparé : avec l’IOT, tout est rangé dans le B-tree de la clé primaire. Autrement dit, on ne jongle plus entre “index” et “table” comme avec une heap classique. Les valeurs de la clé et les autres colonnes cohabitent directement dans les feuilles de l’arbre. Résultat ? Un simple parcours de l’index suffit, le moteur met la main sur la ligne sans détour.

Cette proximité séduit tout particulièrement les charges OLTP, friandes de recherches ponctuelles sur la PK ou de lectures de plages déjà ordonnées. Retenez donc la formule : une IOT, c’est une table Oracle rangée comme un index B-tree, ordonnée par sa primary key, dont l’index principal fait office de stockage.

Différences clés entre IOT et table en tas (heap)

Dans une heap table, les lignes s’empilent sans ordre précis. Interroger la PK revient à : 1) lire l’index, 2) suivre le ROWID jusque dans la table. Avec une IOT, cette seconde étape disparaît la plupart du temps – la donnée est déjà là où l’index pointe.

Cet alignement a cependant un coût : insérer hors de l’ordre naturel, mettre à jour la clé ou supprimer en masse peut provoquer des splits, des déplacements et donc un entretien plus délicat. Pour rester fiable malgré ces mouvements, Oracle utilise des ROWID logiques plutôt que des adresses physiques gravées dans le marbre.

En résumé, là où la heap privilégie la simplicité – pas d’ordre, table et index distincts –, l’IOT parie sur l’ordre trié et l’unicité de stockage, avec des contraintes physiques supplémentaires.

Terminologie essentielle : ROWID logique, clé primaire, branche feuille

Le fameux ROWID logique remplace le pointeur physique. Il encode l’emplacement dans l’arbre, de sorte qu’un éventuel déplacement de ligne n’invalide pas les références.

Quant aux feuilles du B-tree, elles embarquent la PK et toutes les colonnes que vous aurez choisi de laisser dans la “top” part de l’IOT. Les champs trop volumineux ou moins sollicités glissent dans un overflow segment, un stockage d’appoint physiquement séparé.

Dernier détail – et non des moindres : dans le dictionnaire Oracle, l’index racine d’une IOT se présente sous le type IOT – TOP, tandis que le débordement se matérialise comme un objet overflow rattaché.

2. Avantages, limites et cas d’utilisation

Scénarios où l’IOT surpasse les tables classiques

Pourquoi tant d’intérêt ? D’abord, parce que la lecture par clé primaire devient fulgurante : la requête se contente d’un simple index lookup. Les parcours de plages, eux aussi, profitent de l’ordre naturel du B-tree.

Deuxième atout : le footprint disque. Dans une heap, la PK est stockée deux fois (données + index). Avec une IOT, cette redondance s’évapore et, cerise sur le gâteau, la compression de clé peut encore économiser des blocs.

En clair, on privilégie les IOT pour les répertoires, les historiques triés par identifiant composite, les séries temporelles… bref, tous les modèles où l’on tape quasi exclusivement par la PK.

Limitations et pièges fréquents

L’envers du décor ? Les index secondaires. Sur une heap, ils pointent vers un ROWID physique. Sur une IOT, ils doivent d’abord trouver la clé, puis repasser par l’index principal. Ce double détour peut coûter cher.

Ajoutez à cela les gros DML désordonnés : inserts “hors piste”, updates de clé ou suppressions massives malmènent le B-tree et engendrent fragmentation et latence.

Enfin, gare aux lignes éléphantesques. Si leur taille franchit le seuil du bloc d’index, la partie excédentaire file dans l’overflow. Mauvais réglage = bénéfice qui s’évapore.

Retours terrain et repères pratiques

Les DBA aguerris le répètent : une IOT se mérite. Tant que 90 % des accès visent la PK, elle brille. Mais dès que l’appli multiplie les recherches sur d’autres colonnes, la bonne vieille heap, bardée d’index, reprend souvent l’avantage.

Pensez à la table de séries temporelles (code_objet, timestamp). Parfait pour les lectures séquentielles et l’archivage compact. Pourtant, si vos utilisateurs filtrent d’abord par timestamp seul ou par un attribut hors PK, la magie opère beaucoup moins.

Moralité : l’élégance théorique ne suffit pas. L’IOT doit répondre à vos plans d’exécution. Sinon, passez votre chemin.

3. Comment créer une Index-Organised Table

Syntaxe SQL CREATE TABLE … ORGANIZATION INDEX

Premier impératif : définir une primary key. Sans elle, Oracle lève un véto immédiat. Ensuite, un simple ORGANIZATION INDEX dans le CREATE TABLE, et la mécanique se met en place.

Exemple minimaliste :

CREATE TABLE clients_iot (
client_id NUMBER PRIMARY KEY,
nom VARCHAR2(100),
ville VARCHAR2(80)
) ORGANIZATION INDEX;

À la création, Oracle installe la table logique, bâtit l’index IOT-TOP et y dépose directement les lignes.

Options clés : PCTTHRESHOLD, INCLUDING, OVERFLOW et tablespace

PCTTHRESHOLD fixe la place maximale – entre 1 et 50 % du bloc – qu’une ligne peut occuper dans la partie principale. Au-delà, le reste ira dans l’overflow.

La clause INCLUDING détermine la dernière colonne conservée dans la zone principale. Les suivantes basculent, elles, dans l’overflow. Si jamais INCLUDING et le seuil se heurtent, c’est PCTTHRESHOLD qui l’emporte.

Enfin, OVERFLOW autorise la création explicite d’un segment dédié, éventuellement logé dans un tablespace distinct. Idéal pour les colonnes bavardes ou rarement consultées.

Exemple complet pas à pas

Un exemple plus concret :

CREATE TABLE commande_lignes_iot (
commande_id NUMBER,
ligne_id NUMBER,
sku VARCHAR2(40),
quantite NUMBER,
commentaire VARCHAR2(1000),
CONSTRAINT pk_commande_lignes_iot PRIMARY KEY (commande_id, ligne_id)
)
ORGANIZATION INDEX
TABLESPACE data_iot
PCTTHRESHOLD 20
INCLUDING quantite
OVERFLOW TABLESPACE data_iot_ovf;

Oracle stocke ici la PK et les colonnes jusqu’à quantite tant que le seuil de 20 % du bloc n’est pas dépassé. Le champ commentaire, plus volumineux, bascule logiquement dans l’overflow.

Une fois la table chargée, n’oubliez pas de lancer la collecte de statistiques et d’inspecter la proportion d’overflow pour valider vos paramètres.

4. Index secondaires, partitionnement et compression

Pourquoi et comment ajouter un index secondaire

L’IOT n’interdit aucunement les index supplémentaires. Besoin d’accélérer les recherches sur sku ? Aucun problème :

CREATE INDEX idx_commande_lignes_sku
ON commande_lignes_iot (sku);

Gardez toutefois en tête que cet index n’aura pas de ROWID physique sous la main. Il fera donc un premier INDEX RANGE SCAN, puis un saut vers l’index principal. Cette double étape peut peser sur la latence : à mesurer.

Partitionner une IOT pour la scalabilité

Quand les volumes explosent, le partitionnement devient un allié. Que diriez-vous de découper vos données par date ou par domaine métier ? Les IOT le supportent fort bien : maintenance plus fine, meilleure parallélisation, purges ciblées.

Attention toutefois : la stratégie de partition doit coller à la clé primaire. Sans cohérence, vous risquez de compliquer le design pour un gain discutable.

Activer la compression pour optimiser l’espace

Sur les clés composites répétitives, la compression fait des merveilles. En déclarant tout simplement COMPRESS, Oracle mutualise les préfixes communs :

CREATE TABLE prix_histo_iot (
produit_id NUMBER,
horodatage DATE,
prix NUMBER,
CONSTRAINT pk_prix_histo PRIMARY KEY (produit_id, horodatage)
) ORGANIZATION INDEX COMPRESS;

Les séries temporelles y trouvent souvent leur compte : blocs plus denses, E/S réduites.

5. Maintenance et optimisation des IOT

Collecte de statistiques et impact sur l’optimiseur

Sans chiffres fiables, l’optimiseur risque de partir en balade. Intégrez donc DBMS_STATS à vos scripts d’exploitation :

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'COMMANDE_LIGNES_IOT',
cascade => TRUE
);
END;
/

Pensez à passer en revue les plans d’exécution : un INDEX UNIQUE SCAN sur la PK doit rester le chemin privilégié, sans quoi un réglage s’impose.

Rebuild, analyse et monitoring de l’overflow segment

Comme tout B-tree, une IOT peut se tasser ou se fragmenter. Rien de dramatique : un ALTER TABLE ... MOVE (éventuellement MOVE ONLINE) suffit souvent à remettre les choses d’équerre.

ALTER TABLE commande_lignes_iot MOVE;
ALTER TABLE commande_lignes_iot MOVE ONLINE;

Côté overflow, surveillez la taille et la fréquence d’accès. ANALYZE TABLE … LIST CHAINED ROWS vous dira vite si vos seuils sont encore adaptés.

Checklist de monitoring post-mise en production

Une fois en prod, quelques incontournables :

• plans d’exécution sur PK et sur index secondaires ;
• croissance des segments principal et overflow ;
• statistiques à jour (table + index) ;
• dérive de fragmentation après DML intensifs ;
• signaux AWR/ASH révélant des accès coûteux.

L’idée est simple : comparez systématiquement l’avant/après sur votre jeu de requêtes de référence. C’est le seul juge impartial.

6. Convertir ou migrer une IOT

Conversion vers table en tas ou table partitionnée

Bonne nouvelle : rien n’est gravé dans le marbre. Si les usages évoluent – multiplication d’index secondaires, DML aléatoires, besoins d’administration plus souples –, vous pouvez toujours rétrograder l’IOT en heap table. On recrée la table cible, on la remplit, on recrée les index, on bascule.

L’inverse est vrai également : passer d’une heap à une IOT réclame surtout une PK solide, des lignes de taille raisonnable et – encore et toujours – une validation des plans d’exécution.

Stratégies zero-downtime : DBMS_REDEFINITION, CTAS

Pas de fenêtre d’arrêt ? Deux armes : le package DBMS_REDEFINITION ou la méthode CTAS suivie d’une bascule. La première s’intègre mieux à un contexte haute disponibilité, la seconde est souvent plus rapide à prototyper.

Dans les deux cas, prévoyez de recopier contraintes, index secondaires et statistiques. Et, bien sûr, un plan de rollback si la nouvelle structure ne tient pas ses promesses.

Vérification post-migration et tests de performance

Après la bascule, trois familles de tests à cocher : lectures PK, lectures via index secondaires, DML quotidiens. Comparez coûts, I/O, latence. Inspectez aussi les vues USER_TABLES, USER_INDEXES, USER_SEGMENTS : l’objet IOT_OVERFLOW doit figurer là où vous l’attendez.

Et si le gain n’est pas au rendez-vous ? Revenez à la matrice d’accès : l’IOT n’est pas une optimisation universelle, juste un outil redoutable quand il colle à votre workload.

7. Questions fréquentes et comparaisons utiles

What is an index-organized table?

Réponse courte : c’est une table Oracle dont les lignes sont stockées directement dans un index B-tree, triées par clé primaire. On parle de table “organisée par index” parce que la structure de l’index principal sert aussi de stockage des données.

What is the difference between heap table and index-organized table?

La différence tient au stockage. Une heap table conserve les lignes sans ordre particulier, avec des index séparés qui pointent vers des ROWID physiques. Une index-organized table stocke les lignes dans le B-tree primaire, en ordre de clé, avec un ROWID logique.

What is an index table?

Dans le contexte Oracle, cette expression renvoie le plus souvent à l’index-organized table. Ce n’est pas simplement “une table avec un index”, mais une table dont le stockage repose sur l’index lui-même.

IOT vs index clusterisés d’autres SGBD

Le parallèle le plus proche se fait avec les clustered indexes d’autres SGBD. L’idée générale est similaire : les données sont alignées sur une structure d’index. Mais les implémentations diffèrent. Oracle utilise explicitement le modèle IOT, SQL Server parle d’index clusterisé, tandis que MySQL InnoDB adopte un comportement proche par défaut.

La nuance importante : la gestion des index secondaires. Dans tous ces systèmes, dès que les données bougent pour rester ordonnées, les accès via index secondaires se compliquent par rapport au simple ROWID de la heap.

8. P1, P2, P3 dans Oracle et conclusion opérationnelle

Comment interpréter les champs P1, P2, P3 dans les blocs d’index

P1, P2, P3 ne sont pas des colonnes propres aux index-organized tables. Ce trio apparaît dans les événements d’attente et certaines vues de diagnostic. Leur signification dépend donc de l’attente observée.

Exemple : pour une lecture de bloc d’index, les paramètres peuvent désigner le fichier, le numéro de bloc et la classe. Autant dire qu’il faut toujours croiser avec le nom de l’événement dans AWR, ASH ou V$SESSION pour interpréter correctement.

Synthèse : quand utiliser une index organised table

En définitive, l’index organised table excelle lorsque vos requêtes font la cour à la clé primaire, que l’ordre physique vous sert et que la réduction d’espace est un bonus.

Inversement, si votre modèle repose sur moult index secondaires, des filtres exotiques ou des DML chaotiques, la heap, bien indexée, vous rendra de meilleurs services. Le choix du “plus avancé” n’a aucun sens si l’usage ne suit pas.

Avant de décider, jouez la carte des tests : ajustez PCTTHRESHOLD, INCLUDING, OVERFLOW, passez vos requêtes au crible, mesurez la maintenance. Ce n’est qu’alors que l’évidence se fera : IOT ou heap, à vous de trancher.

Questions fréquentes sur les Index-Organised Tables (IOT)

Qu’est-ce qu’une Index-Organised Table (IOT) ?

Une Index-Organised Table (IOT) est une table Oracle où les lignes sont stockées directement dans un index B-tree trié par clé primaire, éliminant le besoin de lire un index séparé pour accéder aux données.

Quelle est la différence entre une heap table et une Index-Organised Table ?

Dans une heap table, les données sont stockées sans ordre précis, tandis qu’une IOT organise les lignes directement dans un index B-tree trié par clé primaire, optimisant les recherches par clé.

Quels sont les avantages des Index-Organised Tables ?

Les IOT offrent des performances accrues pour les recherches par clé primaire, une réduction de l’espace disque grâce à l’absence de redondance PK, et un accès rapide aux plages de données ordonnées.

Qu’est-ce qu’un ROWID logique dans une IOT ?

Un ROWID logique encode l’emplacement d’une ligne dans le B-tree d’une IOT, permettant de gérer les déplacements de données sans invalider les références.

Qu’est-ce que le segment overflow dans une IOT ?

Le segment overflow stocke les colonnes volumineuses ou moins utilisées qui ne tiennent pas dans les blocs d’index principaux d’une IOT, optimisant l’espace et les performances.

Qu’est-ce que P1, P2, P3 dans Oracle ?

P1, P2, et P3 sont des paramètres dans Oracle qui définissent des valeurs spécifiques pour les événements ou les états internes, souvent utilisés dans les diagnostics de performance.