在高并發(fā)、高負(fù)載的情況下，如何給表添加字段并設(shè)置DEFAULT值？

成都創(chuàng)新互聯(lián)公司長期為成百上千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺(tái)，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為古交企業(yè)提供專業(yè)的成都網(wǎng)站建設(shè)、網(wǎng)站制作，古交網(wǎng)站改版等技術(shù)服務(wù)。擁有10多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。

在Oracle 12c之前，當(dāng)Oracle表數(shù)據(jù)量上億時(shí)，對表執(zhí)行“ALTER TABLE XXX ADD COLUMN_XX VARCHAR2(2) DEFAULT 'XXX';”操作時(shí)，效率及安全性是必須要考慮的因素。若直接執(zhí)行，則會(huì)在該過程中給表加上6級表鎖，也就是連查詢都需要等待，這在生產(chǎn)庫上是相當(dāng)危險(xiǎn)的操作。因?yàn)镺racle在執(zhí)行上述操作過程中，不僅要更新數(shù)據(jù)字典，還會(huì)刷新全部的記錄，并且會(huì)使得Undo表空間暴漲，所以，正確的做法是將更新數(shù)據(jù)字典和更新字段值分開。

例如，表LKILL.T_KILL約有4500W的數(shù)據(jù)，直接添加一個(gè)字段C_LHR需要花費(fèi)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

若修改為如下的方式，則可以顯著提高這個(gè)操作的性能，但表中原有的記錄對于新添加的列為空，新增記錄默認(rèn)值會(huì)設(shè)置為LHR，那么原有記錄的默認(rèn)值就需要在系統(tǒng)空閑的時(shí)候進(jìn)行批量更新、批量提交或采用系統(tǒng)包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);

Table altered.

Elapsed: 00:00:00.35

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

Table altered.

Elapsed: 00:00:00.06

需要注意的是，從Oracle 11g開始，當(dāng)添加一個(gè)帶有默認(rèn)值的非空列時(shí)（注意2個(gè)條件，NOT NULL和默認(rèn)值），Oracle不會(huì)使用這個(gè)默認(rèn)值來物理更新現(xiàn)有存在的行，Oracle只會(huì)存儲(chǔ)這個(gè)新列元數(shù)據(jù)（NOT NULL約束和DEFAULT默認(rèn)值），從而使得對該表的添加帶有默認(rèn)值的非空列操作可以在瞬間完成。當(dāng)然，從表中檢索該列時(shí)，會(huì)有部分的NVL函數(shù)代價(jià)。具體的細(xì)微差別可以通過10046事件來分析，這里不再詳細(xì)解析。

從Oracle 12c開始，支持具有默認(rèn)值的空列的添加列的DDL語句優(yōu)化，即如下2條SQL語句的效率是一樣的，也不存在鎖表的現(xiàn)象了：

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 ;

Table altered.

Elapsed: 00:00:25.29

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

BYTES

----------

8388608

Elapsed: 00:00:00.01

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

Table altered.

Elapsed: 00:00:00.08

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

BYTES

----------

8388608

Elapsed: 00:00:00.01

可以看出，在Oracle 11g中，加了NOT NULL約束的SQL語句，可以在瞬間完成添加列的操作，而只設(shè)置了默認(rèn)值的SQL語句使用了25秒的時(shí)間。另外，加了NOT NUL約束的SQL語句執(zhí)行完畢后，表的大小沒有變化，這也說明了Oracle并沒有做物理更新。

下面查看其執(zhí)行計(jì)劃，注意在這里不要使用“SET AUTOT ON”的方式，否則不能看到其真實(shí)的執(zhí)行計(jì)劃：

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

可以看到，在謂詞部分出現(xiàn)了NVL函數(shù)。所以，Oracle認(rèn)為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;

N1 N2 N3 CL C_DDL C_DDL2

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

1 0 8 XYGGZXRRYR 666 8881

Elapsed: 00:00:00.01

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

PLAN_TABLE_OUTPUT

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

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 |

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

Predicate Information (identified by operation id):

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

2 - access("C_DDL2"=8881)

19 rows selected.

Elapsed: 00:00:00.11

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

下面看看在Oracle 12c中的執(zhí)行情況：

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

Elapsed: 00:00:00.00

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;

Table created.

Elapsed: 00:00:09.41

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

BYTES

----------

4194304

Elapsed: 00:00:00.33

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

Table altered.

Elapsed: 00:00:00.65

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

BYTES

----------

4194304

Elapsed: 00:00:00.14

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

Table altered.

Elapsed: 00:00:00.15

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

BYTES

----------

4194304

Elapsed: 00:00:00.09

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

COUNT(*)

----------

100000

Elapsed: 00:00:00.02

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

PLAN_TABLE_OUTPUT

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

SQL_ID bq50v8z914juk, child number 1

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

SELECT COUNT(*) FROM t1 WHERE c_ddl2=888

Plan hash value: 3724264953

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

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

Note

-----

- statistics feedback used for this statement

23 rows selected.

Elapsed: 00:00:00.05

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

COUNT(*)

----------

100000

Elapsed: 00:00:00.04

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

PLAN_TABLE_OUTPUT

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

SQL_ID dph3gfp6f0jja, child number 1

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

SELECT COUNT(*) FROM t1 WHERE c_ddl=666

Plan hash value: 3724264953

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

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中，添加具有默認(rèn)值的DDL優(yōu)化已擴(kuò)展到包括默認(rèn)值的空列。Oracle使用了一個(gè)未公開的函數(shù)SYS_OP_VECBIT和新的隱藏列SYS_NC00005$，因?yàn)樵摿袥]有被物理更新。

& 說明：

有關(guān)批量更新和DBMS_PARALLEL_EXECUTE的使用更詳細(xì)的內(nèi)容可以參考我的BLOG：① http://blog.itpub.net/26736162/viewspace-2140626/ ②http://blog.itpub.net/26736162/viewspace-1684396

About Me
.............................................................................................................................................
● 本文作者：小麥苗，部分內(nèi)容整理自網(wǎng)絡(luò)，若有侵權(quán)請聯(lián)系小麥苗刪除
● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客園（http://www.cnblogs.com/lhrbest）和個(gè)人微信公眾號(hào)（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版、個(gè)人簡介及小麥苗云盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 數(shù)據(jù)庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● DBA寶典今日頭條號(hào)地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群號(hào)：230161599（滿）、618766405
● 微信群：可加我微信，我拉大家進(jìn)群，非誠勿擾
● 聯(lián)系我請加QQ好友（646634621），注明添加緣由
● 于 2018-02-01 06:00 ~ 2018-02-31 24:00 在魔都完成
● 文章內(nèi)容來源于小麥苗的學(xué)習(xí)筆記，部分整理自網(wǎng)絡(luò)，若有侵權(quán)或不當(dāng)之處還請諒解
● 版權(quán)所有，歡迎分享本文，轉(zhuǎn)載請保留出處
.............................................................................................................................................
● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麥苗出版的數(shù)據(jù)庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/
● 好消息：小麥苗OCP、OCM開班啦，詳情請點(diǎn)擊：http://blog.itpub.net/26736162/viewspace-2148098/
.............................................................................................................................................
使用微信客戶端掃描下面的二維碼來關(guān)注小麥苗的微信公眾號(hào)（xiaomaimiaolhr）及QQ群（DBA寶典），學(xué)習(xí)最實(shí)用的數(shù)據(jù)庫技術(shù)。
小麥苗的微信公眾號(hào) 小麥苗的DBA寶典QQ群2 《DBA筆試面試寶典》讀者群小麥苗的微店
.............................................................................................................................................