[20160713]修改表結構增加1列與預設值.txt

[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