在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
---------------------------------------------------------------------------
| 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;
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
------------------------------------------------------------------------------------------
| 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 |
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
本文轉自lhrbest 51CTO部落格，原文連結：http://blog.51cto.com/lhrbest/2070969，如需轉載請自行聯系原作者
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)