[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.