Oracle db12c : Les colonnes invisibles

Suite à ma participation au workshop Oracle sur les News Features db12c, je me suis un peu attardé sur la fonctionnalité de colonne invisible.
Au vu du nombre de nouveauté apportées par Oracle dans sa nouvelle version, certains chapitres sont survolés.
Je suis alors parti en quête de vérité: la fonctionnalité de colonne invisible est elle une révolution ?
1- La mise en place
Sur une nouvelle table, la mise en place de cette option est assez simple dans mesure où le mot clef INVISBLE doit suivre la colonne à la création de la table:
SQL> table easytable_1 (
col1 number,
col2 varchar2(10),
col3 varchar2(10) INVISIBLE,
col4 varchar2(10)
);

Sur une table existante:
SQL> create table easytable_1 (
col1 number,
col2 varchar2(10),
col3 varchar2(10),
col4 varchar2(10)
);

Un simple alter table active l’option sur la colonne désignée.
SQL> alter table easytable_1 modify (col3 INVISIBLE);
2- Les constats
Continuons notre cheminement en partant d’une table « normale » contenant quelques lignes:
SQL> create table easytable_1 (
col1 number,
col2 number,
col3 varchar2(10),
col4 varchar2(10)
);

SQL> desc easytable_1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COL1 NUMBER
COL2 NUMBER
COL3 VARCHAR2(10)
COL4 VARCHAR2(10)

SQL> begin
for i in 1..5 loop
insert into easytable_1 select i,trunc(dbms_random.value(0,100000)), dbms_random.string(‘L’,10), dbms_random.string(‘U’,10) from dual;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> select * from easytable_1;
COL1 COL2 COL3 COL4
———- ———- ———- ———-
1 17923 ivmrpqwlbb JHLGEVYXZW
2 67868 bvnruvpcua RKCUNPXVZS
3 31698 pzhaqgkgag QARZQIGRKX
4 14682 lddvaezvqf OWYBMTCAWW
5 73051 usrakbfavy LVLKALZMXQ
Masquons la colonne COL3:
SQL> alter table easytable_1 modify (col3 INVISIBLE);
Regardons l’impact sur la description de la table et son contenu:
SQL> desc easytable_1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COL1 NUMBER
COL2 NUMBER
COL4 VARCHAR2(10)

SQL> select * from easytable_1;
COL1 COL2 COL4
———- ———- ———-
1 17923 JHLGEVYXZW
2 67868 RKCUNPXVZS
3 31698 QARZQIGRKX
4 14682 OWYBMTCAWW
5 73051 LVLKALZMXQ
La colonne COL3 n’est donc plus visible. Mais est elle vraiment invisible ?
Heureusement non car l’intérêt d’avoir une colonne et donc des données invisibles n’apporte que peu d’avantage.
Oracle nous donne la possibilité de voir les colonnes invisibles en positionnant le nouveau switch COLINVISIBLE:
SQL> set COLINVISIBLE ON
SQL> desc easytable_1
Name Null? Type
—————————————————– ——– ————————————
COL1 NUMBER
COL2 NUMBER
COL4 VARCHAR2(10)
COL3 (INVISIBLE) VARCHAR2(10)
Les plus attentifs remarquerons que la colonne COL3 est maintenant bien visible mais est positionnée à la fin de le description.
Nous pouvons également consulter l’ensemble de la table en nommant explicitement les colonnes:
SQL> select COL1,COL2,COL3,COL4 from easytable_1;
COL1 COL2 COL3 COL4
———- ———- ———- ———-
1 17923 ivmrpqwlbb JHLGEVYXZW
2 67868 bvnruvpcua RKCUNPXVZS
3 31698 pzhaqgkgag QARZQIGRKX
4 14682 lddvaezvqf OWYBMTCAWW
5 73051 usrakbfavy LVLKALZMXQ
En rendant de nouveau la colonne COL3 visible:
SQL> alter table easytable_1 modify (COL3 VISIBLE);
SQL> select * from easytable_1;
COL1 COL2 COL4 COL3
———- ———- ———- ———-
1 17923 JHLGEVYXZW ivmrpqwlbb
2 67868 RKCUNPXVZS bvnruvpcua
3 31698 QARZQIGRKX pzhaqgkgag
4 14682 OWYBMTCAWW lddvaezvqf
5 73051 LVLKALZMXQ usrakbfavy
Nous remarquons que lors du select *, la colonne COL3 être la dernière colonne de la sélection.
Attention toute fois à certains point:
Mettons nous à la place d’une personne qui tente d’insérer une ligne dans une table portant une colonne invisible après avoir pris soin de vérifier sa structure:
SQL> desc easytable_1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COL1 NUMBER
COL2 NUMBER
COL4 VARCHAR2(10)

SQL> insert into easytable_1 values (10, 100, ‘easy’);
1 row created.
SQL> insert into easytable_1 values (20, 200, ‘team’);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from easytable_1;
SQL> select * from easytable_1;
COL1 COL2 COL4
———- ———- ———-
1 17923 JHLGEVYXZW
2 67868 RKCUNPXVZS
3 31698 QARZQIGRKX
4 14682 OWYBMTCAWW
5 73051 LVLKALZMXQ
10 100 easy
20 200 team
7 rows selected.
Quelle valeur a pris colonne COL3 ?
SQL> select COL1,COL2,COL3,COL4 from easytable_1;
COL1 COL2 COL3 COL4
———- ———- ———- ———-
1 17923 ivmrpqwlbb JHLGEVYXZW
2 67868 bvnruvpcua RKCUNPXVZS
3 31698 pzhaqgkgag QARZQIGRKX
4 14682 lddvaezvqf OWYBMTCAWW
5 73051 usrakbfavy LVLKALZMXQ
10 100 easy
20 200 team
7 rows selected.
Le champ sera vide. Il ne faut donc pas oublier de prévenir la présence de cette colonne invisible afin de pouvoir insérer des données dans celle ci:
SQL> insert into easytable_1 (COL1,COL2,COL3,COL4) values (300, 3000 ,'invisible', 'easyteam');
1 row created.
SQL> commit;
Commit complete.
SQL> select COL1,COL2,COL3,COL4 from easytable_1;
COL1 COL2 COL3 COL4
———- ———- ———- ———-
1 17923 ivmrpqwlbb JHLGEVYXZW
2 67868 bvnruvpcua RKCUNPXVZS
3 31698 pzhaqgkgag QARZQIGRKX
4 14682 lddvaezvqf OWYBMTCAWW
5 73051 usrakbfavy LVLKALZMXQ
10 100 easy
20 200 team
300 3000 invisible easyteam
Colonnes invisibles qui changent de place lorsqu’on les rend à nouveau visible. N’est ce pas un casse tête pour nous administrateur
En réalité, cela ne change pas grand chose car en interrogeant le dictionnaire de données, nous constatons que la position interne de la colonne est toujours la même bien que sa position d’affichage a changé
SQL> select COLUMN_NAME, COLUMN_ID , INTERNAL_COLUMN_ID, HIDDEN_COLUMN from dba_tab_cols where table_name='EASYTABLE_1';
COLUMN_NAME COLUMN_ID INTERNAL_COLUMN_ID HIDDEN_COLUMN
——————– ———- —————— ————-
COL1 1 1 NO
COL2 2 2 NO
COL3 3 YES
COL4 3 4 NO
SQL> alter table easytest.easytable_1 modify (COL3 visible);
Table altered.
SQL> select COLUMN_NAME, COLUMN_ID , INTERNAL_COLUMN_ID, HIDDEN_COLUMN from dba_tab_cols where table_name=’EASYTABLE_1′;
COLUMN_NAME COLUMN_ID INTERNAL_COLUMN_ID HIDDEN_COLUMN
——————– ———- —————— ————-
COL1 1 1 NO
COL2 2 2 NO
COL3 4 3 NO
COL4 3 4 NO
Un dernier constat qui nous permet de relancer une piste sur l’intérêt des colonnes invisibles notamment dans le cas de grosses tables:
create table easytable_2 (
col1 number,
col2 varchar2(10),
col3 varchar2(10),
col4 varchar2(10),
col5 varchar2(10),
col6 varchar2(10),
col7 varchar2(10),
col8 varchar2(10),
col9 varchar2(10),
col10 varchar2(10),
col11 varchar2(10),
col12 varchar2(10),
col13 varchar2(10),
col14 varchar2(10),
col15 varchar2(10),
col16 varchar2(10)
);

begin
for i in 1..100000 loop
insert into easytable_2 select i,trunc(dbms_random.value(0,100000)), dbms_random.string(‘L’,10), dbms_random.string(‘U’,10),dbms_random.string(‘L’,10), dbms_random.string(‘U’,10),dbms_random.string(‘L’,10), dbms_random.string(‘U’,10),dbms_random.string(‘L’,10), dbms_random.string(‘U’,10),dbms_random.string(‘L’,10), dbms_random.string(‘U’,10),dbms_random.string(‘L’,10), dbms_random.string(‘U’,10),dbms_random.string(‘L’,10), dbms_random.string(‘U’,10) from dual;
end loop;
end;
/
SQL> explain plan for
select * from easytable_2;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
———————————————————————————
Plan hash value: 1510463845
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————
| 0 | SELECT STATEMENT | | 93887 | 10M| 650 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EASYTABLE_2 | 93887 | 10M| 650 (1)| 00:00:01 |
———————————————————————————
Note
—–
– dynamic statistics used: dynamic sampling (level=2)
12 rows selected.
alter table easytable_2 modify(col2 invisible);
alter table easytable_2 modify(col3 invisible);
alter table easytable_2 modify(col5 invisible);
alter table easytable_2 modify(col7 invisible);
alter table easytable_2 modify(col9 invisible);
alter table easytable_2 modify(col11 invisible);
alter table easytable_2 modify(col12 invisible);
alter table easytable_2 modify(col13 invisible);
alter table easytable_2 modify(col14 invisible);
alter table easytable_2 modify(col15 invisible);
SQL> explain plan for
select * from easytable_2;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
———————————————————————————
Plan hash value: 1510463845
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————
| 0 | SELECT STATEMENT | | 93887 | 5042K| 650 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EASYTABLE_2 | 93887 | 5042K| 650 (1)| 00:00:01 |
———————————————————————————
Note
—–
– dynamic statistics used: dynamic sampling (level=2)
12 rows selected.
En effet, lorsque les colonnes sont cachées le volume de données en bytes est beaucoup moins important qu’avec les colonnes visibles.
3- Mes conclusions
L’intêret semble donc plutot d’ordre esthétique et pratique pour les développeurs trop souvent adeptes du SELECT * que révolutionnaire.
Chacun se fera son opinion mais je comprends maintenant pourquoi ce chapitre est plus que survolé lors de la présentation New Features 12c.