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

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) 312SQL*Net message from client 329

 

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

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

Elapsed: 00:40:59.26

 

 

 

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

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

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使用另外一种机制，达到不真正修改表的行记录，但是能满足查询需求！ 某些场景很适用。

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