Oracle分区表基础运维-09删除分区

Oracle分区表基础运维-09删除分区

About Dropping Partitions and Subpartitions

Dropping Table Partitions

删除分区时要保存分区中的数据，请使用MERGE partition语句而不是DROP partition语句。

To preserve the data in the partition, use the MERGE PARTITION statement instead of the DROP PARTITION statement.

要在不删除分区的情况下删除分区中的数据，可以使用TRUNCATE分区语句。

To remove data in the partition without dropping the partition, use the TRUNCATE PARTITION statement.

从包含数据和全局索引的表中删除分区

有几种方法可以用于从包含数据和全局索引的表中删除分区。

如果分区包含数据，并且在表上定义了一个或多个全局索引，那么使用以下方法之一(方法1、2或3)删除表分区。

Dropping a Partition from a Table that Contains Data and Global Indexes

There are several methods you can use to drop a partition from a table that contains data and global indexes.

If the partition contains data and one or more global indexes are defined on the table,then use one of the following methods (method 1, 2 or 3) to drop the table partition.

Method 1

在不维护全局索引的情况下发出ALTER TABLE DROP PARTITION语句。

Issue the ALTER TABLE DROP PARTITION statement without maintaining global indexes.

之后，您必须重新构建任何全局索引(无论是否分区)，因为索引(或索引分区)已经被标记为不可用。下面的语句提供了一个从sales表中删除分区dec98，然后重新构建其全局非分区索引的示例。

Afterward, you must rebuild any global indexes (whether partitioned or not) because the index (or index partitions) has been marked UNUSABLE. The following statements provide an example of dropping partition dec98 from the sales table, then rebuilding its global nonpartitioned index.

ALTER TABLE sales DROP PARTITION dec98;ALTER INDEX sales_area_ix REBUILD;

如果索引sales_area_ix是一个范围分区的全局索引，那么索引的所有分区都需要重新构建。此外，不可能在一条语句中重建一个索引的所有分区。必须为索引中的每个分区发出单独的重新生成语句。下面的语句重新构建索引分区jan99_ix到dec99_ix。

If index sales_area_ix were a range-partitioned global index, then all partitions of the index would require rebuilding. Further, it is not possible to rebuild all partitions of an index in one statement. You must issue a separate REBUILD statement for each partition in the index. The following statements rebuild the index partitions jan99_ix to dec99_ix.

ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix;ALTER INDEX sales_area_ix REBUILD PARTITION feb99_ix;ALTER INDEX sales_area_ix REBUILD PARTITION mar99_ix;...ALTER INDEX sales_area_ix REBUILD PARTITION dec99_ix;

这种方法最适合于删除的分区包含该表中很大一部分数据的大型表。虽然异步全局索引维护使全局索引保持有效，而不需要任何索引维护，但您必须使用UPDATE indexes子句来启用此新功能。这种行为确保了向后兼容性。

This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table. While asynchronous global index maintenance keeps global indexes valid without the need of any index maintenance, you must use the UPDATE INDEXES clause to enable this new functionality. This behavior ensures backward compatibility.

Method 2

在发出ALTER TABLE DROP partition语句之前，发出DELETE语句来删除分区中的所有行。DELETE语句更新全局索引。

例如，要删除第一个分区，发出以下语句:

Issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE DROP PARTITION statement. The DELETE statement updates the global indexes.

For example, to drop the first partition, issue the following statements:

DELETE FROM sales partition (dec98);ALTER TABLE sales DROP PARTITION dec98;

这种方法最适合于小表，或者在大表中删除的分区只包含表中总数据的一小部分时。

This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.

Method 3

在ALTER TABLE语句中指定UPDATE indexes。这样做可以利用新的异步全局索引维护。索引仍然有效。

Specify UPDATE INDEXES in the ALTER TABLE statement. Doing so leverages the new asynchronous global index maintenance. Indexes remain valid.

ALTER TABLE sales DROP PARTITION dec98 UPDATE INDEXES;

Dropping Index Partitions

不能显式删除本地索引的分区。相反，只有当您从基础表中删除一个分区时，才会删除本地索引分区。

You cannot explicitly drop a partition of a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.

If a global index partition is empty, then you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. But, if a global index partition contains data, then dropping the partition causes the next highest partition to be marked UNUSABLE. For example, you would like to drop the index partition P1, and P2 is the next highest partition. You must issue the following statements:

ALTER INDEX npr DROP PARTITION P1;ALTER INDEX npr REBUILD PARTITION P2;

Dropping Multiple Partitions

可以使用SQL ALTER table语句的DROP PARTITION和DROP SUBPARTITION子句从范围或列表分区表中删除多个分区或子分区。

例如，下面的SQL语句从范围分区表sales中删除多个分区。

You can remove multiple partitions or subpartitions from a range or list partitioned table with the DROP PARTITION and DROP SUBPARTITION clauses of the SQL ALTER TABLE statement.

For example, the following SQL statement drops multiple partitions from the range-partitioned

table sales.

ALTER TABLE sales DROP PARTITION sales_q1_2008, sales_q2_2008,sales_q3_2008, sales_q4_2008;

更多数据库相关学习资料，可以查看我的ITPUB博客，网名chenoracle：

http://blog.itpub.net/29785807/