Oracle 11g enhancement add column

今天在ods 上给一张2亿+ 表增加一列,若该列非空,在11g 以前做法是增加一空列,然后等空闲的时候update .在11g 中不需要这样了.

11g 对于增加字段有一个性能增强的特性，在11g 之前的版本添加一个非空字段(默认值) ,会对表做个巨大的update，锁表, 产生大量的undo 和redo , 增加字段 11g 这个特性将默认值存储在元数据字典中,瞬间完成添加列的操作

在metalink 上发现的已有两个bug
Add Date Column To Table With Default Sysdate And Not Null Inserts Zeros [ID 602327.1]
Bug 8501439 - OERI[kghfrh:ds] / [kghfrempty:ds] / [17147] ORA-7445 [memcpy] with ANALYZE in a table with added column [ID 8501439.8]


根据一些资料,实验验证下 存储add column  的两个数据字典 sys.col$ 和 sys.ecol$


SQL> conn /as sysdba
Connected.
SQL> create table scott.t_addcolumn nologging as  select rownum id,text from dba_source;

Table created.

Elapsed: 00:00:17.82
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8;
Statement processed.
SQL> alter table scott.t_addcolumn add (name varchar2(10) default 'N' not null);

Table altered.

Elapsed: 00:00:01.53
SQL> select * from scott.t_addcolumn where rownum<3;


        ID TEXT                                                                                    NAME
---------- -------------------------------------------------------------------------------------- ----------
         1 package STANDARD AUTHID CURRENT_USER is    -- careful on this line; SED edit occurs!       N
         2                                                                                            N



Elapsed: 00:00:01.01
SQL> oradebug event 10046 trace name context off;
Statement processed.
SQL> oradebug tracefile_name
/opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19120.trc
SQL> !

vi /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19120.trc

update con$ set con#=:3,spare1=:4 where owner#=:1 and name=:2
END OF STMT
PARSE #3:c=30996,e=90188,p=8,cr=193,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1331203324127578
EXEC #3:c=1999,e=2173,p=0,cr=2,cu=6,mis=1,r=1,dep=1,og=4,plh=2574219287,tim=1331203324129854
STAT #3 id=1 cnt=0 pid=0 pos=1 bj=0 p='UPDATE  CON$ (cr=2 pr=0 pw=0 time=0 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 bj=51 p='INDEX UNIQUE SCAN I_CON1 (cr=2 pr=0 pw=0 time=0 us cost=1 size=25 card=1)'
CLOSE #3:c=0,e=2,dep=1,type=3,tim=1331203324130002
=====================
PARSING IN CURSOR #1 len=56 dep=1 uid=0 ct=3 lid=0 tim=1331203324130700 hv=2140575417 ad='4bfbf150' sqlid='7ub921xztd5pt'
select con#,spare1 from con$ where owner#=:1 and name=:2

=====================
PARSING IN CURSOR #8 len=453 dep=1 uid=0 ct=2 lid=0 tim=1331203325530200 hv=224718466 ad='4fc83da4' sqlid='60uw2vh6q9vn2'
insert into col$(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)

=====================
PARSING IN CURSOR #1 len=37 dep=1 uid=0 ct=2 lid=0 tim=1331203325532210 hv=4050124187 ad='3cbc2774' sqlid='cqrnq6vsqgzcv'
insert into ecol$ values (:1, :2, :3)
END OF STMT
PARSE #1:c=1000,e=403,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1331203325532209
EXEC #1:c=1000,e=722,p=0,cr=2,cu=3,mis=1,r=1,dep=1,og=4,plh=0,tim=1331203325532996
STAT #1 id=1 cnt=0 pid=0 pos=1 bj=0 p='LOAD TABLE CONVENTIONAL  (cr=2 pr=0 pw=0 time=0 us)'
CLOSE #1:c=0,e=2,dep=1,type=3,tim=1331203325533068


=====================
PARSING IN CURSOR #4 len=97 dep=1 uid=0 ct=3 lid=0 tim=1331203351112343 hv=2759248297 ad='3e632170' sqlid='aa35g82k7dkd9'
select binaryDefVal, length(binaryDefVal) from ecol$           where tabobj# = :1 and colnum = :2
END OF STMT
PARSE #4:c=1000,e=706,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1331203351112342
EXEC #4:c=1000,e=877,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=3081038021,tim=1331203351113316
FETCH #4:c=0,e=56,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3081038021,tim=1331203351113406
STAT #4 id=1 cnt=1 pid=0 pos=1 bj=123 p='TABLE ACCESS BY INDEX ROWID ECOL$ (cr=2 pr=0 pw=0 time=0 us cost=1 size=2028 card=1)'
STAT #4 id=2 cnt=1 pid=1 pos=1 bj=126 p='INDEX RANGE SCAN ECOL_IX1 (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
CLOSE #4:c=0,e=2,dep=1,type=3,tim=1331203351113491



格式化:
[oracle@mbhvm ~]$tkprof /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19120.trc /opt/oracle/diag/rdbms/orcl/orcl/trace/addcolumn.log

TKPROF: Release 11.2.0.1.0 - Development on Thu Mar 8 18:54:11 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


[oracle@mbhvm ~]$vi /opt/oracle/diag/rdbms/orcl/orcl/trace/addcolumn.log 


********************************************************************************

SQL ID: 8nhg2pdrzs3ww
Plan Hash: 0
insert into con$(owner#,name,con#,spare1)
values
(:1,:2,:3,:4)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          1          6           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          0          1          6           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=0 us)

********************************************************************************

********************************************************************************

SQL ID: bajr90ryjd2w8
Plan Hash: 2574219287
update con$ set con#=:3,spare1=:4
where
 owner#=:1 and name=:2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          2          6           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          0          2          6           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  CON$ (cr=2 pr=0 pw=0 time=0 us)
      1   INDEX UNIQUE SCAN I_CON1 (cr=2 pr=0 pw=0 time=0 us cost=1 size=25 card=1)(object id 51)

********************************************************************************

********************************************************************************

SQL ID: cqrnq6vsqgzcv
Plan Hash: 0
insert into ecol$
values
 (:1, :2, :3)


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          2          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          2          3           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=2 pr=0 pw=0 time=0 us)

********************************************************************************



可以看出更新了 sys.col$ 和 sys.ecol$ 数据字典



SQL> select * from sys.ecol$
 
  TABOBJ#     COLNUM BINA
---------- ---------- ----
    147549          3 4E
    147550          3 4E

Elapsed: 00:00:00.00

SQL> desc sys.ecol$
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 TABOBJ#                                                                    NUMBER
 COLNUM                                                                     NUMBER
 BINARYDEFVAL                                                               BLOB

BLOB 字段

SQL> select utl_raw.cast_to_varchar2(dbms_lob.substr(BINARYDEFVAL)) AS TEL from sys.ecol$;



TEL
------------------------------------------------------------------------------------------------------------------------
N
N

Elapsed: 00:00:00.01


SQL> select obj#,name,default$ from sys.col$ where obj#=147549;

      OBJ# NAME                           DEFAULT$
---------- ------------------------------ ----------
    147549 ID
    147549 TEXT
    147549 NAME                           'N'

Elapsed: 00:00:00.00


证明了这两个数据字典存储的正是赋予的默认值'N'


另外,第一赋予的默认值，将永久的保存在ecol$中，如果我们修改这个默认值，那么，那么仅修改col$中的值，我们查询时也是从这个数据字典里取数据




资料引用:
http://tonguc.wordpress.com/2008/09/28/11g-enhancement-for-alter-table-add-column-functionality/
http://www.pythian.com/news/1660/adding-columns-with-default-values-and-not-null-in-oracle-11g/
http://blog.csdn.net/tianlesoftware/article/details/7226893

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23141985/viewspace-718325/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23141985/viewspace-718325/