[20160713]修改表结构增加1列与缺省值.txt
--昨天看yangtingkun的blog，提到一个非常有趣的测试，链接：
--yangtingkun.net/?p=1483,我自己做一些补充测试：
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table t (id number,name varchar2(20));
insert into t values (1,'a');
commit ;
SCOTT@book> select rowid,t.* from t;
ROWID ID NAME
------------------ ---------- --------------------
AAAXNfAAEAAAAL7AAA 1 a
SCOTT@book> @ &r/rowid AAAXNfAAEAAAAL7AAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
95071 4 763 0 4,763 alter system dump datafile 4 block 763 ;
2.11g开始有一个新特性，增加一列加缺省值，可以很快完成。测试如下：
SCOTT@book> alter table t add memo1 varchar2(20) default 'test' not null;
Table altered.
SCOTT@book> select rowid,t.* from t;
ROWID ID NAME MEMO1
------------------ ---------- -------------------- --------------------
AAAXNfAAEAAAAL7AAA 1 a test
--实际上memo1='test',并没有保存在对应的数据块中。通过bbed观察：
SCOTT@book> alter system checkpoint ;
System altered.
BBED> set dba 4,763
DBA 0x010002fb (16777979 4,763)
BBED> x /rncc *kdbr[0]
rowdata[0] @8180
----------
flag@8180: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8181: 0x01
cols@8182: 2
col 0[2] @8183: 1
col 1[1] @8186: a
3.如果这样增加1列如何呢？
SCOTT@book> alter table t add memo2 varchar2(20) default 'test2' ;
Table altered.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> select rowid,t.* from t;
ROWID ID NAME MEMO1 MEMO2
------------------ ---------- -------------------- -------------------- --------------------
AAAXNfAAEAAAAL7AAA 1 a test test2
BBED> x /rncc *kdbr[0]
rowdata[0] @8165
----------
flag@8165: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8166: 0x02
cols@8167: 4
col 0[2] @8168: 1
col 1[1] @8171: a
col 2[0] @8173: *NULL*
col 3[5] @8174: test2
--可以发现相应块的信息已经修改，所以前面第1种修改方式会非常块，第2种要修改数据块。注意1个细节col 2[0] @8173: *NULL*,块中保存信息是NULL。
--而上面查询显示的是MEMO1='test'.如果你使用参数advanced看执行计划：
SCOTT@book> @ &r/dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 63713zdrgk2ky, child number 0
-------------------------------------
select rowid,t.* from t
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| T | 1 | 61 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - ROWID[ROWID,10], "T"."ID"[NUMBER,22], "T"."NAME"[VARCHAR2,20],
NVL("T"."MEMO1",'test')[20], "T"."MEMO2"[VARCHAR2,20]
--//注意Column Projection Information (identified by operation id):，NVL("T"."MEMO1",'test')，也就是实际"T"."MEMO1"是NULL，显示的是'test'.
4.但是oracle忽略一种情况，如果执行default ''呢？执行如下：
SCOTT@book> alter table t add memo3 varchar2(20) default null not null;
alter table t add memo4 varchar2(20) default null not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
SCOTT@book> alter table t add memo3 varchar2(20) default '' not null;
Table altered.
--//default null not null;无法通过，但是oracle忽略的'' 就是NULL。
--//这是一个矛盾的定义，缺省是''实际是NULL与后面的not null冲突。
SCOTT@book> select rowid,t.* from t;
ROWID ID NAME MEMO1 MEMO2 MEMO3
------------------ ---------- -------------------- -------------------- -------------------- --------------------
AAAXNfAAEAAAAL7AAA 1 a test test2
SCOTT@book> @ &r/dpc '' advanced
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - ROWID[ROWID,10], "T"."ID"[NUMBER,22], "T"."NAME"[VARCHAR2,20],
NVL("T"."MEMO1",'test')[20], "T"."MEMO2"[VARCHAR2,20],
"T"."MEMO3"[VARCHAR2,20]
--//这里的"T"."MEMO3"是直接显示。
SCOTT@book> column BINARYDEFVAL format a30
SCOTT@book> select * from sys.ecol$;
TABOBJ# COLNUM BINARYDEFVAL
---------- ---------- ------------------------------
95071 3 74657374
SCOTT@book> select dump('test',1016) from dual ;
DUMP('TEST',1016)
-----------------------------------------------
Typ=96 Len=4 CharacterSet=ZHS16GBK: 74,65,73,74
--并没有memo3的缺省定义。
--这样就出现了矛盾的输出。
SCOTT@book> set null null
SCOTT@book> select rowid,t.* from t where memo3 is null;
no rows selected
SCOTT@book> select rowid,t.* from t where memo3 is not null;
ROWID ID NAME MEMO1 MEMO2 MEMO3
------------------ ---------- -------------------- -------------------- -------------------- --------------------
AAAXNfAAEAAAAL7AAA 1 a test test2 null