Oracle 11g中 ADD COLUMN 功能增強 說明

來源:互聯網
上載者:User

 

 

一.  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

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.