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--
直接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--