Modifying Table Partitions

Modifying Table PartitionsModifyingTablePartitionsOracleTipsbyBurleson Youcanmodifyatableortablepartitioninanyofthewa

大家好,又见面了,我是你们的朋友全栈君。

Modifying Table PartitionsModifying Table Partitions


Oracle Tips by Burleson
 

You can modify a table or table partition in any of the ways described in the subsections following. You cannot combine partition operations with other partition operations or with operations on the base table in one ALTER TABLE statement. You cannot move a partition with subpartitions; each subpartition must be individually moved.

Add Partition. Use ALTER TABLE ADD PARTITION to add a partition to the high end of the table (after the last existing partition). If the first element of the partition bound of the high partition is MAXVALUE, you cannot add a partition to the table. You must split the high partition.

You can add a partition to a table even if one or more of the table indexes or index partitions are marked UNUSABLE.

You must use the SPLIT PARTITION clause to add a partition at the beginning or the middle of the table.

The following example adds partition jan99 to tablespace yr99 for table sales: 

ALTER TABLE sales
  ADD PARTITION jan99 VALUES LESS THAN( ‘990201’ )
  TABLESPACE yr99;

Drop Partition. ALTER TABLE DROP PARTITION drops a partition and its data. If you want to drop a partition but keep its data in the table, you must merge the partition into one of the adjacent partitions.

If you drop a partition and later insert a row that would have belonged to the dropped partition, the row will be stored in the next higher partition. However, if you drop the highest partition, the insert will fail because the range of values represented by the dropped partition is no longer valid for the table.

This statement also drops the corresponding partition in each local index defined on  table. The index partitions are dropped even if they are marked as unusable.

If there are global indexes defined on the  table, and the partition you want to drop is not empty, dropping the partition marks all the global, nonpartitioned indexes and all the partitions of global partitioned indexes as unusable.

When a table contains only one partition, you cannot drop the partition. You must drop the table.

The following example drops partition jan98:

ALTER TABLE sales DROP PARTITION jan98;

Exchange Partition. This form of ALTER TABLE converts a partition to a nonpartitioned table, and a table to a partition, by exchanging their data segments. You must have ALTER TABLE privileges on both tables to perform this operation.

The statistics of the table and partition, including table, column, index statistics, and histograms, are exchanged. The aggregate statistics of the partitioned table are recalculated. The logging attribute of the table and partition is exchanged.

The following example converts partition feb99 to table sales_feb99:

ALTER TABLE sales
  EXCHANGE PARTITION feb99 WITH TABLE sales_feb99
   WITHOUT VALIDATION;

Modify Partition. Use the MODIFY PARTITION options of ALTER TABLE to:

* Mark local index partitions corresponding to a table partition as unusable.

* Rebuild all the unusable local index partitions corresponding to a table partition.

* Modify the physical attributes of a table partition.

The following example marks all the local index partitions corresponding to the apr96 partition of the procurements table UNUSABLE:

ALTER TABLE procurements MODIFY PARTITION apr99
  UNUSABLE LOCAL INDEXES;

The following example rebuilds all the local index partitions that were marked UNUSABLE:

ALTER TABLE procurements MODIFY PARTITION jan98
  REBUILD UNUSABLE LOCAL INDEXES;

The following example changes MAEXTENTS for partition KANSAS_OFF:

ALTER TABLE branch MODIFY PARTITION kansas_off
  STORAGE(MAXEXTENTS 100) LOGGING;

Merge Partition. New in Oracle8i, the MERGE capability promised in 8 was fulfilled. The MERGE option for PARTITIONS in the ALTER TABLE command allows two partitions to be merged into one larger partition.

ALTER TABLE branch
MERGE PARTITIONS kansas_off,missouri_off
INTO NEW PARTITION midwest_off
STORAGE (INITIAL 1M NEXT 1M MAXEXTENTS 200);

Move Partition or Subpartition. This ALTER TABLE option moves a table partition or subpartition to another segment. MOVE PARTITION always drops the partition’s old segment and creates a new segment, even if you do not specify a new tablespace.

If partition_name is not empty, MOVE PARTITION marks all corresponding local index partitions, all global nonpartitioned indexes, and all the partitions of global partitioned indexes as unusable.

ALTER TABLE MOVE PARTITION or  SUBPARTITION obtains its parallel attribute from the PARALLEL clause, if specified. If not specified, the default PARALLEL attributes of the table, if any, are used. If neither is specified, it performs the move without using parallelism.

The PARALLEL clause on MOVE PARTITION does not change the default PARALLEL attributes of table.

The following example moves partition station3 to tablespace ts099:

ALTER TABLE trains

MOVE PARTITION station3 TABLESPACE ts099 NOLOGGING;

Merge Partition. While there is no explicit MERGE statement, you can merge a partition using either the DROP PARTITION or EXCHANGE PARTITION clause. You can use either of the following strategies to merge table partitions.

If you have data in partition PART1, and no global indexes or referential integrity constraints on the table PARTS, you can merge table partition PART1 into the next-highest partition, PART2.

To merge partition PART1 into partition PART2:

1.        Export the data from PART1.

2.        Issue the following statement:

       ALTER TABLE PARTS DROP PARTITION PART1;

3.        Import the data from step 1 into partition PART2.

TIP: The corresponding local index partitions are also merged.      

Here is another way to merge partition PART1 into partition PART2:

1.        Exchange partition PART1 of table PARTS with “dummy” table PARTS_DUMMY.

2.        Issue the following statement:

   ALTER TABLE PARTS DROP PARTITION PART1;

3.        Insert as SELECT from the “dummy” tables to move the data from PART1 back into PART2.

Split Partition. The SPLIT PARTITION option divides a partition into two partitions, each with a new segment, new physical attributes, and new initial extents. The segment associated with the old partition is discarded.

This statement also performs a matching split on the corresponding partition in each local index defined on the table. The index partitions are split even if they are marked unusable.

With the exception of the TABLESPACE attribute, the physical attributes of the LOCAL index partition being split are used for both new index partitions. If the parent LOCAL index lacks a default TABLESPACE attribute, new LOCAL index partitions will reside in the same tablespace as the corresponding newly created partitions of the underlying table.

If you do not specify physical attributes (PCTFREE, PCTUSED, INITRANS, MAXTRANS, STORAGE) for the new partitions, the current values of the partition being split are used as the default values for both partitions.

If partition_name is not empty, SPLIT PARTITION marks all affected index partitions as unusable. This includes all global index partitions, as well as the local index partitions that result from the split.

The PARALLEL clause on SPLIT PARTITION does not change the default PARALLEL attributes of table.

The following example splits the old partition station5, thereby creating a new partition for station9:

ALTER TABLE trains
  SPLIT PARTITION STATION5 AT ( ’50-001′ )
  INTO (
    PARTITION station5 TABLESPACE train009 (MINEXTENTS 2),
    PARTITION station9 TABLESPACE train010 )
  PARALLEL ( DEGREE 9 );

Truncate Partition or Subpartition. Use TRUNCATE PARTITION to remove all rows from a partition or a subpartition in a table. Freed space is deallocated or reused, depending on whether DROP STORAGE or REUSE STORAGE is specified in the clause.

This statement truncates the corresponding partition or subpartition in each local index defined on the table. The local index partitions are truncated even if they are marked as unusable. The unusable local index partitions are marked valid, resetting the UNUSABLE indicator.

If global indexes are defined on the table, and the partition or subpartition you want to truncate is not empty, truncation marks all the global nonpartitioned indexes and all the partitions of global partitioned indexes as unusable.

If you want to truncate a partition or subpartition that contains data, you must first disable any referential integrity constraints on the table. Alternatively, you can delete the rows and then truncate the partition.

The following example deletes all the data in the part_17 partition and deallocates the freed space:

ALTER TABLE shipments
  TRUNCATE PARTITION part_17 DROP STORAGE;

Rename. Use the RENAME option of ALTER TABLE to rename a table or to rename a partition. The following example renames a table:

ALTER TABLE emp RENAME TO employee;

In the following example, partition EMP3 is renamed:

ALTER TABLE employee RENAME PARTITION emp3 TO employee3;

Altering a Table’s Subpartitions  

Partitioning was complex enough; now, with the addition of subpartitioning, including the ability to do a mixed partition (hash and range), a whole new level of complexity is added to the Oracle ALTER commands. This new layer of complexity deals with subpartitions. Let’s examine a few ALTER commands for use with Oracle and table subpartitions.

Modify Partition partition_name or Add Subpartition. This form of the ALTER TABLE command adds a subpartition. In a table where a new subpartition is added, any local indexes involving the subpartition must be rebuilt. To add subpartitions, the table must already be composite-partitioned (i.e., have subpartitions.) An example would be to add four biweekly subpartitions to the sales table for each month (assuming a weekly set of subpartitions already exists):

ALTER TABLE sales
  MODIFY PARTITION feb99 ADD SUBPARTITION feb99_biweek1
    TABLESPACE feb_subs;
 ALTER TABLE sales
  MODIFY PARTITION feb99 ADD SUBPARTITION feb99_biweek2
    TABLESPACE feb_subs;
ALTER TABLE sales
  MODIFY PARTITION feb99 ADD SUBPARTITION feb99_biweek3
    TABLESPACE feb_subs;
ALTER TABLE sales
  MODIFY PARTITION feb99 ADD SUBPARTITION feb99_biweek4
    TABLESPACE feb_subs;
 
This example creates four additional subpartitions for the feb99 partition of the sales table.

Exchange Partition or Subpartition. This form of ALTER TABLE converts a partition or subpartition to a nonpartitioned table, and a table to a partition by exchanging their data segments. You must have ALTER TABLE privileges on both tables to perform this operation.

The statistics of the table and partition, including table, column, index statistics, and histograms, are exchanged. The aggregate statistics of the partitioned table are recalculated. The logging attribute of the table and partition is exchanged.

The following example converts subpartition feb99_biweek1 to table sales_feb99_biweek1:

ALTER TABLE sales
  EXCHANGE SUBPARTITION feb99_biweek1 WITH TABLE sales_feb99_biweek1
   WITHOUT VALIDATION;

Modify Subpartition. Use the MODIFY SUBPARTITION options of ALTER TABLE to:

* Mark local index partitions corresponding to a table subpartition as unusable.

* Rebuild all the unusable local index subpartitions corresponding to a table subpartition.

* Modify the physical attributes of a table subpartition.

The following example marks all the local index subpartitions corresponding to the apr99 partition of the procurements table as UNUSABLE:

ALTER TABLE procurements MODIFY SUBPARTITION apr99_biweek1
  UNUSABLE LOCAL INDEXES;

The following example rebuilds all the local index partitions that were marked UNUSABLE:

ALTER TABLE procurements MODIFY SUBPARTITION apr99_biweek1

  REBUILD UNUSABLE LOCAL INDEXES;

The following example changes MAXEXTENTS for subpartition kansas_off_

ALTER TABLE branch MODIFY SUBPARTITION kansas_off_sub1
  STORAGE(MAXEXTENTS 100) LOGGING;

Move Subpartition. This ALTER TABLE option moves a table subpartition to another segment. MOVE PARTITION always drops the subpartition’s old segment and creates a new segment, even if you do not specify a new tablespace.

If subpartition_name is not empty, MOVE SUBPARTITION marks all corresponding local index subpartitions, all global nonpartitioned indexes, and all the partitions of global partitioned indexes as unusable.

ALTER TABLE MOVE SUBPARTITION obtains its parallel attribute from the PARALLEL clause, if specified. If not specified, the default PARALLEL attributes of the table, if any, are used. If neither is specified, it performs the move without using parallelism.

The PARALLEL clause on MOVE SUBPARTITION does not change the default PARALLEL attributes of table.

The following example moves subpartition station3_sub1 to tablespace

ALTER TABLE trains
   MOVE SUBPARTITION station3_sub1 TABLESPACE ts098 NOLOGGING;

Truncate Subpartition. Use TRUNCATE SUBPARTITION to remove all rows from a subpartition in a table. Freed space is deallocated or reused depending on whether DROP STORAGE or REUSE STORAGE is specified in the clause.

This statement truncates the corresponding partition in each local index defined on the table. The local index partitions are truncated even if they are marked as unusable. The unusable local index partitions are marked valid, resetting the UNUSABLE indicator.

If there are global indexes defined on the table, and the subpartition you want to truncate is not empty, truncating the subpartition marks all the global nonpartitioned indexes and all the subpartitions of global partitioned indexes as unusable.

If you want to truncate a partition that contains data, you must first disable any referential integrity constraints on the table. Alternatively, you can delete the rows and then truncate the subpartition.

The following example deletes all the data in the subpart_17a subpartition and deallocates the freed space:

ALTER TABLE shipments

  TRUNCATE SUBPARTITION subpart_17a DROP STORAGE;

Rename. Use the RENAME option of ALTER TABLE to rename a table or to rename a subpartition. In the following example, partition subemp3a is renamed:

ALTER TABLE employee
RENAME SUBPARTITION subemp3a TO subemployee3a;

 

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/153308.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

  • SOAP 和WSDL 是什么关系?

    SOAP 和WSDL 是什么关系?最近在学XML,还有ORACLE的ERP,有两个概念学习了一下: SOAP(SimpleObjectAccessProtocol)简单对象访问协议是在分散或分布式的环境中交换信息的简单的协议,是一个基于XML的协议,它包括四个部分:SOAP封装(envelop),封装定义了一个描述消息中的内容是什么,是谁发送的,谁应当接受并处理它以及如何处理它们的框架;SOAP编码规则(encod

  • 2021美赛A题解题思路_小学什么题上有A卷B卷

    2021美赛A题解题思路_小学什么题上有A卷B卷准时赴约。等待开题中……

  • windows下用pycharm安装tensorflow简易教程

    windows下用pycharm安装tensorflow简易教程2019.4.14更新下面的内容挺老了,建议批判性阅读,各种版本一直在变化,最好的教程,果然还是tensorflow和pytorch的英文原网。Windows下面办公还行,不是很适合开发,也就跑跑小代码。我现在一般在windows上使用SSH连接远程linux的服务器,直接使用远程配置的解释器环境(pycharm有相应SSH功能,配置一下就好),这样可以方便的开着音乐,边看资料边coding…

  • Python set() 函数

    Python set() 函数测试示例:>>>x=set('runoob')>>>y=set('google')>>>x,y

  • 反向传播算法(过程及公式推导)「建议收藏」

    反向传播算法(过程及公式推导)「建议收藏」一、反向传播的由来在我们开始DL的研究之前,需要把ANN—人工神经元网络以及bp算法做一个简单解释。关于ANN的结构,我不再多说,网上有大量的学习资料,主要就是搞清一些名词:输入层/输入神经元,输出

  • linux的vi命令详解_useradd命令详解

    linux的vi命令详解_useradd命令详解最近vi用的多,很多技巧不知道,备注一份,vi编辑器是所有Unix及Linux系统下标准的编辑器,它的强大不逊色于任何最新的文本编辑器,这里只是简单地介绍一下它的用法和一小部分指令。由于对Unix及Linux系统的任何版本,vi编辑器是完全相同的,因此您可以在其他任何介绍vi的地方进一步了解它。Vi也是Linux中最基本的文本编辑器,学会它后,您将在Linux的世界里畅行无阻。

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号