ANBOB™

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以前是要修改现有表数据块上的数据，运行时间长，表所占空间有增加

打赏

微信扫一扫，打赏作者吧～