关于表中添加列有缺省值和是否有not null约束在数据库各个版本的变化测试

oracle 11.2.0.4测试:

SQL> conn xiaoyu/xiaoyu
Connected.
SQL> create table t_null01 as select object_id,object_name,owner from dba_objects;

Table created.

SQL> alter session set events '10046 trace name context level 12';

Session altered.

SQL> alter table t_null01 add address varchar2(32) default 'Chain';

Table altered.

SQL> alter table t_null01 add name varchar2(32) default 'test' not null;

Table altered.

SQL> alter session set events '10046 trace name context off';

Session altered.

[oracle@redhat-ora ~]$ more /home/oracle/tkprof01.txt

TKPROF: Release 11.2.0.4.0 - Development on Thu Oct 8 19:44:02 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

SQL ID: 61648vzfjwfwu Plan Hash: 0

LOCK TABLE "T_NULL01" IN EXCLUSIVE MODE  NOWAIT

SQL ID: 1f1r5s0xwm0ag Plan Hash: 0

alter table t_null01 add

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0      11379          1           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          0      11381          1           0

SQL ID: 887ywuvpskfyu Plan Hash: 1758867652

update "T_NULL01" set "ADDRESS"='Chain'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.60       0.70        113      11255     228645       86881
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.60       0.70        113      11256     228645       86881

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  T_NULL01 (cr=9727 pr=83 pw=0 time=618066 us)
    181898     181898     181898   TABLE ACCESS FULL T_NULL01 (cr=2006 pr=113 pw=0 time=67418 us cost=138 size=1212516 card=67362)

--------------------------------------------------------------------------------

SQL ID: 6zuvfjvdaq5dp Plan Hash: 0

LOCK TABLE "T_NULL01" IN ROW EXCLUSIVE MODE  NOWAIT

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83     (recursive depth: 1)
--------------------------------------------------------------------------------

SQL ID: 1f1r5s0xwm0ag Plan Hash: 0

alter table t_null01 add

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          3         26           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3         26           0

在oracle 11g中新添加的列有缺省值时,如果有not null的约束,oracle不会去全量更新表中的数据行,而是将表信息、列信息和列的缺省值记录到了oracle的数据字典sys.ecol$中,这里记录缺省值的是BINARYDEFVAL这个列,该列是blob字段类型

SQL> select column_name,column_id,data_default from dba_tab_columns  where table_name='T_NULL01' and owner='XIAOYU';

COLUMN_NAME                     COLUMN_ID DATA_DEFAULT
------------------------------ ---------- --------------------------------------------------------------------------------
OBJECT_ID                               1
OBJECT_NAME                             2
OWNER                                   3
ADDRESS                                 4 'Chain'
NAME                                    5 'test'

SQL> select object_id from dba_objects where object_name='T_NULL01' and owner='XIAOYU';

 OBJECT_ID
----------
     89526

SQL> select * from sys.ecol$ where tabobj#=89526;

   TABOBJ#     COLNUM BINARYDEFVAL
---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
     89526          5 74657374

但是如果没有指定not null约束,oracle需要对这个表进行全量的更新操作,这个会对表中数据行加上行级锁,如果表中的数据量很大,这个操作会长时间对数据行加上TX mode 6级别的排他锁,可能会对并发的应用造成严重的影响,很多情况下表中的数据列不能保证新增加有default值的列有not null的约束,在oracle 11g的版本下我们依然可以采用下列的方式来添加列,以最小程度的减少对表中数据行的排他锁定:

SQL> create table t_null02 as select object_id,object_name from dba_objects;

Table created.

SQL> alter table t_null02 ADD address varchar2(32);

Table altered.

SQL> alter table t_null02 modify address varchar2(32) default 'Chain';

Table altered.

SQL> select count(*) from t_null02 where address='Chain';

  COUNT(*)
----------
         0

SQL> insert into t_null02(object_id,object_name) values(10000,'OWP');

1 row created.

SQL> select * from t_null02 where address='Chain';

 OBJECT_ID OBJECT_NAME                                                                                                                      ADDRESS
---------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------
     10000 OWP                                                                                                                              Chain

这种添加方式第一次add和modify新增加的列并不会更新表中已经存在的数据,只会对新增加的数据设置default值,而对于之前表中的数据可以用游标取出分批进行更新,这样就不会对表中数据行长时间的锁定而造成应用级别的阻塞。

而在oracle 10g中无论新增加有default值的列是否能够添加not null的约束,oracle都需要对表中的数据进行更新,对于oracle 10g可以采取上面介绍的先添加新列、然后modify赋予default值,最后再用游标来分批更新减少在添加列时对表中的数据进行长时间的锁定而阻塞应用。

oracle 12.1.0.2.0

SQL> create table t_null12 as select object_id,object_name,owner from dba_objects;

Table created.

SQL> select spid from v$process where addr in (select paddr from v$session where sid=userenv('SID'));

SPID
------------------------
7830

SQL> alter table t_null12 add address varchar2(32) default 'Chain';

Table altered.

SQL ID: 7r9g8pkxta6q2 Plan Hash: 0

LOCK TABLE "T_NULL12" IN ROW EXCLUSIVE MODE  NOWAIT

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103     (recursive depth: 1)
--------------------------------------------------------------------------------

SQL ID: bv747sa0guj2j Plan Hash: 0

alter table t_null12 add

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          6          0           0
Execute      1      0.00       0.00          0        110         25           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0        116         25           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103

在oracle 12c后,添加列时即使没有指定not null的约束,oracle也不会对数据进行更新,实现方式和oracle 11g类似,也是由数据字典基表存储对应的表、列和列的默认值关系,只不过相对于oracle 11g而言sys.ecol$基表新增加了GUARD_ID列

SQL> desc sys.ecol$;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABOBJ#                                            NUMBER
 COLNUM                                             NUMBER
 BINARYDEFVAL                                       BLOB
 GUARD_ID                                           NUMBER

About xiaoyu

xiaoyu,享受数据库带给xiaoyu的乐趣! 13439818916@163.com 欢迎邮件联系讨论
This entry was posted in oracle. Bookmark the permalink.