在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笔试面试宝典》读者群 小麦苗的微店
.............................................................................................................................................
