在高并发、高负载的情况下,如何给表添加字段并设置DEFAULT值?-阿里云开发者社区

开发者社区> 小麦苗> 正文

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

简介:
+关注继续查看

在高并发、高负载的情况下,如何给表添加字段并设置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语句优化,即如下2SQL语句的效率是一样的,也不存在锁表的现象了:

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

   小麦苗的微信公众号      小麦苗的DBA宝典QQ群2     《DBA笔试面试宝典》读者群       小麦苗的微店

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

ico_mailme_02.png
DBA笔试面试讲解群
《DBA宝典》读者群 欢迎与我联系



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
IIS 添加mime 支持 apk,exe,.woff,IIS MIME设置 ,Android apk下载的MIME 设置 苹果ISO .ipa下载mime 设置
原文:IIS 添加mime 支持 apk,exe,.woff,IIS MIME设置 ,Android apk下载的MIME 设置 苹果ISO .ipa下载mime 设置 站点--右键属性--http头 扩展名  mime类型.
1308 0
discuz论坛发帖添加字段
1 后台--论坛-- 分类信息   字段管理:添加字段。 分类管理:添加一个分类,将该分类添加几个字段; 2论坛---》 选择一个模块--》分类信息---》开启----》启用分类勾选上即可。
614 0
MySQL添加字段和修改字段的方法
添加表字段 alter table table1 add transactor varchar(10) not Null; alter table   table1 add id int unsigned not Null auto_increment primary key 修改某个表的字段...
644 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
12078 0
phpcms 2008 sp4的这个版本如何添加一个系统字段
phpcms中可以自定义模型,模型的共同字段在pc_content表中,其它的扩展字段在pc_c_这样的前缀中的表中, 自定义模型的过程如下:自定义一个模型,会使用一组创建表的sql,这个sql存储在include/amdin/model.sql这个文件中 每个字段都有以下属性,issystem,这个属性决定着是在主表还是在附表中,还有formtype这个表示在界面生成html的类型。
1021 0
【word xml】将word转化为xml格式后,如何在xml中卫word添加分页符
1.首先在xml中找到我们需要添加分页符的位置 例如:我需要在这个第一部分上面添加一个分页符 2.找到这个【第一部分】这个位置之后,开始往上找,找到对应的位置 3.在下方添加分页符代码 1 2 ...
1186 0
解决Druid设置Oracle的Clob字段时的小坑
众所周知,Oracle有很多坑, 所以才有了去IOE。 在使用Druid做数据库连接池后,其实偶尔也会碰到小坑,这就是使用开源项目所必须去填平的。【如果使用不开源的产品,那就不是坑,而是陷阱了,你都不知道怎么去填坑】 用Druid连接池,通过JDBC往Oracle数据库的Clob字段插入数据,或者更新数据时,一个问题出现了。
2021 0
文件存储 HDFS添加挂载点前提条件及步骤
挂载点是文件系统实例在专有网络或经典网络内的一个访问目标地址,每个挂载点都对应一个域名,ECS和容器需要通过此域名访问文件存储HDFS中的数据。文件存储HDFS目前只支持专有网络类型挂载点。
444 0
+关注
小麦苗
网名:小麦苗 | 微信公众号:DB宝 | 11g和12c OCM | 《数据库笔试面试宝典》作者,博客地址:http://blog.itpub.net/26736162/abstract/1/
889
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载