Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Modifying 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;

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.