在日常的運維工作中，對生產數據表進行DDL操作是一件需要謹慎對待的事情。運維DBA們在進行數據DDL操作的時候，通常要全局考慮，諸如對生產影響、執行時間長度和影響存儲數據等等。 數據列默認值的添加，是DBA們經常頭疼的一個問題。傳統的執行語句，消耗時間
在日常的運維工作中，對生產數據表進行DDL操作是一件需要謹慎對待的事情。運維DBA們在進行數據DDL操作的時候，通常要全局考慮，諸如對生產影響、執行時間長度和影響存儲數據等等。
數據列默認值的添加，是DBA們經常頭疼的一個問題。傳統的執行語句，消耗時間長、資源使用量大，對生產環境影響程度高。采用其他的一些變通方法，又存在操作步驟繁瑣的問題。如何快速的添加一個有默認值的數據列，同時對現有生產環境影響最小，是我們希望達到的一個目標。
本文從操作入手，探討添加default數據列的問題點，最后介紹oracle 11g中對其進行的“革命性”優化。
1、從10g的數據列添加談起
為了實現對比效果，我們首選選擇10g版本的Oracle進行試驗，構造一個相對較大的數據表。
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE?? 10.2.0.1.0???? Production
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
SQL> create table t as select object_id from dba_objects;
表已創建。
SQL> select count(*) from t;
COUNT(*)
———-
? 3220352
數據表t只包括一個數據列，但是數據量大約為320萬條。我們從體積上進行評估如下：
SQL> set timing on;
SQL> select bytes/1024/1024,blocks from dba_segments where wner=’SYS’ and segment_name=’T’;
BYTES/1024/1024??? BLOCKS
————— ———-
??????????? 39????? 4992
已用時間: 00: 00: 00.03
SQL> exec dbms_stats.gather_table_stats(user,’T’,cascade=>true);
PL/SQL過程已成功完成。
已用時間: 00: 00: 00.35
SQL> select blocks from dba_tables where wner=’SYS’ and table_name=’T’;
?? BLOCKS
———-
???? 4883
已用時間: 00: 00: 00.01
Oracle分配給這個段segment的中空間為4992個數據塊，高水位線HWM下的格式化過數據塊為4883。總體積約40M。
下面進行兩種方式的添加數據表默認值列方法，一起觀察一下變化情況。首先是允許為空默認值列的操作。
SQL> alter table t add vc varchar2(100) default ‘TTTTTTTTTTTT’;
表已更改。
已用時間: 00: 34: 37.15
SQL> exec dbms_stats.gather_table_stats(user,’T’,cascade=>true);
PL/SQL過程已成功完成。
已用時間: 00: 00: 03.86
SQL> select bytes/1024/1024,blocks from dba_segments where wner=’SYS’ and segment_name=’T’;
BYTES/1024/1024??? BLOCKS
————— ———-
?????????? 208???? 26624
已用時間: 00: 00: 00.06
SQL> select blocks from dba_tables where wner=’SYS’ and table_name=’T’;
?? BLOCKS
———-
??? 25864
已用時間: 00: 00: 00.01
果然是一個費時的操作，添加一個數據列默認值，總共消耗了近30分鐘時間。原有數據表的體積也發生的膨脹，從原來的不到40M，上升到了208M。
這個現象告訴我們，當我們添加一個有default值的數據列，并且是直接添加的時候，一些數據被插入到了數據塊中，引起空間膨脹。
在原有的結構下，數據添加到數據塊上是必需的，只有這樣才能將數據列default添加到里面去。
除了這個字句，我們是還可以提供數據列的not null選項，也是可以實現相同的功能的。
SQL> alter table t add vc2 varchar2(100) default ‘TTTTTTTTTTTT’ not null;
表已更改。
已用時間: 00: 15: 58.85
SQL> exec dbms_stats.gather_table_stats(user,’T’,cascade=>true);
PL/SQL過程已成功完成。
已用時間: 00: 00: 36.87
SQL> select bytes/1024/1024,blocks from dba_segments where wner=’SYS’ and segme
nt_name=’T’;
BYTES/1024/1024??? BLOCKS
————— ———-
?????????? 256???? 32768
已用時間: 00: 00: 00.14
SQL> select blocks from dba_tables where wner=’SYS’ and table_name=’T’;
?? BLOCKS
———-
??? 32448
已用時間: 00: 00: 00.04
也是消耗了15分鐘，空間發生了很大程度變化。新空間分配，同時數據行數沒有發生變化，潛在的行遷移（Row Migration）和行鏈接（Row Chaining）是嚴重惡化的！
綜合分析Oracle 10g下的操作：為了添加上數據字段的默認值，Oracle會去訪問每個數據塊上的每個數據行進行數據列拓展工作，這個過程中還伴隨著新空間分配和多余數據行復制。
這類型操作對于生產環境是恐怖的，在整個作業過程中，數據表結構被鎖定，相關業務處理操作阻塞或者緩慢。所以，運維DBA都是選擇在維護窗口或者變通的方法進行處理。
在Oracle 11g環境下，事情有了一些不同。
2、11g下的默認值配置
我們在11g上進行相似操作。
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
PL/SQL Release 11.2.0.1.0 – Production
CORE?????? 11.2.0.1.0??????? Production
構建相似規模的數據表。
SQL> set timing on;
SQL> create table t as select object_id from dba_objects;
Table created
SQL> select count(*) from t;
COUNT(*)
———-
? 3323167
SQL> exec dbms_stats.gather_table_stats(user,’T’,cascade => true);
PL/SQL procedure successfully completed
SQL> select segment_name, bytes/1024/1024, extents,blocks from user_segments where segment_name=’T’;
SEGMENT_NA BYTES/1024/1024?? EXTENTS??? BLOCKS
———- ————— ———- ———-
T????????????????????? 40??????? 55????? 5120
SQL> select NUM_ROWS, BLOCKS from dba_tables where wner=’SCOTT’ and table_name=’T’;
NUM_ROWS??? BLOCKS
———- ———-
? 3323167????? 5041
11g下我們準備了約330萬數據，進行添加非空帶默認值的數據列。
SQL> alter table t add vc2 varchar2(100) default ‘TTTTTTTTTTTT’ ;
alter table t add vc2 varchar2(100) default ‘TTTTTTTTTTTT’
ORA-01013:用戶請求取消當前的操作
在添加defalut列，不指定not null的時候，數據持續時間超過了我們的想象。筆者主動將其斷開了。下面試試添加not null時候。
–1s不到完成操作；
SQL> alter table t add vc varchar2(100) default ‘TTTTTTTTTTTT’ not null;
Table altered
Executed in 0.047 seconds
SQL> exec dbms_stats.gather_table_stats(user,’T’,cascade => true);
PL/SQL procedure successfully completed
SQL> select NUM_ROWS, BLOCKS from dba_tables where wner=’SCOTT’ and table_name=’T’;
NUM_ROWS??? BLOCKS
———- ———-
? 3323167????? 5041
Executed in 0 seconds
SQL> select segment_name, bytes/1024/1024, extents,blocks from user_segments where segment_name=’T’;
SEGMENT_NA BYTES/1024/1024?? EXTENTS??? BLOCKS
———- ————— ———- ———-
T????????????????????? 40??????? 55????? 5120
SQL> select * from t where rownum
OBJECT_ID VC
———- ——————————————————————————–
?????? 20 TTTTTTTTTTTT
?????? 46 TTTTTTTTTTTT
?????? 28 TTTTTTTTTTTT
?????? 15 TTTTTTTTTTTT
（篇幅原因，有省略……）
9 rows selected
我們發現，當執行not null的時候，Oracle以超乎想象的速度完成了過程。并且注意：數據表的體積沒有發生任何變化！！但是，我們檢查數據表的時候，卻發現了對應列的默認值已經添加。
這個事情是比較奇怪的，有一個道理必然是可以說通：就是這個默認值在執行過程中，是絕對沒有真正添加到數據塊中的，因為只有這樣才不會影響數據段的體積。
3、11g默認值處理的優化
那么，11g這個過程中是如何處理的呢？而且為什么只有添加Not null的時候才會有這個特點。我們從select數據行的trace進行入手。
我們選擇10046跟蹤一下select的全過程，看看顯示出來的默認值從哪里來。
SQL> select value from v$diag_info where name=’Default Trace File’;
VALUE
———————————————————————–
/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_6177.trc
SQL> alter session set events ‘10046 trace name context forever, level 12’;
會話已更改。
SQL> select * from t where rownum
OBJECT_ID
———-
VC
—————————————————————————–
SQL> alter session set events ‘10046 trace name context off’;
會話已更改。
對生成的trace文件進行處理，獲取到tkprof結果。
D:des>tkprof wilson_ora_6177.trc
output = res.txt
TKPROF: Release 10.2.0.1.0 – Production on星期五8月24 22:07:10 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
在分析的結果中，我們發現很多的recursive語句，也就是Oracle為了執行這個SQL，連帶運行了很多的語句，其中我們發現了一個“可疑”對象。
***********************************************************************
select binaryDefVal, length(binaryDefVal)
from
ecol$????????? where tabobj# = :1 and colnum = :2
call??? count????? cpu?? elapsed????? disk???? query?? current?????? rows
——- —— ——– ———- ———- ———- ———- ———-
Parse?????? 1???? 0.00????? 0.00???????? 0???????? 0???????? 0????????? 0
Execute???? 1???? 0.00????? 0.00???????? 0???????? 0???????? 0????????? 0
Fetch?????? 1???? 0.00????? 0.00???????? 2???????? 2???????? 0????????? 1
——- —— ——– ———- ———- ———- ———- ———-
total?????? 3???? 0.00????? 0.00???????? 2???????? 2???????? 0????????? 1
ecol$是sys用戶下的一個新添加的字典基表，其中內容如下：
SQL> desc ecol$;
Name??????? Type? Nullable Default Comments
———— —— ——– ——- ——–
TABOBJ#???? NUMBER Y???????????????????????
COLNUM????? NUMBER Y???????????????????????
BINARYDEFVAL BLOB? Y???????????????????????
SQL> select * from ecol$;
? TABOBJ#??? COLNUM BINARYDEFVAL
———- ———- ————
??? 76046???????? 2
Executed in 0.031 seconds
SQL> col owner for a10;
SQL> col object_name for a10;
SQL> select owner, object_name, object_id from dba_objects where object_id in (76046);
OWNER???? OBJECT_NAM OBJECT_ID
———- ———- ———-
SCOTT???? T????????????? 76046
Executed in 0 seconds
從ecol$數據表中，我們發現了對數據表T對象第二列（column=2）的一個對象引用，引用的值binarydefval是一個blob類型。從直觀上，我們已經可以猜出這個就是記錄了數據表vc列的默認值。
此處，我們說一個問題，在Oracle中，默認值都是通過大對象類型進行保存。在數據字典col$中，默認值是通過long類進行保存。而進入11g的ecol$表，這個值是使用blob類型進行保存。
另一個需要注意的，就是這個數據表中只有一個數據行，也就是只有我們創建數據表T的默認值。這說明什么呢？
此時，我們已經可以猜出Oracle的良苦用心。首先，Oracle注意到了在生產online的時候，添加帶默認值列數據的困難。但是，從現有的體系結構和存儲結構下，將默認值逐行插入、從而引起行遷移的情況是不能避免的。所以，Oracle采用了一種“障眼法”。
如果我們在創建數據表的時候就指定了數據列的默認值、或者沒有要求將所有數據空值一次性全都變成默認值的時候，Oracle還是按照原有的存儲策略進行管理。如果出現了要求添加數據列，并且一次性將所有默認值列都加入的情況，Oracle索性就不進行插入數據和挪行的操作，而是將這個默認值保存在ecol$中。
接下來，如果要進行檢索數據，首先Oracle會利用recursive call的方法，保存提取出默認值。在檢索數據的過程中，如果遇到默認值列為空的情況（沒有插值），就將取出的默認值輸出到界面上進行顯示。其實，數據行對應的默認值列是沒有這個值的。
這就解釋了為什么只有在添加not null默認值列的時候，才會有這個優化。因為Oracle需要確認這個列不會有空值，才會將出現的空值全都進行“障眼法”匹配。
4、結論
借助了11g這個特性，我們說在online生產環境下，臨時加入默認值列就不是一件恐怖的工作了。不過，處于謹慎的考慮，還是希望有條件的時候，將該數據表進行重構。這種特性屬于應急環境下考慮使用。
