set unused然后drop和直接drop的性能对比

oracle网站上对set unsed使用的说明:

SET UNUSED Clause

Specify SET UNUSED to mark one or more columns as unused. Specifying this clause does not actually remove the target columns from each row in the table. That is, it does not restore the disk space used by these columns. Therefore, the response time is faster than when you execute the DROP clause.

You can view all tables with columns marked UNUSED in the data dictionary views USER_UNUSED_COL_TABS, DBA_UNUSED_COL_TABS, and ALL_UNUSED_COL_TABS.

Unused columns are treated as if they were dropped, even though their column data remains in the table rows. After a column has been marked UNUSED, you have no access to that column. A SELECT * query will not retrieve data from unused columns. In addition, the names and types of columns marked UNUSED will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column.

Note:
Until you actually drop these columns, they continue to count toward the absolute limit of 1000 columns in a single table. However, as with all DDL statements, you cannot roll back the results of this clause. That is, you cannot issue SET USED counterpart to retrieve a column that you have SET UNUSED. Please refer to CREATE TABLE for more information on the 1000-column limit.

Also, if you mark a LONG column as UNUSED, then you cannot add another LONG column to the table until you actually drop the unused LONG column.

实际测试过程:

SYS@orcl>desc adam.t
Name								   Null?    Type
----------------------------------------------------------------- -------- -------
N								   NOT NULL NUMBER
C									    VARCHAR2(10)

SYS@orcl>select count(*) from adam.t;
COUNT(*)
----------
2099999

SYS@orcl>create table adam.t5 as select * from adam.t;

Table created.

SYS@orcl>select count(*) from adam.t5;
COUNT(*)
----------
2099999

SYS@orcl>alter table adam.t drop column c;

Table altered.

Elapsed: 00:00:05.02
SYS@orcl>alter table adam.t5 set unused (c);

Table altered.

Elapsed: 00:00:00.02
SYS@orcl>alter table adam.t5 drop unused columns;

Table altered.

Elapsed: 00:00:03.74

参考资料:
dba_tab_columns和dba_tab_cols 两个视图的区别

Comments are closed.