oracle里如何快速drop column测试(慎用于生产系统，该操作导致表无法访问)

oracle里如何快速drop column测试

2009-05-20 11:21

平台: oracle 10.2.0.1

直接DROP COLUMN操作:

create table u_test.t2 as select rownum c1, rownum + 1 c2, 'test'||rownum c3 from dual connect by level <= 20000000;
--Elapsed: 00:02:06.61

ALTER TABLE u_test.t2 DROP COLUMN c1;
--Elapsed: 00:13:10.53

先SET UNUSED然后DROP UNUSED COLUMNS操作,效率上只有些许的提升:

create table u_test.t1 as select rownum c1, rownum + 1 c2, 'test'||rownum c3 from dual connect by level <= 20000000;
--Elapsed: 00:03:41.98

alter table u_test.t1 SET UNUSED (c1);
--Elapsed: 00:00:00.08

select * from DBA_UNUSED_COL_TABS;
OWNER           TABLE_NAME                          COUNT
--------------- ------------------------------ ----------
U_TEST          T1                                      1

ALTER TABLE u_test.t1 DROP UNUSED COLUMNS;
--Elapsed: 00:12:08.09

先SET UNUSED然后DROP UNUSED COLUMNS操作,并设置指定的CHECKPOINT,效率上也只有些许的提升:

alter table u_test.t1 SET UNUSED (c2);
--Elapsed: 00:00:00.24

ALTER TABLE u_test.t1 DROP UNUSED COLUMNS CHECKPOINT 512;
--Elapsed: 00:11:51.11

但是只要把列置为unused, 则可以当作表没有了该列, 这算是最快方式. 逻辑删除好处就是可以当资源紧张的时候节约处理时间, 当以后空余的时间再真正DROP掉. 注意DROP UNUSED COLUMNS的时候会有最高级的排它锁, 所以要在停业务的情况下进行.

create table u_test.t3 (c1 number, c2 varchar2(10));
alter table u_test.t3 modify c1 not null;
insert into u_test.t3 values (1,'test1');
commit;

alter table u_test.t3 set unused (c1);
desc u_test.t3;
Name             Null?    Type
---------------- -------- -----------------
C2                        VARCHAR2(10)

insert into u_test.t3 values (2,'test2');
ERROR at line 1:
ORA-00913: too many values

insert into u_test.t3 values ('test2');  
1 row created.
commit;

--End--