Drop unused columns --1

最新推荐文章于 2022-01-19 14:25:15 发布

lunar2000

最新推荐文章于 2022-01-19 14:25:15 发布

阅读量1.5k

点赞数

分类专栏： oracle 文章标签： table object statistics constraints database sql

本文链接：https://blog.csdn.net/lunar2000/article/details/7910437

版权

oracle 专栏收录该内容

137 篇文章 2 订阅

订阅专栏

当一个表中包含unused columns时，那么要做exchange partition就会报错。

但是，如果知道表的原始结构，那么做一个和原始结构一样的表，并也将那4列设置为unused，那么可以做exchange partition：

SQL> conn lunar/lunar
Connected.
SQL> set time on
04:40:43 SQL> set timing on
04:40:45 SQL> create table fq_test
04:40:51   2 (
04:40:51   3 OBJECT_ID      NUMBER,
04:40:51   4 OWNER          VARCHAR2(30),
04:40:51   5 SUBOBJECT_NAME VARCHAR2(30),
04:40:51   6 OBJECT_NAME    VARCHAR2(128),
04:40:51   7 STATUS         VARCHAR2(7),
04:40:51   8 TIMESTAMP      VARCHAR2(19),
04:40:51   9 CREATED        DATE,
04:40:51 10 GENERATED      VARCHAR2(1),
04:40:51 11 OBJECT_TYPE    VARCHAR2(19)
04:40:51 12 )
04:40:51 13 partition by range (OBJECT_ID)
04:40:51 14 (
04:40:51 15 partition PART_1 values less than ('10000'),
04:40:51 16 partition PART_2 values less than ('20000'),
04:40:51 17 partition PART_3 values less than ('30000'),
04:40:51 18 partition PART_4 values less than ('40000'),
04:40:51 19 partition PART_5 values less than (MAXVALUE)
04:40:51 20 );

Table created.

Elapsed: 00:00:00.65
04:40:52 SQL>
04:41:23 SQL> insert into fq_test(object_id,owner,subobject_name,object_name,status,timestamp,created,object_type)
04:41:36 2 select object_id,owner,subobject_name,object_name,status,timestamp,created,object_type from dba_objects
04:41:36 3 nologging;

11449 rows created.

Elapsed: 00:00:00.87
04:41:38 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
04:41:44 SQL>
04:41:44 SQL> select count(*) from fq_test;

COUNT(*)
----------
11449

Elapsed: 00:00:00.06
04:42:51 SQL> select count(*) from fq_test partition(PART_1);

COUNT(*)
----------
9633

Elapsed: 00:00:00.07
04:42:57 SQL> select count(*) from fq_test partition(PART_2);

COUNT(*)
----------
1816

Elapsed: 00:00:00.01
04:43:04 SQL> select count(*) from fq_test partition(PART_3);

COUNT(*)
----------
0

Elapsed: 00:00:00.00
04:43:11 SQL> desc fq_test
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_ID                                          NUMBER
OWNER                                              VARCHAR2(30)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
STATUS                                             VARCHAR2(7)
TIMESTAMP                                          VARCHAR2(19)
CREATED                                            DATE
GENERATED                                          VARCHAR2(1)
OBJECT_TYPE                                        VARCHAR2(19)

04:43:37 SQL> alter table fq_test set unused column created;

Table altered.

Elapsed: 00:00:00.88
04:44:30 SQL> alter table fq_test set unused column object_type;

Table altered.

Elapsed: 00:00:00.19
04:44:35 SQL> insert into fq_test select * from fq_test;

11449 rows created.

Elapsed: 00:00:00.16
04:44:56 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
04:44:58 SQL>

04:51:55 SQL> create table fq_test2
04:52:30   2 (
04:52:30   3 OBJECT_ID      NUMBER,
04:52:30   4 OWNER          VARCHAR2(30),
04:52:30   5 SUBOBJECT_NAME VARCHAR2(30),
04:52:30   6 OBJECT_NAME    VARCHAR2(128),
04:52:30   7 STATUS         VARCHAR2(7),
04:52:30   8 TIMESTAMP      VARCHAR2(19),
04:52:30   9 CREATED        DATE,
04:52:30 10 GENERATED      VARCHAR2(1),
04:52:30 11 OBJECT_TYPE    VARCHAR2(19)
04:52:30 12 )
04:52:30 13 partition by range (OBJECT_ID)
04:52:30 14 (
04:52:30 15 partition PART_1 values less than ('10000'),
04:52:30 16 partition PART_2 values less than ('20000'),
04:52:30 17 partition PART_3 values less than ('30000'),
04:52:30 18 partition PART_4 values less than ('40000'),
04:52:30 19 partition PART_5 values less than (MAXVALUE)
04:52:30 20 );

Table created.

Elapsed: 00:00:00.09
04:52:30 SQL> create table fq_test_part_2 as select * from FQ_TEST2 partition(PART_2);
create table fq_test_part_2 as select * from FQ_TEST2 partition(PART_2)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Elapsed: 00:00:00.03
04:52:38 SQL> drop table fq_test_part_2;

Table dropped.

Elapsed: 00:00:00.05
04:52:48 SQL> create table fq_test_part_2 as select * from FQ_TEST2 partition(PART_2);

Table created.

Elapsed: 00:00:00.03
04:52:52 SQL> alter table fq_test_part_2 set unused column created;
alter table fq_test_part_2 set unused column object_type;

Table altered.

Elapsed: 00:00:00.04
04:53:05 SQL>
Table altered.

Elapsed: 00:00:00.02
04:53:06 SQL> alter table fq_test exchange partition PART_2 with table fq_test_part_2
04:53:30 2 ;

Table altered.

Elapsed: 00:00:00.14
04:53:32 SQL>

了解一下Set unused columns的官方解释：

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 theDROP clause.

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

See Also:

Oracle Database Concepts for more information on dependencies

CHECKPOINT SpecifyCHECKPOINT if you want Oracle Database to apply a checkpoint for theDROPCOLUMN operation after processing integer rows;integer is optional and must be greater than zero. Ifinteger is greater than the number of rows in the table, then the database applies a checkpoint after all the rows have been processed. If you do not specifyinteger, then the database sets the default of 512.Checkpointing cuts down the amount of undo logs accumulated during theDROPCOLUMN operation to avoid running out of undo space. However, if this statement is interrupted after a checkpoint has been applied, then the table remains in an unusable state. While the table is unusable, the only operations allowed on it areDROP TABLE, TRUNCATE TABLE, andALTER TABLE DROP ... COLUMNS CONTINUE (described in sections that follow).

You cannot use this clause with SET UNUSED, because that clause does not remove column data.

DROP COLUMNS CONTINUE Clause

Specify DROP COLUMNS CONTINUE to continue the drop column operation from the point at which it was interrupted. Submitting this statement while the table is in an invalid state results in an error.

Restrictions on Dropping Columns Dropping columns is subject to the following restrictions:

Each of the parts of this clause can be specified only once in the statement and cannot be mixed with any otherALTERTABLE clauses. For example, the following statements are not allowed:

ALTER TABLE t1 DROP COLUMN f1 DROP (f2);
ALTER TABLE t1 DROP COLUMN f1 SET UNUSED (f2);
ALTER TABLE t1 DROP (f1) ADD (f2 NUMBER);
ALTER TABLE t1 SET UNUSED (f3) 
   ADD (CONSTRAINT ck1 CHECK (f2 > 0));

You can drop an object type column only as an entity. To drop an attribute from an object type column, use theALTERTYPE ... DROP ATTRIBUTE statement with theCASCADEINCLUDING TABLE DATA clause. Be aware that dropping an attribute affects all dependent objects. SeeDROP ATTRIBUTE for more information.
You cannot specify the DROP clause to drop a column from a compressed table or a partitioned table containing any compressed partition, unless you first disable data compression for the table or partition. You can only use theSETUNUSED clause
You can drop a column from an index-organized table only if it is not a primary key column. The primary key constraint of an index-organized table can never be dropped, so you cannot drop a primary key column even if you have specifiedCASCADECONSTRAINTS.
You can export tables with dropped or unused columns. However, you can import a table only if all the columns specified in the export files are present in the table (that is, none of those columns has been dropped or marked unused). Otherwise, Oracle Database returns an error.
You cannot drop a column on which a domain index has been built.
You cannot drop a SCOPE table constraint or a WITH ROWID constraint on a REF column.
You cannot use this clause to drop:
- A pseudocolumn, cluster column, or partitioning column. You can drop nonpartitioning columns from a partitioned table if all the tablespaces where the partitions were created are online and in read/write mode.
- A column from a nested table, an object table, or a table owned by SYS.