ORACLE 11G新特性之列新增操作

在介紹ORACLE 11G新特性之前，我們先來看一個ORACLE 10G的案例：

案例描述： 在正常業務期間，對一張大資料量的表新增了一個欄位，並且採用default欄位預設欄位非空，從而導致表被鎖定，undo資源被大量消耗，由於表涉及到業務資料，長時間鎖定導致業務使用者無法修改表，並且由於資料庫資源消耗嚴重導致資料庫已經不能正常操作，接下來客戶主動掐掉了回話，導致資料庫回滾，資料庫表現為響應時間慢。回滾結束後資料庫資源釋放，系統正常。重新新增欄位，預設欄位為空，新增成功。

案例分析： 針對以上案例，分析如下幾點： 首先，作為在正常的業務期間對錶做大變更，特別是表結構的調整，這是不允許的，特別是一張大表。 其次新增欄位時要考慮謹慎，不要新增相關的default引數，這也是引起本次事故的原因，導致該表被直接鎖定，業務無法正常訪問。

案例解決方法： 對於以上案例，如果避免以上問題，也就不會發生該事故。 最好辦法應該是先新增列，在業務空閒的時候執行對列的修改操作： SQL> alter table table_name add col_name data_type; SQL> atler table table_name modify col_name default default_value;

本案例中的解決方案是ORACLE 11G之前的做法，在ORACLE  11G以後，oracle對於《新增修改列》有了新的方法。

 

 

在ORACLE 11g中發出如下語句：

alter table table_name add col_name data_type default 'XX' not null；            .

ORACLE 11g直接修改資料字典將已有行的default值更新，無需修改實際的表記錄。

ORACLE 11g會將預設值寫到資料字典(sys.col$.default$)中，這樣，當我們新增了一個新列時，對於以前原有的資料，oracle採用資料字典中的資訊來表達，而對於新新增進來的列，則採用預設值寫入，這樣我們就可以將一個新列定義為非空並具有預設值，同時不會導致任何redo和undo開銷，並且相應的空間也能得到節省，本次測試旨在對該新特性有一個基本的瞭解。

會話一：	會話二：
SQL> create table test( A  number); Table created. SQL> insert into test values(1); 1 row created. SQL> insert into test values(2); 1 row created.	SQL> conn frank/frank Connected. SQL> select * from test; no rows selected SQL> desc test;  Name                                      Null?      Type  ——— – ————————-  A         NUMBER SQL> alter table test add(b number default 10); ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> alter table test add(b number default 10 not null); ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired   無論是新增not null或者不新增，報錯，由於會話1的事務還沒有結束，表處於鎖定狀態，提交會話1
SQL> commit; Commit complete.
	SQL> alter table test add(b number default 10 not null); Table altered.
此時我們可以在會話1查詢： SQL> select * from test;          A          B ————————————         1         10          2         10

 

分析上述的例子，“會話一”預設欄位被新增成功，我們在查詢時，oracle內部其實對於已經存在的資料不再做修改，它將從資料字典(ecol$ 儲存十六進位制)獲取預設值並將其返回給使用者，我們可以透過ecol$轉化十進位制對應基表（col$）看一下儲存的資料：

SQL> select object_name,object_id from dba_objects where object_name='TEST';

OBJECT_NAM  OBJECT_ID

—————— ——————

TEST            18458

SQL> select OBJ#,COL#,NAME,DEFAULT$ from col$ where OBJ#=18458;

      OBJ#       COL# NAME       DEFAULT$

————————————————————————

     18458          1 A

     18458          2 B          10

透過10046事件跟蹤以上SQL我們發現資料其實真正被插入的是ecol$基表：

我們可以透過10046事件看到，預設的default=10被寫入了ecol$基表(ecol$基表是col$基表在11g中的體現，在11G中資料以16進位制的形式插入在ecol$基表中，我們可以在col中查詢到對應的10進位制值)中，當我們做查詢時。oracle其實是從這一部分取出值來應用。

 

在ORACLE 11G中，以下操作均可同時進行DML操作，而不用擔心因為鎖表而導致操作無法進行:

1.       新增一個非空的列（NOT NULL），並且預設default值，（11G中新特性的做法）

2.       新增一個空值，並且沒有default引數（10g中新增列做法）

3.       新增一個虛擬列時候

在新增列時，使用not null和不使用not null是有區別的，我們分別對如下2個SQL進行10046事件跟蹤分析：

ü  alter table test add(b number default 10);

ü  alter table test add(b number default 10 not null);

SQL語句：alter table test add(b number default 10);        

oracle是直接將資料插入到原表中，也就是沒有應用新特性:

PARSING IN CURSOR #47128791364856 len=24 dep=1 uid=0 oct=6 lid=0 tim=1334559603376483 hv=1128945042 ad='760c31d8' sqlid='87stdfj1nnpck'

update "TEST" set "B"=10（可以看到是直接插入update語句）

SQL語句：alter table test add(b number default 10 not null);       

應用了ORACLE 11G的新特性:

PARSING IN CURSOR #47316071119904 len=37 dep=1 uid=0 oct=2 lid=0 tim=1334559827310374 hv=4050124187 ad='70158410' sqlid='cqrnq6vsqgzcv'

insert into ecol$ values (:1, :2, :3)（插入到eco$基表中）

 

檢視是基於表格而存在的，當表格新增列後，相應的檢視是否也自動新增列呢？

Oracle 11g針對該新特性明確解釋，對於針對該表的檢視，如果在新增列後，那麼相應的檢視並不會自動的新增新列。檢視查詢結果仍是原始資料

測試如下:

SQL> select * from test2;

         A          B

—————————

         1         10

         2         10

我們建立檢視：

SQL> create or replace view test2_vw as  select * from test2;

View created.

那麼我們查詢檢視：

SQL> select * from test2_vw;

         A          B

 —————————

         1         10

         2         10

 

而當我們對原表建立新列：

SQL> alter table test2 add(d varchar2(10) default 'abcde' not null);

此時我們再查詢原表：

SQL> select * from test2;

         A          B          D          

      ---------  ----------- --      -----------     

         1         10        abcde        

         2         10      abcde          

而我們查詢檢視：

SQL> select * from test2_vw;

         A          B

 ————————————

         1         10

         2         10

返回依舊是原表結果。

結論：ORACLE 11g 後可以直接修改資料字典將已有行的default值更新而無需修改實際的表記錄，他已經可以做到實時應用而不會因為表很大而造成效能問題。

 ------------------------------------------------------------------------------------

原部落格地址：http://blog.itpub.net/23732248/
原作者：應以峰 (frank-ying)
-------------------------------------------------------------------------------------