ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 826|回复: 1

[笔记] 在高并发、高负载的情况下,如何给表添加字段并设置DEFAULT值?

[复制链接]
认证徽章
论坛徽章:
17
青年奥林匹克运动会-高尔夫
日期:2014-09-10 14:54:51目光如炬
日期:2018-01-08 17:00:01目光如炬
日期:2017-11-19 22:00:00目光如炬
日期:2017-11-12 22:00:01火眼金睛
日期:2017-09-30 22:00:01目光如炬
日期:2017-09-03 22:00:01火眼金睛
日期:2017-09-01 17:00:07火眼金睛
日期:2017-02-28 22:00:00火眼金睛
日期:2017-02-06 01:02:33人气徽章
日期:2016-11-09 15:56:29
发表于 2018-2-10 18:28 | 显示全部楼层 |阅读模式
在高并发、高负载的情况下,如何给表添加字段并设置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)上有同步更新
● 本文博客园地址:http://www.cnblogs.com/lhrbest
● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/
.............................................................................................................................................
● 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笔试面试宝典》读者群       小麦苗的微店
.............................................................................................................................................






非常感谢各位朋友支持小麦苗。
认证徽章
论坛徽章:
37
秀才
日期:2016-01-12 11:23:27秀才
日期:2016-01-13 12:14:26白羊座
日期:2016-02-01 14:49:24秀才
日期:2016-01-21 13:37:04秀才
日期:2016-01-25 15:02:04狮子座
日期:2016-03-22 09:45:47摩羯座
日期:2016-05-16 10:09:40弗兰奇
日期:2017-01-11 14:37:00奥运会纪念徽章:网球
日期:2016-09-26 15:05:12ITPUB15周年纪念
日期:2016-10-13 13:15:34
发表于 2018-2-11 10:26 | 显示全部楼层
好文

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表