[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函数。