[20141116]12c下增加字段与缺省值.txt
--前一段时间写了一篇表增加字段与缺省值的blog.
--链接如下：
http://blog.itpub.net/267265/viewspace-1257035/
--12G 增加字段带缺省值,可以很快完成,不需要update表.实际上是增加一个隐含字段，通过位与的方式确定取值方式。
当时的总结如下:
--增加字段带缺省值,在12c下很快完成,不需修改表,但是以后的插入要多付出2个字节的代价(不会增加9个字段带缺省值的吧^_^)
--它通过增加1个隐含字段里面的bit来表示这些字段从那里来读取.
--后续的插入即使insert不带这些字段,其缺省值也插入数据块中.
--如果以后修改了字段的缺省值，会出现什么情况呢？再做一个测试。
1.建立测试环境:
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t (id number);
insert into t values (1);
commit ;
alter table t add (name varchar2(10) default 'test');
2.测试1：
SCOTT@test01p> select t.* , SYS_NC00002$ from t;
ID NAME SYS_NC0000
---------- -------------------- ----------
1 test
SCOTT@test01p> @dpc '' projection
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 9gw3r0y39kyvp, child number 0
-------------------------------------
select t.* , SYS_NC00002$ from t
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | TABLE ACCESS FULL| T | 1 | 3 (0)|
--------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."ID"[NUMBER,22], "SYS_NC00002$"[RAW,126],
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL,NVL("T"."NAME",'test'),'0',NVL("T"."NAME",'test'),'1',"T"."NAME")[10]
--第2字段的取值实际上执行的是DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL,NVL("T"."NAME",'test'),'0',NVL("T"."NAME",'test'),'1',"T"."NAME")[10]。
select obj#,col#,segcol#,name,default$,type# from sys.col$ where
obj# in ( select object_id from dba_objects where owner=user and object_name='T')
order by col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
---------- ---------- ---------- -------------------- ------------------------------ ----------
96948 0 2 SYS_NC00002$ 23
96948 1 1 ID 2
96948 2 3 NAME 'test' 1
--上次的blog已经提到增加隐含字段SYS_NC00002$,类型raw。SYS_NC00002$为NULL时，取值来之：
SCOTT@test01p> select * from sys.ecol$ where tabobj#= 96948 and colnum = 3;
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- -------------------- ----------
96948 3 74657374 0
SCOTT@test01p> host cat d:\tools\sql\conv_c.sql
select utl_raw.cast_to_varchar2(lower('&1')) c60 from dual;
SCOTT@test01p> @conv_c 74657374
C60
------------------------------------
test
3.测试2：
--增加记录看看。
insert into t values(2,'aaaa');
commit;
SCOTT@test01p> select t.* , SYS_NC00002$ from t;
ID NAME SYS_NC0000
---------- -------------------- ----------
1 test
2 aaaa 01
--SYS_NC00002$='01',表示从块中取值。
--如果这时修改name的缺省值，会出现什么情况呢？
--alter table t modify name varchar2(10) default 'lfree';
SCOTT@test01p> alter table t modify name default 'lfree';
Table altered.
SCOTT@test01p> select t.* , SYS_NC00002$ from t;
ID NAME SYS_NC0000
---------- -------------------- ----------
1 test
2 aaaa 01
--ID=1的name依旧是'test',而不是'lfree'.
select obj#,col#,segcol#,name,default$,type# from sys.col$ where
obj# in ( select object_id from dba_objects where owner=user and object_name='TX')
order by col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
---------- ---------- ---------- -------------------- ------------------------------ ----------
96948 0 2 SYS_NC00002$ 23
96948 1 1 ID 2
96948 2 3 NAME 'lfree' 1
SCOTT@test01p> select * from sys.ecol$ where tabobj#= 96948 ;
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- -------------------- ----------
96948 3 74657374 0
--也就是讲这两个值是分开存放的。
SCOTT@test01p> insert into t(id) values(3);
1 row created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select t.* , SYS_NC00002$ from t;
ID NAME SYS_NC0000
---------- -------------------- ----------
1 test
2 aaaa 01
3 lfree 01
4.增加超过9个字段会出现什么情况呢？
--前面的blog我提到增加字段带缺省值,在11G下很快完成,不需修改表,但是以后的插入要多付出2个字节的代价(不会增加9个字段带缺省
--值的吧^_^)
--正常不会增加9个以上的字段，好像很少有这种情况出现把，如果真出现会是什么情况呢？继续测试：
alter table t add (name1 varchar2(10) default 'test1');
alter table t add (name2 varchar2(10) default 'test2');
alter table t add (name3 varchar2(10) default 'test3');
alter table t add (name4 varchar2(10) default 'test4');
alter table t add (name5 varchar2(10) default 'test5');
alter table t add (name6 varchar2(10) default 'test6');
alter table t add (name7 varchar2(10) default 'test7');
alter table t add (name8 varchar2(10) default 'test8');
insert into t(id) values(4);
ommit ;
SCOTT@test01p> select t.* , SYS_NC00002$ from t;
ID NAME NAME1 NAME2 NAME3 NAME4 NAME5 NAME6 NAME7 NAME8 SYS_NC0000
---------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 test test1 test2 test3 test4 test5 test6 test7 test8
2 aaaa test1 test2 test3 test4 test5 test6 test7 test8 01
3 lfree test1 test2 test3 test4 test5 test6 test7 test8 01
4 lfree test1 test2 test3 test4 test5 test6 test7 test8 FF01
--实际上就是隐含列的长度增加1位吧了。
5.分区交换的问题：
另外要注意的一个问题，就是这种表做分区交换的时候会出现问题，因为它存在一个隐含字段，具体看下一篇blog.