一. Enhanced ADD COLUMN 說明
在Oracle 11gR1中,Oracle 對add column 進行了增強。 官網的說明地址:
http://docs.oracle.com/cd/B28359_01/server.111/b28279/chapter1.htm#NEWFTCH1
1.1 Enhanced ADD COLUMN Functionality
Default valuesof columns are maintained in the data dictionary for columns specifiedas NOT NULL.
--當列指定為not null,那麼該列對應的預設值在資料字典中進行維護。
Adding newcolumns with DEFAULT values and NOT NULL constraint nolonger requires the default value to be stored in all existing records. Thisnot only enables a schema modification in sub-seconds and independent of theexisting data volume, it also consumes no space.
--添加一列,該列不空,且有預設值,在11g中不在需要儲存這個預設在所有的記錄中,該預設值單獨儲存在資料字典裡,在使用時,在從資料字典中調用,從而減少了DDL操作的時間,也減少了空間的使用。
1.2 Adding Table Columns
To add a columnto an existing table, use the ALTERTABLE...ADD statement.
The followingstatement alters the hr.admin_emp table to add a new columnnamed bonus:
ALTER TABLE hr.admin_emp
ADD (bonus NUMBER (7,2));
If a new columnis added to a table, the column is initially NULL unless you specifythe DEFAULT clause. When you specify a default value, the databaseimmediately updates each row with the default value.
--如果對錶添加一個新列,那麼在不指定default 值的情況下,該列初始化為NULL。 當我們指定預設值後,資料會立即更新該表中的所有記錄。
Note that thiscan take some time, and that during the update, there is an exclusive DML lockon the table. For some types of tables (for example, tables without LOBcolumns), if you specify both a NOT NULL constraint and adefault value, the database can optimize the column add operation and greatlyreduce the amount of time that the table is locked for DML.
--注意,這個更新操作可能需要很多時間,並且在表上還會添加一個排它鎖。
You can add acolumn with a NOT NULL constraint only if the table does notcontain any rows, or you specify a default value.
--我們僅可以在表中沒有記錄或者指定預設值的情況下才可以使用NOT NULL 限制。
1.3 說明
通過上面的說明,對add column 有了一定的瞭解。在Oracle 11g 以前,如果我們要添加一列,且該列不為空白,那麼需要指定預設值,如果表非常大,那麼在執行時,在添加列之後,更新表中所有的記錄,添加新的預設值。這樣會花費很長的時間,同時也會產生大量的redo log。 所以在11g 以前添加帶預設值的列需要在DB 相對空閑時進行。
在Oracle 11g對add column功能進行了增強,在上述情況下,11g中不會更新表中所有的記錄,而是將預設值儲存到資料字典裡。 當使用者查詢該列的記錄時,在從資料字典(sys.col$.default$)中擷取預設值。 這樣做可以減少系統的開銷。
注意:
在第一添加列是,會同時更新ecol$和 col$ 字典,但是如果以後修改這個預設值,就僅修改col$中的值,我們以後的查詢也是從col$中擷取,而ecol$中,則永遠儲存的是我們第一次賦予的default值。
MOS上與該功能相關的一個BUG 說明:
Wrong Result For Added Column After TableCreation in 11g [ID 1106553.1]
二.樣本
2.1 add column 操作樣本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0- Production
NLSRTL Version 11.2.0.1.0 – Production
SQL> create table t1(id number,namevarchar2(20));
Table created.
SQL> insert into t1 values(1,'dave');
1 row created.
SQL> insert into t1 values(2,'anqing');
1 row created.
SQL> insert into t1values(3,'huaining');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID NAME
---------- -----------------
1 dave
2 anqing
3 huaining
在執行add column 之前,我們啟用10046 事件跟蹤一下這個過程:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8;
Statement processed.
--執行操作
SQL> alter table t1 add tel varchar2(20)default '13888888888' not null;
Table altered.
SQL> select * from t1;
ID NAME TEL
---------- ----------------------------------------
1 dave 13888888888
2 anqing 13888888888
3 huaining 13888888888
--關閉10046event,並查看trace:
SQL> oradebug event 10046 trace namecontext off;
Statement processed.
SQL> oradebug tracefile_name
d:\app\administrator\diag\rdbms\newccs\newccs\trace\newccs_ora_308.trc
關於10046 事件的更多說明,參考我的Blog:
Oracle SQLTrace 和 10046事件
http://blog.csdn.net/tianlesoftware/article/details/5857023
2.2 分析trace 檔案
查看newccs_ora_308.trc檔案裡的內容,搜尋一下:
=====================
PARSING IN CURSOR #11 len=445 dep=1 uid=0oct=6 lid=0 tim=5734874878 hv=1706555580 ad='b61eda64' sqlid='dbcjnkpkvgy5w'
update col$ setname=:3,segcol#=:4,type#=:5,length=:6,precision#=decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),scale=decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),null$=:9,fixedstorage=:10,segcollength=:11,col#=:12,property=:13,charsetid=:14,charsetform=:15,spare1=:16,spare2=:17,spare3=:18,deflength=decode(:19,0,null,:19),default$=:20where obj#=:1 and intcol#=:2
END OF STMT
PARSE#11:c=0,e=857,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=5734874874
EXEC#11:c=15600,e=23270,p=0,cr=2,cu=7,mis=1,r=1,dep=1,og=4,plh=511615611,tim=5734898508
STAT #11 id=1 cnt=0 pid=0pos=1 obj=0 op='UPDATE COL$ (cr=2 pr=0pw=0 time=0 us)'
STAT #11 id=2 cnt=1 pid=1 pos=1 obj=50op='INDEX UNIQUE SCAN I_COL3 (cr=2 pr=0 pw=0 time=0 us cost=1 size=59 card=1)'
CLOSE#11:c=0,e=4,dep=1,type=3,tim=5734898829
=====================
PARSING IN CURSOR #4 len=37 dep=1 uid=0oct=2 lid=0 tim=5734910715 hv=4050124187 ad='b61ed628' sqlid='cqrnq6vsqgzcv'
insert into ecol$ values(:1, :2, :3)
END OF STMT
PARSE#4:c=0,e=578,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=5734910712
EXEC#4:c=0,e=973,p=0,cr=2,cu=3,mis=1,r=1,dep=1,og=4,plh=0,tim=5734912051
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=0op='LOAD TABLE CONVENTIONAL (cr=2 pr=0pw=0 time=0 us)'
CLOSE#4:c=0,e=4,dep=1,type=3,tim=5734912234
=====================
PARSING IN CURSOR #10 len=97 dep=1 uid=0oct=3 lid=0 tim=5734913014 hv=2759248297 ad='b61ed1ac' sqlid='aa35g82k7dkd9'
select binaryDefVal,length(binaryDefVal) from ecol$ where tabobj# = :1 and colnum = :2
END OF STMT
PARSE#10:c=0,e=596,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=5734913010
EXEC#10:c=0,e=1149,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=3081038021,tim=5734914364
FETCH#10:c=0,e=65,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3081038021,tim=5734914511
STAT #10 id=1 cnt=1 pid=0 pos=1 obj=123op='TABLE ACCESS BY INDEX ROWID ECOL$ (cr=2 pr=0 pw=0 time=0 us cost=1size=2028 card=1)'
STAT #10 id=2 cnt=1 pid=1 pos=1 obj=126op='INDEX RANGE SCAN ECOL_IX1 (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
CLOSE#10:c=0,e=4,dep=1,type=3,tim=5734914692
格式有點亂,使用tkprof 格式化一下:
C:\Users\Administrator.DavidDai>tkprof d:\app\administrator\diag\rdbms\newccs\newccs\trace\newccs_ora_308.trc d:\dave.txt
TKPROF: Release 11.2.0.1.0 - Development onWed Feb 1 22:09:55 2012
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
尋找的相關結果:
SQL ID: 60uw2vh6q9vn2
Plan Hash: 0
insert intocol$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,
null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,
charsetid,charsetform,spare1,spare2,spare3)
values
(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,
null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,
180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12)
,:13,:14,:15,:16,:17,:18,:19,:20)
SQL ID: dbcjnkpkvgy5w
Plan Hash: 511615611
update col$ setname=:3,segcol#=:4,type#=:5,length=:6,precision#=decode(:5,
182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),scale=decode(:5,2,
decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,
183,:8,231,:8,null),null$=:9,fixedstorage=:10,segcollength=:11,col#=:12,
property=:13,charsetid=:14,charsetform=:15,spare1=:16,spare2=:17,spare3=:18,
deflength=decode(:19,0,null,:19),default$=:20
where obj#=:1 and intcol#=:2
SQL ID: cqrnq6vsqgzcv
Plan Hash: 0
insert into ecol$ values (:1, :2, :3)
SQL ID: aa35g82k7dkd9
Plan Hash: 3081038021
select binaryDefVal, length(binaryDefVal)
from ecol$ where tabobj# = :1 and colnum =:2
--注意這裡ecol$和 col$ 都被更新了。
2.3 測試ecol$ 和 col$ 區別:
查看sys.ecol$
SQL> select * from sys.ecol$;
TABOBJ# COLNUM BINARYDEFVAL
---------- ---------------------------------------
83210 3 3133383838383838383838
SQL> desc ecol$;
Name Null? Type
------------------------------------------------- -------
TABOBJ# NUMBER
COLNUM NUMBER
BINARYDEFVAL BLOB
注意這裡的最後值是BLOB的。
我可以使用如下SQL 查看對應的值:
SQL> select utl_raw.cast_to_varchar2(dbms_lob.substr(BINARYDEFVAL)) AS TEL from sys.ecol$;
TEL
--------------------------------------------------------------------------------
13888888888
這裡返回的是我們之前的設定,關於LOB的更多內容參考我的Blog:
ORACLE LOB 大對象處理
http://blog.csdn.net/tianlesoftware/article/details/5070981
通過sys.col$就看的比較簡單了:
SQL> select obj#,name,default$ fromsys.col$ where obj#=83210;
OBJ# NAME DEFAULT$
---------- ----------------------------------------------------
83210 ID
83210 NAME
83210 TEL '13888888888'
現在我們來修改這個欄位的預設值:
SQL> alter table t1 modify tel default '13899999999';
Table altered.
SQL> select * from t1;
ID NAME TEL
---------- ----------------------------------------
1 dave 13888888888
2 anqing 13888888888
3 huaining 13888888888
SQL> insert into t1(id,name)values(4,'hefei');
1 row created.
SQL> select * from t1;
ID NAME TEL
---------- ----------------------------------------
1 dave 13888888888
2 anqing 13888888888
3 huaining 13888888888
4 hefei 13899999999
SQL> commit;
Commit complete.
再次查看ecol$ 和 col$ 中對應的記錄:
SQL> select obj#,name,default$ fromsys.col$ where obj#=83210;
OBJ# NAME DEFAULT$
---------- ----------------------------------------------------
83210 ID
83210 NAME
83210 TEL '13899999999'
--col$ 中變成了13899999999
SQL> selectutl_raw.cast_to_varchar2(dbms_lob.substr(BINARYDEFVAL)) AS TEL from sys.ecol$;
TEL
--------------------------------------------------------------------------------
13888888888
--注意這裡,ecol$中的值並沒有改變。
這就驗證了我們之前的說明,第一賦予的預設值,將永久的儲存在ecol$中,如果我們修改這個預設值,那麼,那麼僅修改col$中的值,我們查詢時也是從這個資料字典裡取資料。
在使用ADD COLUMN 新特性的時,如果該列不空,且有預設值,那麼插入的時間就會較快。
因為在11g中不在需要儲存這個預設在所有的記錄中,該預設值單獨儲存在資料字典裡,在使用時,在從資料字典中調用,從而減少了DDL操作的時間,也減少了空間的使用。
測試資料如下:
SQL> create table dave as select * fromdba_objects;
Table created.
SQL> select count(1) from dave;
COUNT(1)
----------
75312
SQL> insert into dave select * fromdave;
75312 rows created.
SQL> insert into dave select * from dave;
150624 rows created.
SQL> commit;
Commit complete.
SQL> set timing on
SQL> alter table dave add dave varchar2(30) default 'n' not null;
Table altered.
Elapsed: 00:00:00.17
NOT NULL +DEFAULT + 11g ADD COLUMN DEFAULT VALUE NEW FEATURE --> 特性被使用,較快
SQL> alter table dave add dave1 varchar2(30) default 'n';
Table altered.
Elapsed: 00:00:07.75
NULLABLE+ DEFAULT + 11g ADD COLUMN DEFAULT VALUE NEW FEATURE ---> 特性未被使用,較慢
-------------------------------------------------------------------------------------------------------
著作權,文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任!
Email: tianlesoftware@gmail.com
Skype: tianlesoftware
Blog: http://www.tianlesoftware.com
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook:http://www.facebook.com/tianlesoftware
-------加群需要在備忘說明Oracle資料表空間和資料檔案的關係,否則拒絕申請----
DBA1 群:62697716(滿); DBA2 群:62697977(滿) DBA3 群:62697850(滿)
DBA 超級群:63306533(滿); DBA4 群:83829929 DBA5群: 142216823
DBA6 群:158654907 DBA7 群:172855474 DBA總群:104207940