Vote utilisateur: 5 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles actives
 

Définition :

Les tables IOT pour Index Organized Table sont des tables dont les données sont triées suivant l'index de clef primaire (B*Tree Index).

En fait toutes les colonnes d'une ligne de la table sont stockées dans la feuille de l'index de clef primaire. On peut dire que l'index de clef primaire et la table ne font qu'un.

Syntaxe :

Pour créer une table comme table IOT il faut ajouter ORGANIZATION INDEX au create table.

CREATE TABLE <table_name> (
<column_name> <data type and precision>,
<column_name> <data type and precision>,
CONSTRAINT <constraint_name>
PRIMARY KEY (<primary key constraint columns>))
ORGANIZATION INDEX;

Il n'est pas possible de modifier une table classique en table IOT par alter table. Il est néanmoins tout à fait possible de modifier une table IOT pour ajouter, modifier ou supprimer des colonnes. La contrainte de clef primaire par contre ne peut être supprimées ou désactivées.

Il est possible d'utiliser MOVE pour reconstruire la table et ainsi ne pas perturber les utilisateurs (la table reste dispo pendant la reconstruction) :

ALTER TABLE <table_name> MOVE ...

Une table IOT est également partitionnable.

Avantages :

Un avantage évident c'est le gain de place (stockage) puisqu'il ne faut plus stocker les données de la table ET l'index de clef primaire à part. Dans une IOT table et index PK ne font qu'un.

L'avantage au niveau des performances de ces tables se retrouve à la consultation. En effet pour accéder au ligne de la table avec un prédicat portant sur la clef primaire, il n'y a qu'un seul IO avec une table IOT alors qu'on aurait au moins 2 IOs avec une table classique : l'IO pour le parcours de l'index et l'IO pour l'accès à la ROWID de la table une fois l'index parcouru et les rowid interressantes trouvées.

1 IO au lieu de 2 ce n'est pas la panacé me direz vous, mais si on imagine des requêtes mettant en cause des dixaines de tables en accès via PK, avec des requêtes répétées des dixaines, centaines voir milliers de fois par une application plus ou moins bien tunée et qu'en plus on rajoute des milliers d'utilisateurs on trouvera rapidement que diviser par deux le nombre d'IOs par table accédée dans les requêtes peut être fortement interressant.

C'est d'autant plus vrai que l'optimiseur oracle à tendance à avoir de meilleurs plans (si l'accès est par PK) avec des tables IOT.

Finallement quand il faut faire passer des requêtes de 50 ms à 2 ou 3 ms c'est une solution qui peut être appropriée.

Inconvéniants :

L'inconvéniant de ces tables IOT ce situe sur la mise à jour des données car l'insertion dans la table est en fait une insertion dans un index l'insertion est donc ralentie.

Il existe cependant de nombreux cas ou les tables sont quasiment statiques où bien les tables sont alimentées par batch rendant bien moins critique le besoin de performance à l'insertion.

Il existe également un inconvéniant lorsque la table est large (beaucoup de colonnes) dans ce cas il est nécessaire de définir un segment d'overflow de l'index ce qui oblige à avoir un IO de plus pour récupérer les colonnes qui ne sont pas inclusent dans l'index.

Pour finir il est également nécessaire de mettre à jour les index secondaires sur ces tables si les données des tables sont trop mouvantes. En effet l'index secondaire contient une zone dite GUESS qui determine le BLOCK_ID de l'entrée de l'index. Ce GUESS est juste à la création de l'index secondaire, mais des insertions dans la table peuvent rendre ce GUESS faux, dans ce cas, il faudra 1 ou 2 IOs supplémentaires pour récupérer le bon block.  Il existe un indicateur oracle qui sert pour l'optimiseur et qui s'il n'est pas optimal pourrait induire des plans d'execution non optimaux.

Il est possible de recalculer les GUESS d'un index secondaire avec l'ordre SQL suivant :

ALTER INDEX <index_name> UPDATE BLOCKS REFERENCES;

Le cout d'une telle opération de maintenance est généralement très faible.

La taille des index secondaires d'une table IOT est légèrement plus importante qu'un index secondaire d'une table classique.