提高批量update或给大表增加有default值字段的两种方法比较

现状：cdmasmsevent表有一主键PK_cdmasmsevent，如果有其它所有就要单独加上。

alter table cdmasmsevent add col1 Char(1) default '0';
和
alter table cdmasmsevent add col1 Char(1);
update cdmasmsevent set col1='0';
alter table cdmasmsevent modify col1 default '0';
效果一样，执行效率也一样。

执行情况如下：

16:21:36 SQL> set timing on
16:21:39 SQL> select count(*) from cdmasmsevent;

COUNT(*)
----------
668304

Elapsed: 00:00:01.22

－－以下直接增加有default值字段的方式要330秒左右：

16:21:55 SQL> alter table cdmasmsevent add col1 Char(1) default '0';

Table altered.

Elapsed: 00:05:32.91
16:27:35 SQL> desc cdmasmsevent;
Name Null? Type
----------------------------------------- -------- ----------------------------
CDMASMSEVENTID NOT NULL NUMBER(9)
EVENTID NUMBER(9)
COMMAND VARCHAR2(9)
MDN VARCHAR2(32)
MDN_NEW VARCHAR2(32)
MIN VARCHAR2(10)
ESN VARCHAR2(10)
IMSI VARCHAR2(16)
CS_NETWORK NUMBER(9)
CS_PERMISSION NUMBER(5)
MO_PERMISSION CHAR(1)
MT_PERMISSION CHAR(1)
OA_PAYTYPE CHAR(1)
DA_PAYTYPE CHAR(1)
RESPSTATUS CHAR(1)
EVENTPRIORITY CHAR(1)
STATUS CHAR(1)
RESULT CHAR(1)
OCCURTIME DATE
FINISHTIME DATE
ERRCODE NUMBER(4)
GSM_PHONENUMBER VARCHAR2(32)
SERVICETYPE CHAR(1)
COL1 CHAR(1)

－－以下rename方式只要75秒左右：

16:27:38 SQL> alter table cdmasmsevent drop constraint PK_cdmasmsevent cascade;

Table altered.

Elapsed: 00:00:00.17
16:27:50 SQL> create table cdmasmsevent_bak as select a.*,0 col2 from cdmasmsevent a;

Table created.

Elapsed: 00:00:05.25
16:28:05 SQL> rename cdmasmsevent to cdmasmsevent_old;

Table renamed.

Elapsed: 00:00:00.06
16:28:31 SQL> rename cdmasmsevent_bak to cdmasmsevent;

Table renamed.

Elapsed: 00:00:00.04
16:28:37 SQL> ALTER TABLE cdmasmsevent ADD ( CONSTRAINT PK_cdmasmsevent PRIMARY KEY (CDMASMSEVENTID) USING INDEX TABLESPACE WACOS);

Table altered.

Elapsed: 00:00:35.27
16:29:20 SQL> desc cdmasmsevent;
Name Null? Type
----------------------------------------- -------- ----------------------------
CDMASMSEVENTID NOT NULL NUMBER(9)
EVENTID NUMBER(9)
COMMAND VARCHAR2(9)
MDN VARCHAR2(32)
MDN_NEW VARCHAR2(32)
MIN VARCHAR2(10)
ESN VARCHAR2(10)
IMSI VARCHAR2(16)
CS_NETWORK NUMBER(9)
CS_PERMISSION NUMBER(5)
MO_PERMISSION CHAR(1)
MT_PERMISSION CHAR(1)
OA_PAYTYPE CHAR(1)
DA_PAYTYPE CHAR(1)
RESPSTATUS CHAR(1)
EVENTPRIORITY CHAR(1)
STATUS CHAR(1)
RESULT CHAR(1)
OCCURTIME DATE
FINISHTIME DATE
ERRCODE NUMBER(4)
GSM_PHONENUMBER VARCHAR2(32)
SERVICETYPE CHAR(1)
COL1 CHAR(1)
COL2 NUMBER

16:29:43 SQL> drop table cdmasmsevent_old;

Table dropped.

Elapsed: 00:00:35.43
16:30:33 SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10881/viewspace-178077/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10881/viewspace-178077/