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)


相关推荐

  • dump文件分析工具有哪些_如何解析xml文件

    dump文件分析工具有哪些_如何解析xml文件/前言/在讲解Mat工具之前我们先来看下几个关于Dump/文件的问题Dump文件是什么Dump文件是进程的内存镜像。可以把程序的执行状态通过调试器保存到dump文件中我们拿到Dump文件有什么用的?假如JVM因为内存溢出的原因宕机了,而程序的日志里面并没有关于溢出所在对象的信息,此时我们就需要通过分析Dump文件来找到问题原因Dump文件怎么生成?第一种在启动JVM时指定参数 #指定生成Dump文件的异常类型 -XX:+HeapDumpOnOutOf

  • nsga2 matlab,NSGA2算法特征选择MATLAB实现(多目标)

    nsga2 matlab,NSGA2算法特征选择MATLAB实现(多目标)利用nsga2进行进行特征选择其主要思想是:将子集的选择看作是一个搜索寻优问题(wrapper方法),生成不同的组合,对组合进行评价,再与其他的组合进行比较。这样就将子集的选择看作是一个是一个优化问题。需要优化的两个目标为特征数和精度。nsga2是一个多目标优化算法。具体的特征选择代码在上述代码的基础上改了两个①主函数②评价函数,增加了一个数据分成训练集和测试集的函数:MATLABfunction…

  • tomcat部署war包 接口404 网页能访问「建议收藏」

    tomcat部署war包 接口404 网页能访问「建议收藏」后端新手部署项目被自己坑哭了。访问网页都知道加一个打包的war包名字,访问接口的时候没有加。http://xxx.xxx.xxx/项目名/页面路径页面基地址http://xxx.xxx.xxx/shop/例:http://xxx.xxx.xxx/shop/index.jsp接口基地址:http://xxx.xxx.xxx/shop例:http://xxx.xxx.xxx/shop/goods/getAllGoods…

  • DVP协议

    DVP协议Whatisadigitalvideoport?Camera的并口传输方式很多地方叫做dvp接口,但是并没有统一的标准。AsfarasIknow,thereisnoDVPspecbutthepinoutseemstobesomethingofadefactostandard,eventhoughpartmanufacturers…

  • java ftl_.ftl文件 是什么文件 freemaker「建议收藏」

    java ftl_.ftl文件 是什么文件 freemaker「建议收藏」freemarker的文件一般以后缀ftl,ftl文件的头上要写这样才能在ftl中使用webwork的tag。freemarker确实是不错的模版语言引擎,尤其是处理对象图很方便,处理xml也很方便,还支持xpath什么是FreeMarker?FreeMarker是一个模版引擎,一个基于文本的模板输出工具(生成任意的HTML表单代码)。它是一个Javapackage,面向Java程序员的cla…

  • python 使用 with open() as 读写文件

    python 使用 with open() as 读写文件读文件:要以读文件的模式打开一个文件对象,使用Python内置的open()函数,传入文件名和标示符:>>>f=open(‘E:\python\python\test.txt’,’r’)标示符’r’表示读,这样,我们就成功地打开了一个文件。如果文件不存在,open()函数就会抛出一个IOError的错误,并且给出错误码和详细的信息告诉你文件不存在:…

发表回复

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

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