[20141117]12c下增加字段与缺省值--分区交换.txt

--前一段时间写了一篇表增加字段与缺省值的blog.
--链接如下：
http://blog.itpub.net/267265/viewspace-1257035/

--12C 增加字段带缺省值,可以很快完成,不需要update表.实际上是增加一个隐含字段，通过位与的方式确定取值方式。

当时的总结如下:
--增加字段带缺省值,在11G下很快完成,不需修改表,但是以后的插入要多付出2个字节的代价(不会增加9个字段带缺省值的吧^_^)
--它通过增加1个隐含字段里面的bit来表示这些字段从那里来读取.
--后续的插入即使insert不带这些字段,其缺省值也插入数据块中.

--如果这种表做分区交换的时候会出现问题，因为它存在一个隐含字段，表结构实际上不一样的。应该会报错。自己做一个测试

1.建立测试环境：

SCOTT@ztest> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

CREATE TABLE t (id NUMBER, name VARCHAR2 (30))
PARTITION BY RANGE (id)
(
PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20),
PARTITION PMAX VALUES LESS THAN (maxvalue)
);

insert into t values (1,'a');
insert into t values (2,'b');

insert into t values (11,'c');
insert into t values (12,'d');
commit ;

CREATE TABLE tx (id NUMBER);
insert into tx values (31);
commit ;
alter table tx add (name varchar2(30) default 'test');

2.测试：
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#;

SCOTT@ztest> /
      OBJ#       COL#    SEGCOL# NAME                 DEFAULT$                            TYPE#
---------- ---------- ---------- -------------------- ------------------------------ ----------
     92455          0          2 SYS_NC00002$                                                23
     92455          1          1 ID                                                           2
     92455          2          3 NAME                 'test'                                  1

--两个表结构内部实际不一样的。

SCOTT@ztest> alter table t exchange partition pmax with table tx;
alter table t exchange partition pmax with table tx
                                                 *
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

SCOTT@ztest> create table ty as select * from tx;
Table created.

SCOTT@ztest> alter table t exchange partition pmax with table ty;
Table altered.

SCOTT@ztest> select * from t partition(pmax);
        ID NAME
---------- --------------------
        31 test

SCOTT@ztest> select * from ty;
no rows selected

--这样才能发生交换。

3.另外更正我前面链接的错误：

http://blog.itpub.net/267265/viewspace-1257035/
http://blog.itpub.net/267265/viewspace-1335561/

--实际上仅仅对12c有效，而不是11g，记忆有点乱。老了..........
--在11g下仅仅能这样执行。补充测试：

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

CREATE TABLE t(id NUMBER);
insert into t values (11);
commit ;
alter table t add (name varchar2(30) default 'test');

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#
---------- ---------- ---------- -------------------- -------------------- ----------
    293168          1          1 ID                                                 2
    293168          2          2 NAME                 'test'                        1

--并没有隐含列。也就是这样执行要修改块内的结构。

SCOTT@test> select * from sys.ecol$ where tabobj#= 293168;
no rows selected

--11g 支持的是这种方式，带有not null：
SCOTT@test> alter table t add (name1 varchar2(30) default 'xxxx' not null);
Table altered.

      OBJ#       COL#    SEGCOL# NAME                 DEFAULT$                  TYPE#
---------- ---------- ---------- -------------------- -------------------- ----------
    293168          1          1 ID                                                 2
    293168          2          2 NAME                 'test'                        1
    293168          3          3 NAME1                'xxxx'                        1

SCOTT@test> column BINARYDEFVAL format a30
SCOTT@test> select * from sys.ecol$ where tabobj#= 293168;
   TABOBJ#     COLNUM BINARYDEFVAL
---------- ---------- ------------------------------
    293168          3 78787878

--也没有隐含列。不过第3列的缺省值从sys.ecol$获得。不过这样执行不需要要修改块内的数据。

SCOTT@test> select * from t;
        ID NAME                 NAME1
---------- -------------------- ----------
        11 test                 xxxx

SCOTT@test> @dpc '' projection
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  89km4qj1thh13, child number 0
-------------------------------------
select * 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], "T"."NAME"[VARCHAR2,30],NVL("T"."NAME1",'xxxx')[30]

--name1字段的取值使用NVL("T"."NAME1",'xxxx')[30],也就是nvl函数。