oracle 12c new feature 隐藏列(invisible columns)

原创 Anbob 2013-12-06

591

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                    4
anbob@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 values
anbob@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                   14
anbob@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 number
INTCOL# – internal column number
SEGCOL# – segment column number


-- 修改前，所有列正常显示
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             0
ID1            2                    2                    2             0
ID2            3                    3                    3             0
ID3            4                    4                    4             0
-- 修改后，隐藏ID1,ID2列
sys@ORA12C>/
NAME        COL#              INTCOL#              SEGCOL# TO_CHAR(PROPE
---------- ----- -------------------- -------------------- -------------
ID             1                    1                    1             0
ID1            0                    2                    2     400000020
ID2            0                    3                    3     400000020
ID3            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 values

anbob@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             0
ID1            4                    2                    2             0
ID2            3                    3                    3             0
ID3            2                    4                    4             0
anbob@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                   12
anbob@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                   21
sys@ORA12C>alter system dump datafile 10 block  143;

block_row_dump:
tab 0, row 0, @0x1f89
tl: 15 fb: --H-FL-- lb: 0x0  cc: 4
col  0: [ 2]  c1 02
col  1: [ 2]  c1 03
col  2: [ 2]  c1 04
col  3: [ 2]  c1 05
tab 0, row 1, @0x1f7a
tl: 15 fb: --H-FL-- lb: 0x0  cc: 4
col  0: [ 2]  c1 0c
col  1: [ 2]  c1 0d
col  2: [ 2]  c1 0e
col  3: [ 2]  c1 0f
tab 0, row 2, @0x1f6b
tl: 15 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 2]  c1 16
col  1: [ 2]  c1 17
col  2: [ 2]  c1 18
col  3: [ 2]  c1 19
end_of_block_dump
复制

note:
显然列不是基于此列的顺序存储的，也许是 INTCOL#或者SEGCOL# ,对于列值的还原可以参考http://www.anbob.com/archives/1466.html
Summary:
对于新特性要了解其原理后再使用，隐藏列控制了select* 和insert values未指定列名时的显示，对于隐藏列显示指定列名还是可以显示，对于列顺序错乱目前保守的做法是可以rename原表，建原表名view 指定列顺序或逐次invisible visible，如果自己测试可以尝试上面我提到的直接update col$.col#列。

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

oracle 12c new feature 隐藏列(invisible columns)

评论

相关阅读