oracle11g增加字段,Oracle11g新特性：在线操作功能增强-表增加包含默认值的字段（转载）...

在11g以前，表中新增一个NOT NULL的字段是十分痛苦的事情，尤其是表很大的情况。不光是执行速度慢，而且由于现有数据长度的变化，很容易造成表中大量的行链接情况。

在11g中，这种情况得到了彻底的改善，Oracle通过在数据字典中记录DEFAULT值，避免了繁重的更新操作，增加非空字段的时间和增加一个可空字段的时间完全一样。

先来看看10g中添加一个包含DEFAULT值的非空字段的情况：

SQL

>

CONN YANGTK

/

YANGTK

@YTK102已连接

。 SQL

>

CREATE

TABLE

T

AS

SELECT

*

FROM

DBA_OBJECTS; 表已创建。 SQL

>

SELECT

COUNT

(

*

)

FROM

T;

COUNT

(

*

)

--

--------

50357

SQL

>

SET

TIMING

ON

SQL

>

ALTER

TABLE

T

ADD

NEW_COL

CHAR

(

1000

)

DEFAULT

'

LARGE COLUMN

'

NOT

NULL

; 表已更改。 已用时间:

00

:

00

:

17.04

SQL

>

SET

TIMING

OFF

SQL

>

SELECT

SUM

(BYTES)

/

1024

/

1024

FROM

USER_SEGMENTS

WHERE

SEGMENT_NAME

=

'

T

'

;

SUM

(BYTES)

/

1024

/

1024

--

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

72

SQL

>

SELECT

*

FROM

V$VERSION; BANNER

--

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

Oracle

Database

10g Enterprise Edition Release

10.2

.

0.1

.

0

-

Prod PL

/

SQL Release

10.2

.

0.1

.

0

-

Production CORE

10.2

.

0.1

.

0

Production TNS

for

32

-

bit

Windows: Version

10.2

.

0.1

.

0

-

Production NLSRTL Version

10.2

.

0.1

.

0

-

Production

在10g中，5万条记录添加一个CHAR(1000)的新增NOT NULL列，需要17秒，而且更新后表的大小为72M。

下面看看11g中的表现：

SQL

>

CONN YANGTK

/

yangtk

@ORA11G已连接

。 SQL

>

CREATE

TABLE

T

AS

SELECT

*

FROM

DBA_OBJECTS; 表已创建。 SQL

>

SELECT

COUNT

(

*

)

FROM

T;

COUNT

(

*

)

--

--------

68376

SQL

>

SET

TIMING

ON

SQL

>

ALTER

TABLE

T

ADD

NEW_COL

CHAR

(

1000

)

DEFAULT

'

LARGE COLUMN

'

NOT

NULL

; 表已更改。 已用时间:

00

:

00

:

00.15

SQL

>

SET

TIMING

OFF

SQL

>

SELECT

SUM

(BYTES)

/

1024

/

1024

FROM

USER_SEGMENTS

WHERE

SEGMENT_NAME

=

'

T

'

;

SUM

(BYTES)

/

1024

/

1024

--

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

9

SQL

>

SELECT

*

FROM

V$VERSION; BANNER

--

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

Oracle

Database

11g Enterprise Edition Release

11.1

.

0.6

.

0

-

Production PL

/

SQL Release

11.1

.

0.6

.

0

-

Production CORE

11.1

.

0.6

.

0

Production TNS

for

Linux: Version

11.1

.

0.6

.

0

-

Production NLSRTL Version

11.1

.

0.6

.

0

-

Production

将近7万的数据，添加字段仅用了0.15秒。性能完全没有可比性。从更新后表的大小也可以看到明显的区别。这里只看了执行时间和占用空间两个方面，从生成REDO和UNDO的大小看，前者同样远远超过了后者。     Oracle采用了何种方法实现了如此的性能提升，说起来也很简单。Oracle11g中，在添加一个包含DEFAULT值的NOT NULL字段，Oracle不会去更新现有的数据，Oracle需要做的不过是将默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中。     这张表利用BLOB字段存储ALTER TABLE添加的DEFAULT值：SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'T'; OBJECT_ID ---------- 70482 SQL> SELECT * FROM SYS.ECOL$ WHERE TABOBJ# = 70482; TABOBJ# COLNUM BINARYDEFVAL ---------- ---------- ---------------------------------------- 70482 16 4C4152474520434F4C554D4E     简单转换一下： SQL> SET SERVEROUT ON SQL> DECLARE 2 V_BLOB VARCHAR2(32767) DEFAULT '4C4152474520434F4C554D4E'; 3 BEGIN 4 FOR I IN 1..LENGTH(V_BLOB)/2 LOOP 5 DBMS_OUTPUT.PUT(CHR(TO_NUMBER(SUBSTR(V_BLOB, (I - 1) * 2 + 1, 2), 'XXX'))); 6 END LOOP; 7 DBMS_OUTPUT.NEW_LINE; 8 END; 9 / LARGE COLUMN PL/SQL procedure successfully completed.     Oracle在读取数据时，发现COLUMN 16列为非空，但是在存储的数据中找不到该列，于是就会从ECOL$中读取该列的默认值。     这个功能不但提高了添加非空字段的速度，而且节省了大量的磁盘空间。相比之下，在数据字典中查找DEFAULT VALUE的代价小的几乎可以忽略。