Changer l'ordre des colonnes d'une table avec l'attribut Invisible - Change columns order in a table with Invisible attribute

    
Introduction
Chez un client, un responsable développement est venu me voir avec un problème bien particulier. Il avait une table de 300 colonnes et, quand il voulait récupérer le nom de ses clients, au lieu de simplement faire un SELECT nom FROM clients, il récupérait toutes les données de la table puis faisait un extract du fichier .txt créé en se basant sur la position des caractères. En clair, pour lire le nom, il récupérait les caractères 50 à 99 dans ce fichier. OK, c'était une application ancienne, inutile de lui dire que le SQL est indépendant de la position des colonnes et qu'il faut travailler autrement. Mais là où les choses se sont compliquées, c'est que en production la table a été droppée et reconstruite mais avec un ordre de colonnes différent de celui de l'environnement de développement. Résultat : les caractères 50 à 99 ne correspondaient plus au nom du client mais à son adresse mail...

Cette personne m'a donc demandé s'il était possible de remonter la colonne Nom dans la définition de la table pour ne pas à avoir à modifier son programme. Vu que la base était en version 11g, la seule solution était d'utiliser DBMS_REDEFINITION mais c'est un package pas très simple à manipuler et je ne voyais pas de solution plus rapide à son problème. Finalement cette personne a modifié son programme mais je n'ai pas oublié cette histoire.

C'est alors qu'une nouvelle solution, bien plus simple et élégante, est apparue en version 12c. En jouant sur l'attribut Invisible des colonnes, on peut facilement changer leur emplacement, sans que ce soit un bug d'affichage mais une vraie modification du dictionnaire de données.

---

Points d'attention
Aucun.

---

Base de tests
Une base Oracle 12c au minimum.

---

 
Exemples
============================================================================================
Création et ordre des colonnes dans le dictionnaire de données.
============================================================================================
On commence par créer une table avec quelques colonnes.
          SQL> CREATE TABLE TEST01(COL1 NUMBER(4,2), COL2 VARCHAR2(10 CHAR), COL3 NUMBER(6,2), COL4 DATE, COL5 TIMESTAMP);
          Table créée.

Nous vérifions que l'ordre de ces colonnes avec DESC et SELECT est conforme à celui du CREATE TABLE. C'est bien le cas!
          SQL> DESC TEST01
          Nom  NULL Type             
          ---- ---- -----------------
          COL1      NUMBER(4,2)      
          COL2      VARCHAR2(10 CHAR)
          COL3      NUMBER(6,2)      
          COL4      DATE             
          COL5      TIMESTAMP(6) 

         SQL> select * from test01;
         COL1           COL2          COL3          COL4               COL5
         aucune ligne sélectionnée

Vérifions maintenant leur ordre interne dans le dictionnaire de données avec la table USER_TAB_COLS.
Les champs intéressants sont les suivants :
         COLUMN_ID : sequence number of the column as created
         SEGMENT_COLUMN_ID : sequence number of the column in the segment
         INTERNAL_COLUMN_ID : internal sequence number of the column

Le SELECT ci-dessous montre que le numéro des colonnes correspond bien à l'ordre du CREATE TABLE.
          SQL> select table_name, column_name, column_id, segment_column_id, internal_column_id from user_tab_cols where table_name = 'TEST01' order by  column_name;
         TABLE_NAME          COLUMN_NAME   COLUMN_ID    SEGMENT_COLUMN_ID     INTERNAL_COLUMN_ID
         ------------------------------ ------- ----------------- --------------------------------
         TEST01                   COL1     1            1                     1
         TEST01                   COL2     2            2                     2
         TEST01                   COL3     3            3                     3
         TEST01                   COL4     4            4                     4
         TEST01                   COL5     5            5                     5         

============================================================================================
Modification de la visibilité des colonnes et impacts.
============================================================================================
Nous rendons maintenant les colonnes COL1 et COL3 invisibles. Avec un DESC, ces colonnes sont absentes. Idem avec le SELECT *.
          SQL> ALTER TABLE TEST01 MODIFY COL1 INVISIBLE;
          table TEST01 modifié(e).

          SQL> ALTER TABLE TEST01 MODIFY COL3 INVISIBLE;
          table TEST01 modifié(e).

          SQL> DESC TEST01
          Nom  NULL Type             
          ---- ---- -----------------
          COL2      VARCHAR2(10 CHAR)
          COL4      DATE             
          COL5      TIMESTAMP(6)

         SQL> select * from test01;
         COL2          COL4               COL5
         aucune ligne sélectionnée

Les colonnes invisibles sont maintenant rendues visibles.
         SQL> ALTER TABLE TEST01 MODIFY COL1 VISIBLE;
         table TEST01 modifié(e).

         SQL> ALTER TABLE TEST01 MODIFY COL3 VISIBLE;
         table TEST01 modifié(e).

BINGO! les colonnes traitées sont bien en queue de table, ce qui veut dire que leur position a été affectée en ayant été rendues invisibles puis visibles.
         SQL> DESC TEST01
         Nom  NULL Type             
         ---- ---- -----------------
         COL2      VARCHAR2(10 CHAR)
         COL4      DATE             
         COL5      TIMESTAMP(6)     
         COL1      NUMBER(4,2)      
         COL3      NUMBER(6,2)     

         SQL>select * from test01;
         COL2           COL4          COL5          COL1               COL3
         aucune ligne sélectionnée

Validons avec le dictionnaire de données qu'il ne s'agit pas seulement d'un bug  d'affichage mais une vraie modification dans le dictionnaire de données. La colonne COLUMN_ID est celle qui a été mise à jour lors des modifications alors que la colonne INTERNAL_COLUMN_ID garde l'ordre de création des colonnes. On note aussi que la colonne SEGMENT_COLUMN_ID n'a pas été modifiée.
          SQL>x select table_name, column_name, column_id, segment_column_id, internal_column_id from user_tab_cols where table_name = 'TEST01' order by  column_name;
         TABLE_NAME     COLUMN_NAME   COLUMN_ID   SEGMENT_COLUMN_ID    INTERNAL_COLUMN_ID
         ------------------------------ ------------------------------ ---------- ---------
         TEST01         COL1          4           1                    1
         TEST01         COL2          1           2                    2
         TEST01         COL3          5           3                    3
         TEST01         COL4          2           4                    4
         TEST01         COL5          3           5                    5