Introduction
Une table IOT (Index Organized Table) est une table d'un genre spécial. A la différence d'une table classique, dite aussi HOT (Heap Organized Table), cette table possède de multiples particularités qu'il est bon de connaître pour ne pas être vite débordé par leur complexité.

En premier, une table classique, avec un index pour la PK, est en réalité un ensemble de deux objets : la table ET l'index, comme un livre avec son index à la fin. En revanche une table IOT ne comprend en réalité qu'un index et pas de table; c'est, dans la vraie vie, un annuaire téléphonique ou un dictionnaire : toutes les infos sont présentes dans l'index.

En deuxième, une table IOT a des logical rowids et pas, comme les tables HOT, des physical rowids (nous verrons cela plus en détail plus bas). A quoi cela est-il dû? Le H d'une table dite HOT signifie Heap, c'est à dire Tas en français. Les enregistrements dans une table classique sont mis n'importe où, là où il y a de la place, sans ordre; si une table HOT subit de nombreux Deletes, les prochains Inserts pourront se mettre là où il y a de la place; dans le premier bloc par exemple. En revanche, une table IOT est un index, donc les données sont triées! Cela veut dire que ces données changent régulièrement de bloc (sauf si on a construit la table IOT sur une séquence). La conséquence de ceci est qu'il n'est pas possible d'identifier un enregistrement d'une table IOT par un physical rowid puisqu'il est valide lors de l'Insert mais peut-être plus valide après plusieurs jours et splits de blocs. Oracle s'en sort en gérant l'emplacement de ces enregistrements via un logical rowid.

Dernier point, plus vicieux : une table IOT est constituée d'un seul segment, de type index, alors qu'une table classique, avec PK, est constituée de deux segments, l'un pour la table, l'un pour l'index. Donc une recherche dans DBA_SEGMENTS sur le nom de la table ne renverra rien...


Quel est l'intérêt de ce type de table? Si vous ne faites des recherches QUE sur la PK, alors créez une table IOT plutôt qu'une table HOT; c'est plus performant car il y a moins de blocs à parcourir pour récupérer les données.

Pour les DBA avec une certaine expérience, vous aurez compris ce qu'est la table IOT : un index composite poussé à l'extrème car composé de TOUTES les colonnes de la table. Et, par rapport à une table qui aurait un tel index composite, vous n'avez plus de données dupliquées :-)


 
Points d'attention
Aucun.



Base de tests
Une base Oracle 12.



Exemples
============================================================================================
Création de la table IOT
============================================================================================

Pour créer une table IOT, il faut impérativement déclarer une Primary Key; normal puisque cette table est en réalité un index; à noter que ce n'est pas le cas pour une table HOT. Le mot clé est "organization index" pour créer une table IOT.
     SQL> create table ZZIOT_KO (id number, name varchar2(50 char)) organization index;
     create table ZZIOT_KO (id number, name varchar2(50 char)) organization index
                                                                            *
     ERROR at line 1:
     ORA-25175: no PRIMARY KEY constraint found
     
     SQL> create table ZZ_IOT (id number primary key, first_name varchar2(50 char), last_name varchar2(50 char), created date) organization index;
     Table created.
     
     SQL> select iot_type from dba_tables where table_name = 'ZZ_IOT';
     IOT_TYPE
     ------------
     IOT

On remplit celle-ci avec 1000 enregistrements.    
     SQL> INSERT INTO zz_iot SELECT rownum, 'First_name' || to_char(rownum), 'Last_name' || to_char(rownum), sysdate FROM dual CONNECT BY LEVEL <=1000;
     1000 rows created.
     
     SQL> commit;
     Commit complete.

     SQL> select * from zz_iot where id = 200;
     ID FIRST_NAME                          LAST_NAME                  CREATED
     ---------- -------------------------------------------------- ---------------
     200 First_name200                      Last_name200               27-JAN-19

     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT');
     PL/SQL procedure successfully completed.
    
Le nom de l'index est celui de la pk; il est dans cet exemple généré par Oracle.
     SQL> select index_name from dba_indexes where table_name='ZZ_IOT';
     INDEX_NAME
     ---------------------
     SYS_IOT_TOP_80252
     
Attention, comme je le disais en introduction, contrairement à une table classique, créer une table IOT consiste à créer deux objets, une table et un index, MAIS à créer un seul segment, de type index. Attention aussi, ce segment de type index est plus proche d'un index composé que d'un index classique car si cet index porte sur la colonne PK, il renferme également les données de toutes les autres colonnes de la table.

Deux objets, un index, une table MAIS un seul segment.
     SQL> select object_name, object_type from user_objects where object_name in ('ZZ_IOT', 'SYS_IOT_TOP_80252') order by object_name;
     OBJECT_NAME                    OBJECT_TYPE
     -----------------------------------------
     SYS_IOT_TOP_80252            INDEX
     ZZ_IOT                        TABLE
     
     SQL> select table_name from dba_tables where table_name in ('ZZ_IOT', 'SYS_IOT_TOP_80252');
     TABLE_NAME
     ----------
     ZZ_IOT
    
     SQL> select index_name, index_type from user_indexes where index_name = 'SYS_IOT_TOP_80252';
     INDEX_NAME           INDEX_TYPE
     -------------------- ---------------------------
     SYS_IOT_TOP_80252    IOT - TOP

     SQL> select segment_name from dba_segments where segment_name in ('ZZ_IOT', 'SYS_IOT_TOP_80252');
     SEGMENT_NAME
     ------------------
     SYS_IOT_TOP_80252
     
Petite surprise supplémentaire, une table IOT a ce qu'on appelle des logical rowids et pas des physycal rowid comme les tables HOT. Il suffit des afficher pour voir de suite la différence. A noter qu'avec un Logical rowid, Oracle peut recalculer la PK à partir de ce rowid, cela sera utile pour les index secondaires.

Logical rowids
     SQL> select rowid from zz_iot where rownum < 11 order by rowid;
     ROWID
     --------------------
     *BAMAJEQDwgJS/g
     *BAMAJEQDwgJT/g
     *BAMAJEQDwgJU/g
     *BAMAJEQDwgJV/g
     *BAMAJEQDwgJW/g
     *BAMAJEQDwgJX/g
     *BAMAJEQDwgJY/g
     *BAMAJEQDwgJZ/g
     *BAMAJEQDwgJa/g
     *BAMAJEQDwgJb/g
     
     10 rows selected.
    
Physical rowids
     SQL> select rowid from employees where rownum < 11 order by rowid;
     ROWID
     ------------------
     AAATTBAAMAAACD9AAD
     AAATTBAAMAAACD9AAP
     AAATTBAAMAAACD9AAV
     AAATTBAAMAAACD9AAv
     AAATTBAAMAAACD9AA6
     AAATTBAAMAAACD9ABD
     AAATTBAAMAAACD9ABL
     AAATTBAAMAAACD9ABV
     AAATTBAAMAAACD9ABX
     AAATTBAAMAAACD9ABg
     
     10 rows selected.
     

============================================================================================
La table IOT est un index et seulement un index
============================================================================================
    
La table IOT est bien considérée par Oracle comme un index, sans table associée, c'est ce que nous avons vu dans DBA_SEGMENTS.

Regardons maintenant le plan d'exécution : on accède à toutes les données de la table (SELECT *) via un INDEX UNIQUE SCAN, sans avoir ensuite un TABLE ACCESS BY INDEX ROWID. Pourtant, la PK ne porte que sur la colonne ID. Pour une table HOT on aurait eu le même plan d'exécution si le Select avait porté uniquement sur la colonne ID.
     SQL> select * from zz_iot where id = 200;
     ID FIRST_NAME                          LAST_NAME                  CREATED
     ---------- -------------------------------------------------- -----------------
     200 First_name200                      Last_name200               27-JAN-19
    
     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL ,NULL, 'TYPICAL'));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------
     SQL_ID    4u2tuk6g76g5n, child number 0
     -------------------------------------
     select * from zz_iot where id = 200
     
     Plan hash value: 631232691
     
     ---------------------------------------------------------------------------------------
     | Id  | Operation      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
     ---------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |              |       |       |     1 (100)|          |
     |*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_80252 |     1 |    39 |     1    (0)| 00:00:01 |
     ---------------------------------------------------------------------------------------
     
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - access("ID"=200)
     
     18 rows selected.
     

============================================================================================
La table IOT et les index secondaires : Oracle peut indexer un index
============================================================================================
    
     
Si, au bout d'un certain temps, on se rends compte que la recherche sur la PK n'est plus suffisante, il est possible de créer un index dit index secondaire sur une ou des colonnes non participantes à la PK : c'est, comme le dit Oracle dans sa doc, un index sur index puisque la table IOT est en réalité un index. Oracle permet donc d'indexer un index! Cet index sera de type NORMAL et non pas IOT - TOP comme l'index primaire.
     SQL> create index idx_zz_iot_first_name on zz_iot(first_name);
     Index created.
     
     SQL> select index_name, INDEX_TYPE, TABLE_TYPE, UNIQUENESS from dba_indexes where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME                 INDEX_TYPE          TABLE_TYPE  UNIQUENES
     ------------------------- --------------------------- ----------- ---------
     IDX_ZZ_IOT_FIRST_NAME      NORMAL              TABLE       NONUNIQUE
     SYS_IOT_TOP_80252          IOT - TOP           TABLE       UNIQUE
    
Les stats sur les index sont OK.
     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT', cascade=>TRUE);
     PL/SQL procedure successfully completed.

     SQL> select num_rows, index_name from user_indexes where table_name = 'ZZ_IOT';

       NUM_ROWS INDEX_NAME
     ---------- -------------------------
           1000 SYS_IOT_TOP_80252
           1000 IDX_ZZ_IOT_FIRST_NAME
     
Si on fait une recherche sur la colonne de l'index secondaire, Oracle parcourt cet index secondaire puis parcourt aussi le premier pour récupérer les autres données. Mais n'est-ce pas bizarre? Pourquoi pas d'accès direct à la table IOT via le logical rowid? On a bien un INDEX UNIQUE SCAN mais pas un INDEX ACCESS BY ROWID (ça n'existe pas) : donc Oracle, même s'il récupère un seul enregistrement, parcourt l'index primaire; ça me surprends.
     SQL> select * from zz_iot where first_name = 'First_name200';
     ID FIRST_NAME                          LAST_NAME                      CREATED
     ---------- -------------------------------------------------- -------------------
     200 First_name200                      Last_name200                     27-JAN-19
     
     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL ,NULL, 'TYPICAL'));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------------------------
     SQL_ID    4b5xhv7xbfzm0, child number 0
     -------------------------------------
     select * from zz_iot where first_name = 'First_name200'
     
     Plan hash value: 2333378696
     
     -------------------------------------------------------------------------------------------
     | Id  | Operation      | Name          | Rows  | Bytes | Cost (%CPU)| Time      |
     -------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |              |      |      |    1 (100)|      |
     |*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_80252      |    1 |    39 |    1   (0)| 00:00:01 |
     |*  2 |   INDEX RANGE SCAN| IDX_ZZ_IOT_FIRST_NAME |    1 |      |    1   (0)| 00:00:01 |
     -------------------------------------------------------------------------------------------
     
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - access("FIRST_NAME"='First_name200')
        2 - access("FIRST_NAME"='First_name200')
     
     20 rows selected.


La colonne PCT_DIRECT_ACCESS 
Dans les vues xxx_INDEXES, la colonne PCT_DIRECT_ACCESS signifie "Quel est le pourcentage d'accès direct à l'index primaire via le logical rowid récupéré dans l'index secondaire?". Attention, cette colonne est une statistique, pas une probabilité :-)

La colonne PCT_DIRECT_ACCESS vaut 0 pour SYS_IOT_TOP_80252 parce qu'on est sur l'index primaire, et que cette colonne n'a de sens que pour un index secondaire... Mais on a 100 pour IDX_ZZ_IOT_FIRST_NAME donc pourquoi accéder à l'index primaire via un INDEX UNIQUE SCAN et pas un accès direct via rowid? Tout simplement parce que Oracle n'a pas de mode d'accès direct aux index via un rowid?
     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME                       INDEX_TYPE           PCT_DIRECT_ACCESS
     ------------------------------ ---------------------------------
     IDX_ZZ_IOT_FIRST_NAME            NORMAL                100
     SYS_IOT_TOP_80252                IOT - TOP              0
     
Quel est le plan d'exécution si j'utilise un logical rowid dans le WHERE?
Oh, intéressant, même si on a bien un prédicat d'accès via le ROWID, Oracle met dans le plan d'exécution une opération de type INDEX UNIQUE SCAN. Cela veut-il dire qu'Oracle doive toujours parcourir l'index lié à la PK et que son logical rowid ne permette pas un accès direct au bloc de l'index de la pk?
     SQL> select rowid from zz_iot where id = 200;

     ROWID
     -----------------------------------------
     *BAMAJEQCwgP+
     
     SQL> select * from zz_iot where rowid = '*BAMAJEQCwgP+';
     ID FIRST_NAME                          LAST_NAME                CREATED
     ---------- -------------------------------------------------- -----------------
     200 First_name200                      Last_name200             27-JAN-19
    
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------------
     SQL_ID    4matk2kgx2yrk, child number 0
     -------------------------------------
     select * from zz_iot where rowid = '*BAMAJEQCwgP+'
     
     Plan hash value: 631232691
     
     ---------------------------------------------------------------------------------------
     | Id  | Operation      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
     ---------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |              |       |       |     1 (100)|          |
     |*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_80252 |     1 |    39 |     3    (0)| 00:00:01 |
     ---------------------------------------------------------------------------------------
     
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - access(ROWID=:B1)
     
     18 rows selected.
   
Essayons de mettre le champ PCT_DIRECT_ACCESS à 0 pour voir si cela change le plan d'exécution. Pour se faire on va supprimer 90% de la table puis faire un shrink pour changer les emplacements des enregistrements restants et forcer le recalcul des rowids dans la table; de la sorte on crée un décalage avec ceux de l'index et on invalide les logical rowids de l'index secondaire.
     SQL> delete from zz_iot where mod(id, 10) <> 0;
     900 rows deleted.
     
     SQL> commit;
     Commit complete.
     
Tiens, ENABLE ROW MOVEMENT n'est pas obligatoire pour faire un SHRINK sur les tables IOT non partitionnées.
     SQL> alter table zz_iot ENABLE ROW MOVEMENT;
     alter table zz_iot ENABLE ROW MOVEMENT
     *
     ERROR at line 1:
     ORA-14066: illegal option for a non-partitioned index-organized table
     
     SQL> ALTER TABLE zz_iot SHRINK SPACE;
     Table altered.
     
     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT');
     PL/SQL procedure successfully completed.
     
     SQL> exec dbms_stats.gather_index_stats('', 'IDX_ZZ_IOT_FIRST_NAME');
     PL/SQL procedure successfully completed.
     
Héhé, on est passé de 100 à 17! Mais le plan d'exécution lui ne change pas... bizarre, bizarre...
     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME                 INDEX_TYPE     PCT_DIRECT_ACCESS
     -------------------- --------------------------------------
     IDX_ZZ_IOT_FIRST_NAME      NORMAL             17
     SYS_IOT_TOP_80252          IOT - TOP          0
     
     SQL> set autotrace on
     SQL> select * from zz_iot where first_name = 'First_name10';
         ID FIRST_NAME                          LAST_NAME                      CREATED
     ---------- -------------------------------------------------- -----------------------
         10 First_name10                       Last_name10                     27-JAN-19
    
     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 2333378696
     
     -------------------------------------------------------------------------------------------
     | Id  | Operation      | Name          | Rows  | Bytes | Cost (%CPU)| Time      |
     -------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |              |    1 |    39 |    2   (0)| 00:00:01 |
     |*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_80252      |    1 |    39 |    2   (0)| 00:00:01 |
     |*  2 |   INDEX RANGE SCAN| IDX_ZZ_IOT_FIRST_NAME |    1 |      |    1   (0)| 00:00:01 |
     -------------------------------------------------------------------------------------------
     
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - access("FIRST_NAME"='First_name10')
        2 - access("FIRST_NAME"='First_name10')
     
     
     Statistics
     ----------------------------------------------------------
           1  recursive calls
           0  db block gets
           5  consistent gets
           0  physical reads
           0  redo size
         781  bytes sent via SQL*Net to client
         607  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           1  rows processed



============================================================================================
Conclusion
============================================================================================
     
On en reste là pour le moment, je vous laisse cogiter sur le plan d'exécution quand on utilise un index secondaire d'une table IOT.

La suite dans un prochain article.