大表添加一个字段需求

测试目标：

   客户需求，一套11.2.0.4环境下，4G大表，添加一个字段。

   本次测试从，时间消耗，锁申请级别，以及字段是否添加默认值，数据真实存储进行测试；

测试流程：

  一.创建测试表

SQL> drop table a purge;

Table dropped.

SQL> create table a as select * from dba_objects;

Table created.

SQL> set timing on
多次循环插入

SQL> insert into a select * from a;
22291968 rows created.
SQL> commit;
SQL> select sum(Bytes)/1024/1024 from user_segments where segment_name='A' ;
SUM(BYTES)/1024/1024
--------------------
4990

二.添加字段，并且附加default

1)申请锁资源
Session 1

SQL> delete a where rownum=1;

1 row deleted.

Session 2

SQL> alter session set ddl_lock_timeout=600;
SQL>  alter table a add C_LHR VARCHAR2(100) DEFAULT 'LHR';
会话hang住，查询申请的锁资源

SQL> select object_id from dba_objects where owner='YZ' and object_name='A';

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

SQL> select sid,id1,type,lmode,request,ctime,block from v$lock where id1=89526;

SID ID1 TY LMODE REQUEST CTIME BLOCK
---------- ---------- -- ---------- ---------- ---------- ----------
329 89526 TM 3 0 156 1
312 89526 TM 0 6 86 0   申请TM 6!!!

2）更新时间

Session 1 回滚，让添加字段的操作自动执行

SQL> roll;
Rollback complete.

与系统性能有关！  

查询用户正在执行的event

SQL> select event,sid from gv$session where status='ACTIVE' and username='YZ';

EVENT SID
----------------------------------------------------------------- ------
log file switch (checkpoint incomplete) 312
SQL*Net message from client 329

由于测试环境日志未进行格式化，因此日志文件很小，日志组数量也少，最终导致Session被CKPT进程未完成而造成的等待，大大延误时间，并且归档日志有大量产生。

查询统计执行alter table 操作的session event统计

Elapsed: 00:40:59.26

可以发现，数据库等待CKPT进程写完毕20分钟，等待日志切换15分钟，相反看起来数量最多的物理单快读次数最多40万次，但是实际消耗的时间才3分钟。

本次不涉及优化，但是从本次修改的角度看，直接添加字段加default 数值，效率太低，成本太高，锁表TM6号锁。

三. 添加字段,但是不加default,后续对字段设置default值

      本次对比不太严谨，在已添加的字段上，在次添加（但是测试效果能体现就行)

1)相同，观察申请锁资源

Session 1

SQL> delete a where rownum=1;

1 row deleted.

Session 2

SQL> alter session set ddl_lock_timeout=600;

SQL>  alter table a add C_LHR_NEW VARCHAR2(100);

会话hang住，查询申请的锁资源

SQL> select object_id from dba_objects where owner='YZ' and object_name='A';

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

SQL> select sid,id1,type,lmode,request,ctime,block from v$lock where id1=89526;

SID ID1 TY LMODE REQUEST CTIME BLOCK
------ ---------- -- ---------- ---------- ---------- ----------
312 89526 OD 6 0 10 0
329 89526 TM 3 0 30 0
312 89526 TM 3 0 10 0

未发现被阻塞的现象，查询测试环境被阻塞的Session，可以发现申请一个TX锁资源

SQL> select sid,id1,type,lmode,request,ctime,block from v$lock where REQUEST>0;

SID ID1 TY LMODE REQUEST CTIME BLOCK
------ ---------- -- ---------- ---------- ---------- ----------
312 131075 TX 0 4 74 0

可以发现，并未对表添加TM>3级别锁，不锁表，只是业务表有DML操作，无法添加字段。

2）观察时间 
session1 回滚
SQL>rollback;

Session2 秒出结果，也就是说，不修改数据行，只是修改数据字典相关记录信息。

查询数据

SQL> set autotrace TRACE
SQL> select * from a where rownum=1;

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)

  3）对表新增加的列，增加default值

SQL> ALTER TABLE a MODIFY C_LHR_NEW VARCHAR2(100) DEFAULT 'LHR';

Table altered.

Elapsed: 00:00:00.04   秒出结果

查询数据

SQL> select C_LHR_NEW from a where rownum=1;

C_LHR_NEW
----------------------------------------------------------------------------------------------------

Null

SQL>alter table a modify C_LHR_NEW not null
*
ERROR at line 1:
ORA-02296: cannot enable (YZ.) - null values found

4）也就是说，添加字段，修改默认值，修改not null属性无法操作，因为列存在null值
还一种

SQL> alter table a add C_LHR_NEW2 varchar2(200) default 'LHR' not null;

Table altered.

Elapsed: 00:00:00.04  秒出

SQL> select C_LHR,C_LHR_NEW,C_LHR_NEW2 FROM A where rownum=1；

C_LHR C_LHR_NEW C_LHR_NEW2
---------- ---------- --------------------
LHR                  LHR

5）使用上述的方法，进行查询数据，是否真实存储数据，还是其它方式存储的数据方式

创建测试表

SQL> create table b as select * from dba_objects ;

SQL> alter table b add c_name varchar2(100) default 'LHR' not null;        

SQL> select count(*) from b where c_name='CC';

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

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID 9qj24brgbnrkg, child number 0
-------------------------------------
select count(*) from b where c_name='CC'

Plan hash value: 749587668

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 347 (100)| |
| 1 | SORT AGGREGATE | | 1 | 52 | | |

|* 2 | TABLE ACCESS FULL| B | 14 | 728 | 347 (1)| 00:00:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(NVL("C_NAME",'LHR')='CC')    

也就是说，对于这种方式创建的oracle列，实际上并未存储数据，只是显示查询使用nvl参数当无数据时是null值，则显示LHR数据default值

SQL> update b set c_name='DD' where object_id=20;

SQL> commit;

SQL> select count(*) from b where c_name='DD';

COUNT(*)
----------
1

   2 - filter(NVL("C_NAME",'LHR')='DD')

补充说明及总结

1.添加字段,不使用default 秒出结果,只是对数据字典进行修改；

2.添加字段，使用default 的值并且不加not null,则会修改数据字典外，对每一行记录真正的修改数据操作,大表而言代价非常大！

3.添加字段,使用default + not null，则oracle使用另外一种机制，达到不真正修改表的行记录，但是能满足查询需求！ 某些场景很适用。

Oracle11g中，在添加一个包含DEFAULT值的NOT NULL字段，Oracle不会去更新现有的数据，Oracle需要做的不过是将默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中。这张表利用BLOB字段存储ALTER TABLE添加的DEFAULT值

   

补充：20210708，公司大佬认为Oracle已经对新增字段add column default 'xx'值实现了只对数据字典进行调整！而不会真正修改数据，为此，本篇文档进行了如下版本的补充内容：

12.1、12.2版本进行了测试结果一致

set timing on
alter table a add t1 varchar2(10) default 'c'; 
Elapsed: 00:00:01.77
insert into  yz.a select * from a;
Elapsed: 00:00:22.44
insert into  yz.a select * from a;
2325472 rows created.
Elapsed: 00:00:49.85
将测试表数据增大,再次增加字段测试
alter table a add t2 varchar2(10) default 'c'; 
Elapsed: 00:00:00.72

insert into a(object_id) values(1232145215215);


set autotrace on
select object_id,t1,t2 from a where object_id=1232145215215;
 OBJECT_ID T1                   T2
---------- -------------------- --------------------
1.2321E+12 c                    c
   1 - filter("OBJECT_ID"=1232145215215)

select object_id,t1,t2 from a where t2='1232';
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   727 | 13813 |   396   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| A    |   727 | 13813 |   396   (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T2"='1232')

11.2.0.4

Alter table add new_column; 秒级别完成,只修改数据字典信息，不修改数据行;

Alter table add new_column default ‘xx’;真正修改每一行记录，对于大表消耗时间长，产生大量Redo;

Alter table add new_column;Alter table modify new_column default ‘xx’；秒出,但是原有数据新增字段的值为null,新写入的数据使用default值；

Alter table add new_column default ‘xx’ not null; 秒出！

>=12.1

Alter table add new_column default ‘xx’;并不会真正修改每一条记录，所以DB在12c的朋友们放心大胆的加字段吧！！！