Oracle11新特性——線上操作功能增強（二）

打算寫一系列的文章介紹11g的新特性和變化。

11g中線上處理功能得到了很大增強，其中包括線上修改表結構，線上建立或重建索引，建立不可見索引，表增加非空欄位，線上DDL以及物件依賴性細化等。

這一篇介紹表增加包含預設值的欄位。

Oracle11新特性——線上操作功能增強（一）：http://yangtingkun.itpub.net/post/468/400430

在11g以前，表中新增一個NOT NULL的欄位是十分痛苦的事情，尤其是表很大的情況。不光是執行速度慢，而且由於現有資料長度的變化，很容易造成表中大量的行連結情況。

在11g中，這種情況得到了徹底的改善，Oracle透過在資料字典中記錄DEFAULT值，避免了繁重的更新操作，增加非空欄位的時間和增加一個可空欄位的時間完全一樣。

先來看看10g中新增一個包含DEFAULT值的非空欄位的情況：

SQL> CONN YANGTK/YANGTK@YTK102已連線。
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已建立。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
50357

SQL> SET TIMING ON
SQL> ALTER TABLE T ADD NEW_COL CHAR(1000) DEFAULT 'LARGE COLUMN' NOT NULL;

表已更改。

已用時間: 00: 00: 17.04
SQL> SET TIMING OFF
SQL> SELECT SUM(BYTES)/1024/1024 FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'T';

SUM(BYTES)/1024/1024
--------------------
72

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

在10g中，5萬條記錄新增一個CHAR(1000)的新增NOT NULL列，需要17秒，而且更新後表的大小為72M。

下面看看11g中的表現：

SQL> CONN YANGTK/yangtk@ORA11G已連線。
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已建立。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
68376

SQL> SET TIMING ON
SQL> ALTER TABLE T ADD NEW_COL CHAR(1000) DEFAULT 'LARGE COLUMN' NOT NULL;

表已更改。

已用時間: 00: 00: 00.15
SQL> SET TIMING OFF
SQL> SELECT SUM(BYTES)/1024/1024 FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'T';

SUM(BYTES)/1024/1024
--------------------
9

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

將近7萬的資料，新增欄位僅用了0.15秒。效能完全沒有可比性。從更新後表的大小也可以看到明顯的區別。

這裡只看了執行時間和佔用空間兩個方面，從生成REDO和UNDO的大小看，前者同樣遠遠超過了後者。

Oracle採用了何種方法實現瞭如此的效能提升，說起來也很簡單。Oracle11g中，在新增一個包含DEFAULT值的NOT NULL欄位，Oracle不會去更新現有的資料，Oracle需要做的不過是將預設值以及對應的表資訊、列資訊一起儲存在一個新增資料字典表ecol$中。

這張表利用BLOB欄位儲存ALTER TABLE新增的DEFAULT值：

SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'T';

OBJECT_ID
----------
70482

SQL> SELECT * FROM SYS.ECOL$ WHERE TABOBJ# = 70482;

TABOBJ# COLNUM BINARYDEFVAL
---------- ---------- ----------------------------------------
70482 16 4C4152474520434F4C554D4E

簡單轉換一下：

SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_BLOB VARCHAR2(32767) DEFAULT '4C4152474520434F4C554D4E';
3 BEGIN
4 FOR I IN 1..LENGTH(V_BLOB)/2 LOOP
5 DBMS_OUTPUT.PUT(CHR(TO_NUMBER(SUBSTR(V_BLOB, (I - 1) * 2 + 1, 2), 'XXX')));
6 END LOOP;
7 DBMS_OUTPUT.NEW_LINE;
8 END;
9 /
LARGE COLUMN

PL/SQL procedure successfully completed.

Oracle在讀取資料時，發現COLUMN 16列為非空，但是在儲存的資料中找不到該列，於是就會從ECOL$中讀取該列的預設值。

這個功能不但提高了新增非空欄位的速度，而且節省了大量的磁碟空間。相比之下，在資料字典中查詢DEFAULT VALUE的代價小的幾乎可以忽略。