oracle更改列的默认值,oracle11g add default values columns(增加默认值列的改进)

oracle 11G 对 现有表增加指定默认值列方式发生了很大变化，无论是存储方式上,还是效率上，下面看一个简单的实验

sys@ANBOB> desc ecol$

Name Null? Type

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

TABOBJ# NUMBER

COLNUM NUMBER

BINARYDEFVAL BLOB

sys@ANBOB> select * from ecol$;

no rows selected

sys@ANBOB> create table testcol(id int ,name varchar2(10));

Table created.

sys@ANBOB> begin

2 for i in 1..10000 loop

3 insert into testcol values(i,'anbob'||i);

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

sys@ANBOB> select segment_name,segment_type,bytes/1024 nk from dba_segments where segment_name='TESTCOL';

SEGMENT_NAME SEGMENT_TYPE NK

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

TESTCOL TABLE 256

sys@ANBOB> set timing on

sys@ANBOB> alter table testcol add remark varchar2(20) default 'this is test!' not null;

Table altered.

Elapsed: 00:00:00.09

sys@ANBOB> desc ecol$

Name Null? Type

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

TABOBJ# NUMBER

COLNUM NUMBER

BINARYDEFVAL BLOB

sys@ANBOB> col binarydefval for a100

sys@ANBOB> select * from ecol$;

TABOBJ# COLNUM BINARYDEFVAL

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

73014 3 74686973206973207465737421

Elapsed: 00:00:00.00

sys@ANBOB> select object_name from dba_objects where object_id=73014;

OBJECT_NAME

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

TESTCOL

Elapsed: 00:00:00.02

sys@ANBOB> set timing off

sys@ANBOB> select segment_name,segment_type,bytes/1024 nk from dba_segments where segment_name='TESTCOL';

SEGMENT_NAME SEGMENT_TYPE NK

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

TESTCOL TABLE 256

sys@ANBOB> select * from v$version where rownum=1;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

sys@ANBOB> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@orazhang /]$ cd

[oracle@orazhang ~]$ . .bash_profile

[oracle@orazhang ~]$ ora

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 8月 16 17:12:20 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

sys@ORCL> create table testcol(id int ,name varchar2(10));

Table created.

sys@ORCL> begin

2 for i in 1..10000 loop

3 insert into testcol values(i,'anbob'||i);

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

sys@ORCL> select segment_name,segment_type,bytes/1024 nk from dba_segments where segment_name='TESTCOL';

SEGMENT_NAME SEGMENT_TYPE NK

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

TESTCOL TABLE 256

sys@ORCL> set timing on

sys@ORCL> alter table testcol add remark varchar2(20) default 'this is test!' not null;

Table altered.

Elapsed: 00:00:04.75

sys@ORCL> select segment_name,segment_type,bytes/1024 nk from dba_segments where segment_name='TESTCOL';

SEGMENT_NAME SEGMENT_TYPE NK

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

TESTCOL TABLE 640

Elapsed: 00:00:00.02

sys@ORCL> set timing off

sys@ORCL> select * from v$version where rownum=1;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

note:

1,11G增加了ecol$基表，储存默认值(blob)，应用相当于nvl(null,ecol$.object.columnsval)

2,11G以前是要修改现有表数据块上的数据，运行时间长，表所占空间有增加