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

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

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

這一篇介紹表增加包含預設值的字段。

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

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

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

先來看看10g中添加一個包含DEFAULT值的非空字段的情況：

SQL> CONN YANGTK/[email protected]已連接配接。

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/[email protected]已連接配接。

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的代價小的幾乎可以忽略。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-69440/，如需轉載，請注明出處，否則将追究法律責任。

轉載于:http://blog.itpub.net/4227/viewspace-69440/