在高併發、高負載的情況下，如何給表新增欄位並設定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); 
		
			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開始，當新增一個帶有預設值的非空列時（注意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 ; 
		
			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語句，可以在瞬間完成新增列的操作，而只設定了預設值的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 
		
			---------------------------------------------------------------------------
		
			|
Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
		
			---------------------------------------------------------------------------
		
			|   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; 
		
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 
		
			------------------------------------------------------------------------------------------ 
		
			| Id  |
Operation                   | Name       | Rows 
| Bytes | Cost (%CPU)| Time     | 
		
			------------------------------------------------------------------------------------------ 
		
			|   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中的執行情況：

			  
			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 
		
			--------------------------------------------------------------------------- 
		
			| Id  |
Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
		
			--------------------------------------------------------------------------- 
		
			|   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 
dph2gfp6f0jja, child number 1 
		
			------------------------------------- 
		
			SELECT COUNT(*) FROM t1 WHERE c_ddl=666 
		
			Plan hash value: 3724264953 
		
			--------------------------------------------------------------------------- 
		
			| Id  |
Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
		
			--------------------------------------------------------------------------- 
		
			|   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中，新增具有預設值的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

About Me

.............................................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：

.............................................................................................................................................

● QQ群號：230161599（滿）、618766405

● 微信群：可加我微信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友（646634621），註明新增緣由

● 於 2018-02-01 06:00 ~ 2018-02-31 24:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

.............................................................................................................................................

● 小麥苗的微店：

● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/

● 好訊息：小麥苗OCP、OCM開班啦，詳情請點選：http://blog.itpub.net/26736162/viewspace-2148098/

.............................................................................................................................................

使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。

小麥苗的微信公眾號小麥苗的DBA寶典QQ群2 《DBA筆試面試寶典》讀者群小麥苗的微店

.............................................................................................................................................

在高併發、高負載的情況下，如何給表新增欄位並設定DEFAULT值？

在高併發、高負載的情況下，如何給表新增欄位並設定DEFAULT值？

相關文章