Oracle12c -Invisible Columns 测试

http://www.anbob.com/archives/2083.html

Oracle 12c release 1版本引入新功能可隐藏列(invisible columns),这个特性有很多的争议，不明白为什么ORACLE会推出这个特性，因为这个特性可能会带来很多麻烦，列默认是显示的可以通过create table 或alter table 控制此特性，下面我来演示一下此特性。

anbob@PDB1>create table test(id int,id1 int,id2 int,id3 int);Table created.anbob@PDB1>insert into test values(1,2,3,4);1 row created.anbob@PDB1>commit;Commit complete.anbob@PDB1>desc test; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- ID                                                 NUMBER(38) ID1                                                NUMBER(38) ID2                                                NUMBER(38) ID3                                                NUMBER(38) anbob@PDB1>select * from test;                  ID                  ID1                  ID2                  ID3-------------------- -------------------- -------------------- --------------------                   1                    2                    3                    4anbob@PDB1>alter table test modify id2 invisible;Table altered.anbob@PDB1>select * from test;                  ID                  ID1                  ID3-------------------- -------------------- --------------------                   1                    2                    4				   anbob@PDB1>desc test; Name                          Null?    Type ----------------------------- -------- -------------------- ID                                     NUMBER(38) ID1                                    NUMBER(38) ID3                                    NUMBER(38)anbob@PDB1>alter table test modify id1 invisible;Table altered.anbob@PDB1>desc test; Name                          Null?    Type ----------------------------- -------- -------------------- ID                                     NUMBER(38) ID3                                    NUMBER(38) anbob@PDB1>insert into test values(11,12,13,14);insert into test values(11,12,13,14)            *ERROR at line 1:ORA-00913: too many valuesanbob@PDB1>insert into test(id,id1,id2,id3) values(11,12,13,14);1 row created.anbob@PDB1>commit;Commit complete.anbob@PDB1>select * from test;                  ID                  ID3-------------------- --------------------                   1                    4                  11                   14anbob@PDB1>select id,id1,id2,id3 from test;                  ID                  ID1                  ID2                  ID3-------------------- -------------------- -------------------- --------------------                   1                    2                    3                    4                  11                   12                   13                   14
note:从上面可以看出这个特性隐藏的也只是对于desc 和 select “*” 的显示和未指定列名insert 时隐藏列无效, 如果把隐藏列重新显示会有什么影响？接着下面演示
查看两列隐藏后的建表DDL
anbob@PDB1>select dbms_metadata.get_ddl('TABLE','TEST') from dual;DBMS_METADATA.GET_DDL('TABLE','TEST')--------------------------------------------------------------------------------  CREATE TABLE "ANBOB"."TEST"   (    "ID1" NUMBER(*,0) INVISIBLE,        "ID2" NUMBER(*,0) INVISIBLE,        "ID" NUMBER(*,0),        "ID3" NUMBER(*,0)   ) SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT Flash_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS"  
NOTE:也看出此时的建表语句列顺序已经是变化的。
看一下col$基表的变化，col#是显示的顺序，在我以前的日志中有写个修改列顺序”oracle 修改列的前后顺序”http://www.anbob.com/archives/757.html
tip:The data dictionary COL$ table contains one row for each table column. Each row has three column numbers:COL# – external column numberINTCOL# – internal column numberSEGCOL# – segment column number
-- 修改前，所有列正常显示sys@ORA12C>select name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') from col$ where obj#=91771NAME        COL#              INTCOL#              SEGCOL# TO_CHAR(PROPE---------- ----- -------------------- -------------------- -------------ID             1                    1                    1             0ID1            2                    2                    2             0ID2            3                    3                    3             0ID3            4                    4                    4             0-- 修改后，隐藏ID1,ID2列sys@ORA12C>/NAME        COL#              INTCOL#              SEGCOL# TO_CHAR(PROPE---------- ----- -------------------- -------------------- -------------ID             1                    1                    1             0ID1            0                    2                    2     400000020ID2            0                    3                    3     400000020ID3            2                    4                    4             0
note:注意修改隐藏列会把col#更新为0,同时其它不为0值并且大于修改col#的列号col#会相应减1
anbob@PDB1>alter table test drop column id4 ;Table altered.anbob@PDB1>insert into test values(11,12,13,14);insert into test values(11,12,13,14)            *ERROR at line 1:ORA-00913: too many valuesanbob@PDB1>insert into test(id,id1,id2,id3) values(11,12,13,14);1 row created.anbob@PDB1>commit;Commit complete.anbob@PDB1>alter table test modify id2 visible;Table altered.anbob@PDB1>alter table test modify id1 visible;Table altered.sys@ORA12C>select name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') from col$ where obj#=91771;NAME        COL#              INTCOL#              SEGCOL# TO_CHAR(PROPE---------- ----- -------------------- -------------------- -------------ID             1                    1                    1             0ID1            4                    2                    2             0ID2            3                    3                    3             0ID3            2                    4                    4             0anbob@PDB1>alter table test modify id1 invisible;Table altered.anbob@PDB1>alter table test add id4 int;Table altered.anbob@PDB1>alter table test modify id1 visible;Table altered.anbob@PDB1>desc test; Name             Null?    Type ---------------- -------- --------------------------------- ID                        NUMBER(38) ID3                       NUMBER(38) ID2                       NUMBER(38) ID4                       NUMBER(38) ID1                       NUMBER(38)anbob@PDB1>select * from test;                  ID                  ID3                  ID2                  ID4                  ID1-------------------- -------------------- -------------------- -------------------- --------------------                   1                    4                    3                                         2                  11                   14                   13                                        12
note:注意此时的列顺序没有还原为原来的顺序，如果程序使用的select * from 方式而控制列顺序显示，将让人误解。如果是insert tablename values() 方式呢? 如果对于一些老程序而使用了insert 未指定列， 而且你使用了此特性做了一个隐藏再显示的小测试， 这下麻烦可就大了， 对于只隐藏可能会提示你列数不匹配，如果隐藏再显示，导致列顺序错乱，而有恰巧列类型一致数据库insert 成功，那数据写错了列又无提示，对于客户数据是灾难性的。
anbob@PDB1>alter table test drop column id4 ;Table altered.anbob@PDB1>select * from test;                  ID                  ID3                  ID2                  ID1-------------------- -------------------- -------------------- --------------------                   1                    4                    3                    2                  11                   14                   13                   12anbob@PDB1>insert into test(id,id1,id2,id3) values(21,22,23,24);1 row created.anbob@PDB1>commit;Commit complete.anbob@PDB1>select * from test;                  ID                  ID3                  ID2                  ID1-------------------- -------------------- -------------------- --------------------                   1                    4                    3                    2                  11                   14                   13                   12                  21                   24                   23                   22
note:对于程序严格指定了列名的就没有任何影响，下面我们来dump 一下存储结构，看这些顺序会不会影响列的存储顺序。
anbob@PDB1>select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file#, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,id from test;               FILE#               BLOCK#                   ID-------------------- -------------------- --------------------                  10                  143                    1                  10                  143                   11                  10                  143                   21sys@ORA12C>alter system dump datafile 10 block  143;block_row_dump:tab 0, row 0, @0x1f89tl: 15 fb: --H-FL-- lb: 0x0  cc: 4col  0: [ 2]  c1 02col  1: [ 2]  c1 03col  2: [ 2]  c1 04col  3: [ 2]  c1 05tab 0, row 1, @0x1f7atl: 15 fb: --H-FL-- lb: 0x0  cc: 4col  0: [ 2]  c1 0ccol  1: [ 2]  c1 0dcol  2: [ 2]  c1 0ecol  3: [ 2]  c1 0ftab 0, row 2, @0x1f6btl: 15 fb: --H-FL-- lb: 0x1  cc: 4col  0: [ 2]  c1 16col  1: [ 2]  c1 17col  2: [ 2]  c1 18col  3: [ 2]  c1 19end_of_block_dump
note:显然列不是基于此列的顺序存储的，也许是 INTCOL#或者SEGCOL# ,对于列值的还原可以参考http://www.anbob.com/archives/1466.html
Summary:对于新特性要了解其原理后再使用，隐藏列控制了select* 和insert values未指定列名时的显示，对于隐藏列显示指定列名还是可以显示，对于列顺序错乱目前保守的做法是可以rename原表，建原表名view 指定列顺序或逐次invisible visible，如果自己测试可以尝试上面我提到的直接update col$.col#列。