在高并發、高負載的情況下，如何給表添加字段并設定DEFAULT值？

在Oracle 12c之前，當Oracle表資料量上億時，對表執行“ALTER TABLE XXX ADD COLUMN_XX VARCHAR2(2) DEFAULT 'XXX';”操作時，效率及安全性是必須要考慮的因素。若直接執行，則會在該過程中給表加上6級表鎖，也就是連查詢都需要等待，這在生産庫上是相當危險的操作。因為Oracle在執行上述操作過程中，不僅要更新資料字典，還會重新整理全部的記錄，并且會使得Undo表空間暴漲，是以，正确的做法是将更新資料字典和更新字段值分開。

例如，表LKILL.T_KILL約有4500W的資料，直接添加一個字段C_LHR需要花費21分鐘，如下所示：

12:20:17 SYS@RACLHR2> ALTER TABLE LKILL.T_KILL ADD C_LHR VARCHAR2(100) DEFAULT 'LHR';

Table altered.

Elapsed: 00:21:58.53

若修改為如下的方式，則可以顯著提高這個操作的性能，但表中原有的記錄對于新添加的列為空，新增記錄預設值會設定為LHR，那麼原有記錄的預設值就需要在系統空閑的時候進行批量更新、批量送出或采用系統包DBMS_PARALLEL_EXECUTE來更新，這樣不至于大批量鎖表，請參考本書中分批更新的部分【 REF _Ref24783 \n \h 3.1.10.5 REF _Ref24783 \h 分批插入、分批更新、分批删除、分批送出】。如下所示：

12:42:17 SYS@RACLHR2> ALTER TABLE LKILL.T_KILL ADD A_LHR VARCHAR2(100);

Elapsed: 00:00:00.35

13:53:54 SYS@RACLHR2> ALTER TABLE LKILL.T_KILL MODIFY A_LHR VARCHAR2(100) DEFAULT 'LHR';

Elapsed: 00:00:00.06

需要注意的是，從Oracle 11g開始，當添加一個帶有預設值的非空列時（注意2個條件，NOT NULL和預設值），Oracle不會使用這個預設值來實體更新現有存在的行，Oracle隻會存儲這個新列中繼資料（NOT NULL限制和DEFAULT預設值），進而使得對該表的添加帶有預設值的非空列操作可以在瞬間完成。當然，從表中檢索該列時，會有部分的NVL函數代價。具體的細微差别可以通過10046事件來分析，這裡不再詳細解析。

從Oracle 12c開始，支援具有預設值的空列的添加列的DDL語句優化，即如下2條SQL語句的效率是一樣的，也不存在鎖表的現象了：

ALTER TABLE LKILL.T_KILL ADD A_LHR VARCHAR2(100);

ALTER TABLE LKILL.T_KILL ADD A_LHR VARCHAR2(100) NOT NULL;

示例如下所示：

LHR@OCPLHR1> select * from v$version where rownum<=1;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

LHR@OCPLHR1> set time on

16:59:00 LHR@OCPLHR1> set timing on

16:59:08 LHR@OCPLHR1> CREATE TABLE t1 AS

16:59:21 2 SELECT ROWNUM N1,

16:59:21 3 TRUNC((ROWNUM - 1) / 3) N2,

16:59:21 4 TRUNC(DBMS_RANDOM.VALUE(ROWNUM, ROWNUM * 10)) N3,

16:59:21 5 DBMS_RANDOM.STRING('U', 10) cl

16:59:21 6 FROM DUAL

16:59:21 7 CONNECT BY LEVEL <= 200000;

Table created.

Elapsed: 00:00:05.72

16:59:45 LHR@OCPLHR1> SELECT d.bytes FROM user_segments d WHERE d.segment_name='T1';

BYTES

----------

7340032

Elapsed: 00:00:00.09

17:01:00 LHR@OCPLHR1> ALTER TABLE t1 ADD c_ddl NUMBER DEFAULT 666 ;

Elapsed: 00:00:25.29

17:02:07 LHR@OCPLHR1> SELECT d.bytes FROM user_segments d WHERE d.segment_name='T1';

8388608

Elapsed: 00:00:00.01

17:02:13 LHR@OCPLHR1> ALTER TABLE t1 ADD c_ddl2 NUMBER DEFAULT 888 not null;

Elapsed: 00:00:00.08

17:02:37 LHR@OCPLHR1> SELECT d.bytes FROM user_segments d WHERE d.segment_name='T1';

可以看出，在Oracle 11g中，加了NOT NULL限制的SQL語句，可以在瞬間完成添加列的操作，而隻設定了預設值的SQL語句使用了25秒的時間。另外，加了NOT NUL限制的SQL語句執行完畢後，表的大小沒有變化，這也說明了Oracle并沒有做實體更新。

下面檢視其執行計劃，注意在這裡不要使用“SET AUTOT ON”的方式，否則不能看到其真實的執行計劃：

17:05:30 LHR@OCPLHR1> SELECT COUNT(*) FROM t1 WHERE c_ddl2=888;

COUNT(*)

200000

Elapsed: 00:00:00.02

17:05:39 LHR@OCPLHR1> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID bq50v8z914juk, child number 0

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

SELECT COUNT(*) FROM t1 WHERE c_ddl2=888

Plan hash value: 3724264953

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

| 0 | SELECT STATEMENT | | | | 282 (100)| |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | TABLE ACCESS FULL| T1 | 199K| 2530K| 282 (2)| 00:00:04 |

Predicate Information (identified by operation id):

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

2 - filter(NVL("C_DDL2",888)=888)

Note

-----

- dynamic sampling used for this statement (level=2)

23 rows selected.

17:08:55 LHR@OCPLHR1> SELECT * FROM t1 WHERE rownum<=1;

N1 N2 N3 CL C_DDL C_DDL2

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

1 0 8 XYGGZXRRYR 666 888

可以看到，在謂詞部分出現了NVL函數。是以，Oracle認為C_DDL2列是空列。

下面測試是否可以使用索引：

17:29:24 LHR@OCPLHR1> CREATE INDEX idx_c_ddl2 ON t1(c_ddl2);

Index created.

Elapsed: 00:00:00.71

17:31:08 LHR@OCPLHR1> update t1 set c_ddl2='8881' where rownum<=1;

1 row updated.

Elapsed: 00:00:00.05

17:31:13 LHR@OCPLHR1> commit;

Commit complete.

Elapsed: 00:00:00.00

17:31:16 LHR@OCPLHR1> SELECT * FROM t1 WHERE c_ddl2=8881;

1 0 8 XYGGZXRRYR 666 8881

17:31:24 LHR@OCPLHR1> select * from table(dbms_xplan.display_cursor);

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

SQL_ID 0sm5s7zkvycrq, child number 0

SELECT * FROM t1 WHERE c_ddl2=8881

Plan hash value: 1464185165

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 34 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_C_DDL2 | 1 | | 1 (0)| 00:00:01 |

2 - access("C_DDL2"=8881)

19 rows selected.

Elapsed: 00:00:00.11

令人驚喜的是，使用了索引。

下面看看在Oracle 12c中的執行情況：

LHR@lhr121> set line 120

LHR@lhr121> select * from v$version where rownum<=1;

BANNER CON_ID

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

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0

LHR@lhr121> CREATE TABLE t1 AS

2 SELECT ROWNUM N1,

3 TRUNC((ROWNUM - 1) / 3) N2,

4 TRUNC(DBMS_RANDOM.VALUE(ROWNUM, ROWNUM * 10)) N3,

DBMS_RANDOM.STRING('U', 10) cl

6 FROM DUAL

7 CONNECT BY LEVEL <= 100000;

Elapsed: 00:00:09.41

LHR@lhr121> SELECT d.bytes FROM user_segments d WHERE d.segment_name='T1';

4194304

Elapsed: 00:00:00.33

LHR@lhr121> ALTER TABLE t1 ADD c_ddl NUMBER DEFAULT 666 ;

Elapsed: 00:00:00.65

Elapsed: 00:00:00.14

LHR@lhr121> ALTER TABLE t1 ADD c_ddl2 NUMBER DEFAULT 888 not null;

Elapsed: 00:00:00.15

LHR@lhr121> SELECT COUNT(*) FROM t1 WHERE c_ddl2=888;

100000

LHR@lhr121> select * from table(dbms_xplan.display_cursor);

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

SQL_ID bq50v8z914juk, child number 1

| 0 | SELECT STATEMENT | | | | 122 (100)| |

|* 2 | TABLE ACCESS FULL| T1 | 100K| 1269K| 122 (1)| 00:00:01 |

- statistics feedback used for this statement

LHR@lhr121> SELECT COUNT(*) FROM t1 WHERE c_ddl=666;

Elapsed: 00:00:00.04

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

SQL_ID dph2gfp6f0jja, child number 1

SELECT COUNT(*) FROM t1 WHERE c_ddl=666

|* 2 | TABLE ACCESS FULL| T1 | 1000 | 13000 | 122 (1)| 00:00:01 |

2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00005$",0)),NULL,NVL("

C_DDL",666),'0',NVL("C_DDL",666),'1',"C_DDL")=666)

20 rows selected.

Elapsed: 00:00:00.12

LHR@lhr121> SELECT d.column_name, d.column_id,d.hidden_column,d.virtual_column FROM Dba_Tab_Cols d WHERE d.table_name='T1' order by column_id;

COLUMN_NAME COLUMN_ID HID VIR

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

N1 1 NO NO

N2 2 NO NO

N3 3 NO NO

CL 4 NO NO

C_DDL 5 NO NO

C_DDL2 6 NO NO

SYS_NC00005$ YES NO

7 rows selected.

Elapsed: 00:00:00.32

LHR@lhr121>

從示例可以清楚地看到，在Oracle 12c中，添加具有預設值的DDL優化已擴充到包括預設值的空列。Oracle使用了一個未公開的函數SYS_OP_VECBIT和新的隐藏列SYS_NC00005$，因為該列沒有被實體更新。

& 說明：

有關批量更新和DBMS_PARALLEL_EXECUTE的使用更詳細的内容可以參考我的BLOG：① http://blog.itpub.net/26736162/viewspace-2140626/ ②http://blog.itpub.net/26736162/viewspace-1684396

在高并發、高負載的情況下，如何給表添加字段并設定DEFAULT值？

繼續閱讀

部署.net平台的應用程式

Robot Framework配置發送郵件功能

II6 IIS7 下 asp.net的生命周期 ASP.NET Application Life Cycle Overview for IIS 5.0 and 6.0

2010年終總結--技術篇

Oracle的基本操作

PAT (Advanced Level) Practise 1131 Subway Map (30)

ZOJ 3938 Defuse the Bomb

CSU 1565 Word Cloud

ZOJ 3700 Ever Dream

為什麼要選擇UniDAC

ZOJ 1199 Point of Intersection

CSU 1567 Reverse Rot

如何成為一名.net 工程師?

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List