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

lhrbest發表於2018-02-10

在高併發、高負載的情況下，如何給表新增欄位並設定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寶典今日頭條號地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

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

● QQ群號：230161599（滿）、618766405

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

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

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

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

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

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

● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

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

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

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

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

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

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

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

高併發高負載情況下常見的3種效能問題
2014-12-08
負載
高併發情況下如何線上加核心表的主鍵？
2014-06-04
高可用+高併發+負載均衡架構設計
2017-09-09
負載架構
mysql-欄位設定Default值問題
2017-11-06
MySql
PHP高併發情況下防止商品庫存超賣
2022-04-04
PHP
SqlServer 高併發的情況下，如何利用鎖保證資料的穩定性
2023-03-17
SQLServer
oracle大表新增欄位default經驗分享
2015-11-12
Oracle
高寫入負載情況cassandra當機故障解決
2011-09-27
負載
資料庫系列：高併發下的資料欄位變更
2022-05-31
資料庫
說說大型高併發高負載網站的系統架構（轉載）
2009-07-19
負載網站架構
在Load average 高的情況下如何鑑別系統瓶頸。
2014-10-16
【高併發】高併發環境下如何防止Tomcat記憶體溢位？看完我懂了！！
2020-04-19
Tomcat記憶體溢位
說說大型高併發高負載網站的系統架構（更新）
2010-08-13
負載網站架構
對錶中的欄位設定了預設值，新增記錄後卻發現該欄位為null
2017-12-11
Null
如何設計高併發介面？
2019-01-20
高併發下的php.int及PHP-fpm設定
2021-02-09
PHP
如何較方便給上百張資料庫表新增表欄位
2021-02-23
資料庫
sql設定欄位預設值
2020-04-04
SQL
【高併發】如何設計一個支撐高併發大流量的系統？這次我將設計思路分享給大家！
2020-07-26
11g中關於表新增欄位default屬性研究
2012-01-13
預設情況下RAC是按照SESSION數來負載均衡的？
2009-12-02
Session負載
[分散式][高併發]負載均衡方案和演算法
2019-02-14
分散式負載演算法
oracle 11g對大表中新增DEFAULT值的NOT NULL欄位速度有大幅度的提升
2015-09-21
OracleNull
IIS Web伺服器的高併發設定
2019-04-19
Web伺服器
【高併發】高併發環境下如何優化Tomcat效能？看完我懂了！
2020-04-17
優化Tomcat
面試基礎之：叢集高併發情況下如何保證分散式唯一全域性Id生成
2020-11-23
面試分散式
Django日期欄位預設值default=timezone.now
2024-04-24
Django
【COLUMN】設定表欄位預設值僅對未來生效
2011-09-12
css-flex:在不確定div高的情況下讓圖片文字上下左右居中
2019-01-09
CSSFlex
【高併發】面試官：講講高併發場景下如何優化加鎖方式？
2020-10-15
面試優化
在沒有許可權登陸終端的情況下如何透過exp工具獲取含clob欄位的表
2016-04-22
MySQL-修改欄位型別、設定預設值，以及新增註釋
2017-12-11
MySql型別
Oracle 時段負載情況
2017-01-10
Oracle負載
SQL新增表欄位
2015-12-31
SQL
【知識分享】企業在什麼情況下需要做負載均衡
2023-02-11
負載
如何設計一個高可用、高併發秒殺系統
2020-03-10
Redis如何防止高併發?
2024-04-10
Redis
（高階）用CSS3新增的rem單位設定字型大小
2012-11-24
CSSS3REM

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

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

相關文章