[20120607]多余的空格-画蛇添足.txt

在10.2.0.4下,给表增加字段,一般情况下仅仅执行:

alter table t add (x number);

11G下新特性可以给增加的字段赋予一个缺省值,例子如下:

alter table t add (x number default 1);

会马上返回,执行很快.

但是如果在10g下执行如何呢?
alter table t add (x number default 1);
如果表很大,执行会很慢.

但是如果这样执行结构如何呢.

alter table t add (x1 number default NULL);

alter table t add (x2 number default NULL );

做一个测试看看.

1.建立测试环境:

SQL> select * from v$version ;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

drop table t purge ;

create table t as select rownum id, 'test' name from dual connect by level 

--alter table t add (x number default 1); =>忽略测试

2.执行如下:

set timing on

alter table t add (x1 number );

alter table t add (x2 number default NULL);

alter table t add (x3 number default NULL ); 

alter table t add ( x4 number default NULL); 

alter table t add ( x5 number default NULL ); 

SQL> alter table t add (x1 number );

Table altered.

Elapsed: 00:00:00.11

SQL> alter table t add (x2 number default NULL);

Table altered.

Elapsed: 00:00:00.04

SQL> alter table t add (x3 number default NULL );

Table altered.

Elapsed: 00:02:26.98

SQL> alter table t add ( x4 number default NULL);

Table altered.

Elapsed: 00:00:00.03

SQL> alter table t add ( x5 number default NULL );

Table altered.

Elapsed: 00:01:30.75

--可以发现添加字段X3消耗了2分28秒,添加字段x5消耗了1:30秒.why?
--两种的区别仅仅是)前的空格有无!

3.做10046跟踪看看.

drop table t purge ;

create table t as select rownum id, 'test' name from dual connect by level 

alter session set events '10046 trace name context forever, level 12';

alter table t add (x3 number default NULL );

alter session set events '10046 trace name context off';

查看跟踪文件可以发现如下:

update "T" set "X3"=NULL

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.01          0          1          0           0

Execute      1     11.45      11.53         35        470     244646      100000

Fetch        0      0.00       0.00          0          0          0           0

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

total        2     11.45      11.54         35        471     244646      100000

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 5     (recursive depth: 1)

Rows     Row Source Operation

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

      0  UPDATE  T (cr=508 pr=28 pw=0 time=9057310 us)

 214094   TABLE ACCESS FULL T (cr=451 pr=35 pw=0 time=214178 us)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file scattered read                          9        0.00          0.00

  db file sequential read                         3        0.00          0.00

  log file switch completion                      3        0.16          0.32

********************************************************************************

看来oracle在分析sql语句时有点问题.切记不要这样写,特别是在10.2.0.4.其他版本我没有测试.