oracle 12c new feature 隐藏列(invisible columns)
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#列。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。