In Oracle 12c, there’s a new feature called table’s invisible columns. As indexes in oracle 11g, you can now makes columns visible or invisible.
This feature has a strange behavior that we will see later and see how it really works.
First, I created a table with 3 columns:
SQL> create table test(a number, b number, c number);
Table created.
SQL> insert into test(a,b,c) values(1,2,3);
1 row created.
SQL> desc test
Name Null? Type
----------------------- -------- ----------------
A NUMBER
B NUMBER
C NUMBER
And then, I modified the table to transform the B column as invisible:
SQL> alter table test modify (b invisible);
Table altered.
SQL> desc test
Name Null? Type
----------------------- -------- ----------------
A NUMBER
C NUMBER
SQL> select * from test;
A C
---------- ----------
1 3
If we tried to specifically query the B column, data appear:
SQL> select a,b,c from test;
A B C
---------- ---------- ----------
1 2 3
If we have a look deep inside the block, we can see that the visibility of a specific column is defined as the dictionary level and not at the block level:
SQL> select dbms_rowid.rowid_relative_fno(rowid) File#,
2 dbms_rowid.rowid_block_number(rowid) Block#
3 from test;
FILE# BLOCK#
---------- ----------
1 99241
SQL> alter system dump datafile 1 block 99241;
System altered.
SQL>
data_block_dump,data header at 0x7f59e1a66a5c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x7f59e1a66a5c
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f94
avsp=0x1f80
tosp=0x1f80
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f94
block_row_dump:
tab 0, row 0, @0x1f94
tl: 12 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [ 2] c1 03 <<<<<<< OUR B COLUMN
col 2: [ 2] c1 04
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 99241 maxblk 99241
SQL> select dump(a,16),dump(b,16), dump(c,16) from test;
DUMP(A,16) DUMP(B,16) DUMP(C,16)
----------------- ----------------- -----------------
Typ=2 Len=2: c1,2 Typ=2 Len=2: c1,3 Typ=2 Len=2: c1,4
Now, let’s modify the B column to be visible:
SQL> alter table test modify (b visible);
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
C NUMBER
B NUMBER
Well, the columns order seems to have changed. What is more funny is when you try now to insert a new row without specifying the column, it takes the new order definition:
SQL> insert into test values(1,2,3);
1 row created.
SQL> select * from test;
A C B
---------- ---------- ----------
1 3 2
1 2 3
Now, let’s dump the block (after a necessary checkpoint).
data_block_dump,data header at 0x7f59e1a66a5c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x7f59e1a66a5c
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f88
avsp=0x1f72
tosp=0x1f72
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f94
0x14:pri[1] offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1f94
tl: 12 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [ 2] c1 03
col 2: [ 2] c1 04
tab 0, row 1, @0x1f88
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 02
col 1: [ 2] c1 04
col 2: [ 2] c1 03
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 99241 maxblk 99241
We confirm here that column visibility and column order definition are not defined at the block level. So it may be at the dictionary level. Let’s have a closer look to the col$ system table:
SQL> select o.obj#,col#,segcol#,o.name object_name,c.name col_name
2 from obj$ o, col$ c
3 where o.obj#=c.obj#
4 and o.name='TEST'
5 /
OBJ# COL# SEGCOL# OBJECT_NAME COL_NAME
---------- ---------- ---------- -------------------- ------------------------------
92056 1 1 TEST A
92056 3 2 TEST B
92056 2 3 TEST C
Column position in the segment (ie. in the block) is defined by the SEGCOL# column. COL# column defines the rank of the column when you perform a SELECT * or an INSERT without specifying the corresponding column. The COL# value can change depending on visibility modifications made on the column.
So be very careful about apps code that makes inserts without column definition (INSERT INTO t VALUES (val1,val2, … , valN)). This could trigger new errors or worse, involve data integrity errors (like in the previous example).
Data ... as usual 