大表添加一个字段需求

测试目标：

   客户需求，一套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的朋友们放心大胆的加字段吧！！！






        

	      

	    

	  

	  

	    
      
        【推广】
        免费学中医，健康全家人
      
	  

    

    

          原文地址：https://www.cnblogs.com/lvcha001/p/11837569.html