Maintaining Partitioned Tables - Oracle 10g

This section describes how to perform partition and subpartition maintenance operations for both tables and indexes. We lists maintenance operations that can be performed on table partitions (or subpartitions) and, for each type of partitioning, lists the specific clause of the ALTER TABLE statement that is used to perform that maintenance operation.

ALTER TABLE Maintenance Operations for Table Partitions

ALTER TABLE Maintenance Operations for Table PartitionsALTER TABLE Maintenance Operations for Table Partitions

We lists maintenance operations that can be performed on index partitions, and indicates on which type of index (global or local) they can be performed. The ALTER INDEX clause used for the maintenance operation is shown.

Global indexes do not reflect the structure of the underlying table. If partitioned, they can be partitioned by range or hash. Partitioned global indexes share some, but not all, of the partition maintenance operations that can be performed on partitioned tables. Because local indexes reflect the underlying structure of the table, partitioning is maintained automatically when table partitions and subpartitions are affected by maintenance activity. Therefore, partition maintenance on local indexes is less necessary and there are fewer options.

ALTER INDEX Maintenance Operations for Index Partitions

ALTER INDEX Maintenance Operations for Index Partitions

Updating Indexes Automatically

Before discussing the individual maintenance operations for partitioned tables and indexes, it is important to discuss the effects of the UPDATE INDEXES clause that can be specified in the ALTER TABLE statement.

By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) the corresponding indexes or index partitions. You must then rebuild the entire index or, in the case of a global index, each of its partitions. The database lets you override this default behavior if you specify UPDATE INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells the database to update the index at the time it executes the maintenance operation DDL statement. This provides the following benefits:

  • The index is updated in conjunction with the base table operation. You are not required to later and independently rebuild the index.
  • The index is more highly available, because it does not get marked UNUSABLE. The index remains available even while the partition DDL is executing and it can be used to access unaffected partitions in the table.
  • You need not look up the names of all invalid indexes to rebuild them. Optional clauses for local indexes let you specify physical and storage characteristics for updated local indexes and their partitions.
  • You can specify physical attributes, tablespace storage, and logging for each partition of each local index. Alternatively, you can specify only the PARTITION keyword and let the database update the as follows
  • For operations on a single table partition (such as MOVE PARTITION and SPLIT PARTITION), the corresponding index partition inherits the attributes of the affected index partition. The database does not generate names for new index partitions, so any new index partitions resulting from this operation inherit their names from the corresponding new table partition.
  • For MERGE PARTITION operations, the resulting local index partition inherits its name from the resulting table partition and inherits its attributes from the local index.
  • For a composite-partitioned index, you can specify tablespace storage for each subpartition.

The following operations support the UPDATE INDEXES clause:

  • ADD PARTITION | SUBPARTITION
  • COALESCE PARTITION | SUBPARTITION
  • DROP PARTITION | SUBPARTITION
  • EXCHANGE PARTITION | SUBPARTITION
  • MERGE PARTITION | SUBPARTITION
  • MOVE PARTITION | SUBPARTITION
  • SPLIT PARTITION | SUBPARTITION
  • TRUNCATE PARTITION | SUBPARTITION

SKIP_UNUSABLE_INDEXES Initialization Parameter

The SKIP_UNUSABLE_INDEXES, which in earlier releases was a session parameter, is now an initialization parameter with a default value of TRUE. This setting disables error reporting of indexes and index partitions marked UNUSABLE. If you do not want the database to choose an alternative execution plan to avoid the unusable elements, you should set this parameter to FALSE.

Considerations when Updating Indexes Automatically

The following performance implications are worth noting when you specify UPDATE INDEXES:

  • The partition DDL statement takes longer to execute, because indexes that were previously marked UNUSABLE are updated. However, you must compare this increase with the time it takes to execute DDL without updating indexes, andthen rebuild all indexes. A rule of thumb is that it is faster to update indexes if the size of the partition is less that 5% of the size of the table.
  • The DROP, TRUNCATE, and EXCHANGE operations are no longer fast operations. Again, you must compare the time it takes to do the DDL and then rebuild all indexes.
  • When you update a table with a global index:
  • The index is updated in place. The updates to the index are logged, and redo and undo records are generated. In contrast, if you rebuild an entire global index, you can do so in NOLOGGING mode.
  • Rebuilding the entire index manually creates a more efficient index, because it is more compact with space better utilized.

Adding Partitions

This section describes how to add new partitions to a partitioned table and explains why partitions cannot be specifically added to most partitioned indexes.

Adding a Partition to a Range-Partitioned Table

Use the ALTER TABLE ... ADD PARTITION statement to add a new partition to the "high" end (the point after the last existing partition). To add a partition at the beginning or in the middle of a table, use the SPLIT PARTITION clause. For example, consider the table, sales, which contains data for the current month in addition to the previous 12 months. On January 1, 1999, you add a partition for January, which is stored in tablespace tsx.


Local and global indexes associated with the range-partitioned table remain usable.

Adding a Partition to a Hash-Partitioned Table

When you add a partition to a hash-partitioned table, the database populates the new partition with rows rehashed from an existing partition (selected by the database) as determined by the hash function.

The following statements show two ways of adding a hash partition to table scubagear. Choosing the first statement adds a new hash partition whose partition name is system generated, and which is placed in the table default tablespace. The second statement also adds a new hash partition, but that partition is explicitly named p _named and is created in tablespace gear5.


Indexes may be marked UNUSABLE as explained in the following table:

Indexes may be marked UNUSABLE as explained

Adding a Partition to a List-Partitioned Table

The following statement illustrates adding a new partition to a list-partitioned table. In this example physical attributes and NOLOGGING are specified for the partition being added.


Any value in the set of literal values that describe the partition being added must not exist in any of the other partitions of the table. You cannot add a partition to a list-partitioned table that has a default partition, but you can split the default partition. By doing so, you effectively create a new partition defined by the values that you specify, and a second partition that remains the default partition. Local and global indexes associated with the list-partitioned table remain usable.

Table Type Index Behavior

Regular (Heap) Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

  • The local indexes for the new partition, and for the existing partition from which rows were redistributed, are marked UNUSABLE and must be rebuilt.
  • All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt. Index-organized _ For local indexes, the behavior is the same as for heap tables.
  • All global indexes remain usable.

Adding Partitions to a Range-Hash Composite-Partitioned Table

Partitions can be added at both the range partition level and the hash subpartition level.

Adding a Partition to a Range-Hash Composite-Partitioned Table Adding a new range partition to a range-hash partitioned table is as described previously in "Adding a Partition to a Range-Partitioned Table". However, you can specify a SUBPARTITIONS clause that lets you add a specified number of subpartitions, or a SUBPARTITION clause for naming specific subpartitions. If no SUBPARTITIONS or SUBPARTITION clause is specified, the partition inherits table level defaults for subpartitions.

This example adds a range partition q1_2000 to table sales, which will be populated with data for the first quarter of the year 2000. There are eight subpartitions stored in tablespace tbs5. The subpartitions cannot be set explicitly to use table compression. Subpartitions inherit the compression attribute from the partition level and are stored in a compressed form in this example:


Adding a Subpartition to a Range-Hash Partitioned Table You use the MODIFY PARTITION ... ADD SUBPARTITION clause of the ALTER TABLE statement to add a hash subpartition to a range-hash partitioned table. The newly added subpartition is populated with rows rehashed from other subpartitions of the same partition as determined by the hash function.

In the following example, a new hash subpartition us_loc5, stored in tablespace us1, is added to range partition locations_us in table diving.


Index subpartitions corresponding to the added and rehashed subpartitions must be rebuilt unless you specify UPDATE INDEXES.

Adding Partitions to a Range-List Partitioned Table

Partitions can be added at both the range partition level and the list subpartition level.

Adding a Partition to a Range-List Partitioned Table Adding a new range partition to a range- list partitioned table is as described previously in "Adding a Partition to a Range- Partitioned Table". However, you can specify SUBPARTITION clauses for naming and providing value lists for the subpartitions. If no SUBPARTITION clauses are specified, then the partition inherits the subpartition template. If there is no subpartition template, then a single default subpartition is created.

This following statement statements adds a new partition to the quarterly_ regional _sales table that is partitioned by the range- list method. Some new physical attributes are specified for this new partition while table-level defaults are inherited for those that are not specified.


Adding a Subpartition to a Range-List Partitioned Table You use the MODIFY PARTITION ... ADD SUBPARTITION clause of the ALTER TABLE statement to add a list subpartition to a range-list partitioned table. The following statement adds a new subpartition to the existing set of subpartitions in range-list partitioned table quarterly _regional _sales. The new subpartition is created in tablespace ts2.


Adding Index Partitions

You cannot explicitly add a partition to a local index. Instead, a new partition is added to a local index only when you add a partition to the underlying table. Specifically, when there is a local index defined on a table and you issue the ALTER TABLE statement to add a partition, a matching partition is also added to the local index. The database assigns names and default physical storage attributes to the new index partitions, but you can rename or alter them after the ADD PARTITION operation is complete. You can effectively specify a new tablespace for an index partition in an ADD PARTITION operation by first modifying the default attributes for the index. For example, assume that a local index, q1 _sales _by _region _locix, was created for list partitioned table q1_ sales _by _region. If before adding the new partition q1_nonmainland, as shown in "Adding a Partition to a List-Partitioned Table”, you had issued the following statement, then the corresponding index partition would be created in tablespace tbs_4.


Otherwise, it would be necessary for you to use the following statement to move the index partition to tbs_4 after adding it:


You can add a partition to a hash- partitioned global index using the ADD PARTITION syntax of ALTER INDEX. The database adds hash partitions and populates them with index entries rehashed from an existing hash partition of the index, as determined by the hash function. The following statement adds a partition to the index hgidx shown in "Creating a Hash-Partitioned Global Index"

ALTER INDEX hgidx ADD PARTITION p5;

You cannot add a partition to a range-partitioned global index, because the highest partition always has a partition bound of MAXVALUE. If you want to add a new highest partition, use the ALTER INDEX ... SPLIT PARTITION statement.

Coalescing Partitions

Coalescing partitions is a way of reducing the number of partitions in a hash- partitioned table or index, or the number of subpartitions in a range- hash partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by the database, and is dropped after its contents have been redistributed. Index partitions may be marked UNUSABLE as explained in the following table:

Coalescing Partitions

Coalescing a Partition in a Hash-Partitioned Table

The ALTER TABLE ... COALESCE PARTITION statement is used to coalesce a partition in a hash-partitioned table. The following statement reduces by one the number of partitions in a table by coalescing a partition.


Coalescing a Subpartition in a Range- Hash Partitioned Table

The following statement distributes the contents of a subpartition of partition us _locations into one or more remaining subpartitions (determined by the hash function) of the same partition. Basically, this operation is the inverse of the MODIFY PARTITION ... ADD SUBPARTITION clause discussed in "Adding a Subpartition to a Range -Hash Partitioned Table".


Coalescing Hash-partitioned Global Indexes

You can instruct the database to reduce by one the number of index partitions in a hash-partitioned global index using the COALESCE PARTITION clause of ALTER INDEX. The database selects the partition to coalesce based on the requirements of the hash partition. The following statement reduces by one the number of partitions in the hgidx index, created in "Creating a Hash-Partitioned Global Index":

ALTER INDEX hgidx COALESCE PARTITION;

Table Type Index Behavior

Regular (Heap) Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

  • Any local index partition corresponding to the selected partition is also dropped. Local index partitions corresponding to the one or more absorbing partitions are marked UNUSABLE and must be rebuilt.
  • All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt. Index-organized _ Some local indexes are marked UNUSABLE as noted for heap indexes.
  • All global indexes remain usable.

    Dropping Partitions

    You can drop partitions from range, list, or composite range-list partitioned tables. For hash-partitioned tables, or hash subpartitions of range- hash partitioned tables, you must perform a coalesce operation instead.

    Dropping a Table Partition

    Use one of the following statements to drop a table partition or subpartition:

  • ALTER TABLE ... DROP PARTITION to drop a table partition
  • ALTER TABLE ... DROP SUBPARTITION to drop a subpartition of a range-list partitioned table

    If you want to preserve the data in the partition, use the MERGE PARTITION statement instead of the DROP PARTITION statement. If local indexes are defined for the table, this statement also drops the matching partition or subpartitions from the local index. All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE unless either of the following are true:

  • You specify UPDATE INDEXES (cannot be specified for index-organized tables)
  • The partition being dropped or its subpartitions are empty

The following sections contain some scenarios for dropping table partitions.

Dropping 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, use one of the following methods to drop the table partition.

Method 1
Leave the global indexes in place during the ALTER TABLE ... DROP PARTITION statement. Afterward, you must rebuild any global indexes (whether partitioned or not) because the index (or index partitions) will have been marked UNUSABLE. The following statements provide an example of dropping partition dec98 from the sales table, then rebuilding its global nonpartitioned index.


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 write a separate REBUILD statement for each partition in the index. The following statements rebuild the index partitions jan99 _ix, feb99 _ix, mar99_ix, ..., dec99_ix.


This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table.

Method 2

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, and also fires triggers and generates redo and undo logs. For example, to drop the first partition, which has a partition bound of 10000, issue the following statements:


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

Specify UPDATE INDEXES in the ALTER TABLE statement. Doing so causes the global index to be updated at the time the partition is dropped.


Dropping a Partition Containing Data and Referential Integrity Constraints If a partition contains data and the table has referential integrity constraints, choose either of the following methods to drop the table partition. This table has a local index only, so it is not necessary to rebuild any indexes.

Method 1
Disable the integrity constraints, issue the ALTER TABLE ... DROP PARTITION statement, then enable the integrity constraints:


This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table.

Method 2

Issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE ... DROP PARTITION statement. The DELETE statement enforces referential integrity constraints, and also fires triggers and generates redo and undo log.


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.

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, you can explicitly drop it by issuing the ALTER INDEX ... DROP PARTITION statement. But, if a global index partition contains data, 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:


Exchanging Partitions

You can convert a partition (or subpartition) into a nonpartitioned table, and a nonpartitioned table into a partition (or subpartition) of a partitioned table by exchanging their data segments. You can also convert a hash-partitioned table into a partition of a range -hash partitioned table, or convert the partition of the range-hash partitioned table into a hash-partitioned table. Similarly, you can convert a list-partitioned table into a partition of a range-list partitioned table, or convert the partition of the range- list partitioned table into a list-partitioned table.

Exchanging table partitions is most useful when you have an application using nonpartitioned tables that you want to convert to partitions of a partitioned table. For example, in data warehousing environments exchanging partitions facilitates high- speed data loading of new, incremental data into an already existing partitioned table. Generically, OLTP as well as data warehousing environments benefit from exchanging old data partitions out of a partitioned table. The data is purged from the partitioned table without actually being deleted and can be archived separately afterwards.

When you exchange partitions, logging attributes are preserved. You can optionally specify if local indexes are also to be exchanged (INCLUDING INDEXES clause), and if rows are to be validated for proper mapping (WITH VALIDATION clause). Unless you specify UPDATE INDEXES (this cannot be specified for index -organized tables), the database marks UNUSABLE the global indexes, or all global index partitions, on the table whose partition is being exchanged. Global indexes, or global index partitions, on the table being exchanged remain invalidated.

Exchanging a Range, Hash, or List Partition

To exchange a partition of a range, hash, or list-partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE ... EXCHANGE PARTITION statement. An example of converting a partition into a nonpartitioned table follows. In this example, table stocks can be range, hash, or list partitioned.

ALTER TABLE stocks EXCHANGE PARTITION p3 WITH TABLE stock_table_3;

Exchanging a Hash-Partitioned Table with a Range-Hash Partition

In this example, you are exchanging a whole hash -partitioned table, with all of its partitions, with the range partition of a range-hash partitioned table and all of its hash subpartitions. This is illustrated in the following example. First, create a hash- partitioned table:


Populate the table, then create a range -hash partitioned table as shown:


It is important that the partitioning key in table t1 is the same as the subpartitioning key in table t2.

Exchanging a Subpartition of a Range- Hash Partitioned Table

Use the ALTER TABLE ... EXCHANGE PARTITION statement to convert a hash subpartition of a range- hash partitioned table into a nonpartitioned table, or the reverse. The following example converts the subpartition q3 _1999 _s1 of table sales into the nonpartitioned table q3 _1999. Local index partitions are exchanged with corresponding indexes on q3 _1999.


Exchanging a List-Partitioned Table with a Range-List Partition

The semantics of the ALTER TABLE ... EXCHANGE PARTITION statement are the same as described previously in "Exchanging a Hash-Partitioned Table with a Range- Hash Partition". In the example shown there, the syntax of the CREATE TABLE statements would only need to be modified to create a list -partitioned table and a range-list partitioned table, respectively. The actions involved remain the same.

Exchanging a Subpartition of a Range-List Partitioned Table
The semantics of the ALTER TABLE ... EXCHANGE SUBPARTITION are the same as described previously in "Exchanging a Subpartition of a Range-Hash Partitioned Table".

Merging Partitions

Use the ALTER TABLE ... MERGE PARTITION statement to merge the contents of two partitions into one partition. The two original partitions are dropped, as are any corresponding local indexes.

You cannot use this statement for a hash- partitioned table or for hash subpartitions of a range-hash partitioned table.

If the involved partitions or subpartitions contain data, indexes may be marked UNUSABLE as explained in the following table:

Merging Partitions

Merging Range Partitions

You are allowed to merge the contents of two adjacent range partitions into one partition. Nonadjacent range partitions cannot be merged. The resulting partition inherits the higher upper bound of the two merged partitions. One reason for merging range partitions is to keep historical data online in larger partitions. For example, you can have daily partitions, with the oldest partition rolled up into weekly partitions, which can then be rolled up into monthly partitions, and so on.

The following scripts create an example of merging range partitions. First, create a partitioned table and create local indexes.

-- Create a Table with four partitions each on its own tablespace
-- Partitioned by range on the data column.
--


Next, merge partitions.
-- Merge the first two partitions
--
ALTER TABLE four_seasons
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two
UPDATE INDEXES;

If you omit the UPDATE INDEXES clause from the preceding statement, then you must rebuild the local index for the affected partition.

-- Rebuild index for quarter_two, which has been marked unusable -- because it has not had all of the data from Q1 added to it. -- Rebuilding the index will correct this. Merging List Partitions

When you merge list partitions, the partitions being merged can be any two partitions. They do not need to be adjacent, as for range partitions, since list partitioning does not assume any order for partitions. The resulting partition consists of all of the data from the original two partitions. If you merge a default list partition with any other partition, the resulting partition will be the default partition.

The statement below merges two partitions of a table partitioned using the list method into a partition that inherits all of its attributes from the table- level default attributes, except for PCTFREE and MAXEXTENTS, which are specified in the statement.


The value lists for the two original partitions were specified as:


The resulting sales _west partition value list comprises the set that represents the union of these two partition value lists, or specifically:

('SD','WI','OK','TX')

Merging Range-Hash Partitions

When you merge range -hash partitions, the subpartitions are rehashed into the number of subpartitions specified by SUBPARTITIONS n or the SUBPARTITION clause. If neither is included, table- level defaults are used.

Note that the inheritance of properties is different when a range- hash partition is split, as opposed to when two range-hash partitions are merged. When a partition is split, the new partitions can inherit properties from the original partition since there is only one parent. However, when partitions are merged, properties must be inherited from table level defaults because there are two parents and the new partition cannot inherit from either at the expense of the other.

The following example merges two range -hash partitions:


Merging Range-List Partitions

Partitions can be merged at the range partition level and subpartitions can be merged at the list subpartition level.

Merging Partitions in a Range-List Partitioned Table Merging range partitions in a range- list partitioned table is as described previously in "Merging Range Partitions". However, when you merge two range- list partitions, the resulting new partition inherits the subpartition descriptions from the subpartition template, if one exists. If no subpartition template exists, then a single default subpartition is created for the new partition. This following statement merges two partitions in the range- list partitioned stripe _regional _sales table. A subpartition template exists for the table.


Some new physical attributes are specified for this new partition while table- level defaults are inherited for those that are not specified. The new resulting partition q1 _q2 _1999 inherits the high- value bound of the partition q2 _1999 and the subpartition value -list descriptions from the subpartition template description of the table.

The data in the resulting partitions consists of data from both the partitions. However, there may be cases where the database returns an error. This can occur because data may map out of the new partition when both of the following conditions exist:

  • Some literal values of the merged subpartitions were not included in the subpartition template
  • The subpartition template does not contain a default partition definition.

This error condition can be eliminated by always specifying a default partition in the default subpartition template.

Merging Subpartitions in a Range -List Partitioned Table You can merge the contents of any two arbitrary list subpartitions belonging to the same range partition. The resulting subpartition value-list descriptor includes all of the literal values in the value lists for the partitions being merged.

The following statement merges two subpartitions of a table partitioned using range-list method into a new subpartition located in tablespace ts4:


The value lists for the original two partitions were:

  • Subpartition q1_1999_northwest was described as ('WA','OR')
  • Subpartition q1_1999_southwest was described as ('AZ','NM','UT')

The resulting subpartition value list comprises the set that represents the union of these two subpartition value lists:

  • Subpartition q1_1999_west has a value list described as
('WA','OR','AZ','NM','UT')

The tablespace in which the resulting subpartition is located and the subpartition attributes are determined by the partition-level default attributes, except for those specified explicitly. If any of the existing subpartition names are being reused, then the new subpartition inherits the subpartition attributes of the subpartition whose name is being reused.

Modifying Default Attributes

You can modify the default attributes of a table, or for a partition of a composite partitioned table. When you modify default attributes, the new attributes affect only future partitions, or subpartitions, that are created. The default values can still be specifically overridden when creating a new partition or subpartition.

Modifying Default Attributes of a Table

You modify the default attributes that will be inherited for range, list, or hash partitions using the MODIFY DEFAULT ATTRIBUTES clause of ALTER TABLE. The following example changes the default value of PCTFREE in table emp for any new partitions that are created.


For hash-partitioned tables, only the TABLESPACE attribute can be modified.

Modifying Default Attributes of a Partition

To modify the default attributes inherited when creating subpartitions, use the ALTER TABLE ... MODIFY DEFAULT ATTRIBUTES FOR PARTITION. The following statement modifies the TABLESPACE in which future subpartitions of partition p1 in range-hash partitioned table emp will reside.


Since all subpartitions of a range-hash partitioned table must share the same attributes, except TABLESPACE, it is the only attribute that can be changed.

Modifying Default Attributes of Index Partitions

In similar fashion to table partitions, you can alter the default attributes that will be inherited by partitions of a range-partitioned global index, or local index partitions of partitioned tables. For this you use the ALTER INDEX ... MODIFY DEFAULT ATTRIBUTES statement. Use the ALTER INDEX ... MODIFY DEFAULT ATTRIBUTES FOR PARTITION statement if you are altering default attributes to be inherited by subpartitions of a composite partitioned table.

Modifying Real Attributes of Partitions

It is possible to modify attributes of an existing partition of a table or index. You cannot change the TABLESPACE attribute. Use ALTER TABLESPACE ... MOVE PARTITION/ SUBPARTITION to move a partition or subpartition to a new tablespace.

Modifying Real Attributes for a Range or List Partition

Use the ALTER TABLE ... MODIFY PARTITION statement to modify existing attributes of a range partition or list partition. You can modify segment attributes (except TABLESPACE), or you can allocate and deallocate extents, mark local index partitions UNUSABLE, or rebuild local indexes that have been marked UNUSABLE. If this is a range partition of a range hash partitioned table, note the following:

  • If you allocate or deallocate an extent, this action is performed for every subpartition of the specified partition.
  • Likewise, changing any other attributes results in corresponding changes to those attributes of all the subpartitions for that partition. The partition level default attributes are changed as well. To avoid changing attributes of existing subpartitions, use the FOR PARTITION clause of the MODIFY DEFAULT ATTRIBUTES statement.

The following are some examples of modifying the real attributes of a partition. This example modifies the MAXEXTENTS storage attribute for the range partition sales _q1 of table sales:


All of the local index subpartitions of partition ts1 in range-hash partitioned table scubagear are marked UNUSABLE in the following example:


Modifying Real Attributes for a Hash Partition

You also use the ALTER TABLE ... MODIFY PARTITION statement to modify attributes of a hash partition. However, since the physical attributes of individual hash partitions must all be the same (except for TABLESPACE), you are restricted to:

  • Allocating a new extent
  • Deallocating an unused extent
  • Marking a local index subpartition UNUSABLE
  • Rebuilding local index subpartitions that are marked UNUSABLE

The following example rebuilds any unusable local index partitions associated with hash partition P1 of table dept:


Modifying Real Attributes of a Subpartition

With the MODIFY SUBPARTITION clause of ALTER TABLE you can perform the same actions as listed previously for partitions, but at the specific composite partitioned table subpartition level. For example:


Modifying Real Attributes of Index Partitions

The MODIFY PARTITION clause of ALTER INDEX lets you modify the real attributes of an index partition or its subpartitions. The rules are very similar to those for table partitions, but unlike the MODIFY PARTITION clause for ALTER INDEX, there is no subclause to rebuild an unusable index partition, but there is a subclause to coalesce an index partition or its subpartitions. In this context, coalesce means to merge index blocks where possible to free them for reuse. You can also allocate or deallocate storage for a subpartition of a local index, or mark it UNUSABLE, using the MODIFY PARTITION clause.

Modifying List Partitions: Adding Values

List partitioning allows you the option of adding literal values from the defining value list.

Adding Values for a List Partition

Use the MODIFY PARTITION ... ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global index, or global or local index partitions, remain usable. The following statement adds a new set of state codes ('OK', 'KS') to an existing partition list.


The existence of a default partition can have a performance impact when adding values to other partitions. This is because in order to add values to a list partition, the database must check that the values being added do not already exist in the default partition. If any of the values do exist in the default partition, an error is raised. You cannot add values to a default list partition.

Adding Values for a List Subpartition

This operation is essentially the same as described for "Modifying List Partitions: Adding Values", however, you use a MODIFY SUBPARTITION clause instead of the MODIFY PARTITION clause. For example, to extend the range of literal values in the value list for subpartition q1_1999_southeast use the following statement:


Literal values being added must not have been included in any other subpartition value list within the owning partition. However, they can be duplicates of literal values in the subpartition value lists of other partitions within the table.

Modifying List Partitions: Dropping Values

List partitioning allows you the option of dropping literal values from the defining value list.

Dropping Values from a List Partition

Use the MODIFY PARTITION ... DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that correspond to the set of values being dropped. If any such rows are found then the database returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values. The partition value list for any corresponding local index partition reflects the new value list, and any global index, or global or local index partitions, remain usable.

The statement below drops a set of state codes ('OK' and 'KS') from an existing partition value list.


You cannot drop values from a default list partition.

Dropping Values from a List Subpartition

This operation is essentially the same as described for "Modifying List Partitions: Dropping Values", however, you use a MODIFY SUBPARTITION clause instead of the MODIFY PARTITION clause. For example, to remove a set of literal values in the value list for subpartition q1 _1999 _southeast use the following statement:


Modifying a Subpartition Template

You can modify a subpartition template of a composite partitioned table by replacing it with a new subpartition template. Any subsequent operations that use the subpartition template (such as ADD PARTITION or MERGE PARTITIONS) will now use the new subpartition template. Existing subpartitions remain unchanged. Use the ALTER TABLE ... SET SUBPARTITION TEMPLATE statement to specify a new subpartition template. For example:


You can drop a subpartition template by specifying an empty list:


Moving Partitions

Use the MOVE PARTITION clause of the ALTER TABLE statement to:

  • Re-cluster data and reduce fragmentation
  • Move a partition to another tablespace
  • Modify create-time attributes
  • Store the data in compressed format using table compression

Typically, you can change the physical storage attributes of a partition in a single step using an ALTER TABLE/INDEX ... MODIFY PARTITION statement. However, there are some physical attributes, such as TABLESPACE, that you cannot modify using MODIFY PARTITION. In these cases, use the MOVE PARTITION clause. Modifying some other attributes, such as table compression, affects only future storage, but not existing data. If the partition being moved contains any data, indexes may be marked UNUSABLE according to the following table:

Moving Partitions

Moving Table Partitions

Use the MOVE PARTITION clause to move a partition. For example, to move the most active partition to a tablespace that resides on its own disk (in order to balance I/O), not log the action, and compress the data, issue the following statement:


This statement always drops the old partition segment and creates a new segment, even if you do not specify a new tablespace. If you are moving a partition of a partitioned index -organized table, you can specify the MAPPING TABLE clause as part of the MOVE PARTITION clause, and the mapping table partition will be moved to the new location along with the table partition.

Table Type Index Behavior

Regular (Heap) Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

  • The matching partition in each local index is marked UNUSABLE. You must rebuild these index partitions after issuing MOVE PARTITION.
  • Any global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE. Index -organized Any local or global indexes defined for the partition being moved remain usable because they are primary-key based logical rowids. However, the guess information for these rowids becomes incorrect.

Moving Subpartitions

The following statement shows how to move data in a subpartition of a table. In this example, a PARALLEL clause has also been specified.


Moving Index Partitions

The ALTER TABLE ... MOVE PARTITION statement for regular tables, marks all partitions of a global index UNUSABLE. You can rebuild the entire index by rebuilding each partition individually using the ALTER INDEX ... REBUILD PARTITION statement. You can perform these rebuilds concurrently.

You can also simply drop the index and re-create it.

Rebuilding Index Partitions
Some reasons for rebuilding index partitions include:

  • To recover space and improve performance
  • To repair a damaged index partition caused by media failure
  • To rebuild a local index partition after loading the underlying table partition with SQL*Loader or an import utility
  • To rebuild index partitions that have been marked UNUSABLE
  • To enable key compression for B-tree indexes

The following sections discuss your options for rebuilding index partitions and subpartitions.

Rebuilding Global Index Partitions

You can rebuild global index partitions in two ways:

  • Rebuild each partition by issuing the ALTER INDEX ... REBUILD PARTITION statement (you can run the rebuilds concurrently).
  • Drop the entire global index and re- create it. This method is more efficient because the table is scanned only once.

For most maintenance operations on partitioned tables with indexes, you can optionally avoid the need to rebuild the index by specifying UPDATE INDEXES on your DDL statement.

Rebuilding Local Index Partitions

Rebuild local indexes using either ALTER INDEX or ALTER TABLE as follows:

  • ALTER INDEX ... REBUILD PARTITION/SUBPARTITION

    This statement rebuilds an index partition or subpartition unconditionally.

  • ALTER TABLE ... MODIFY PARTITION/SUBPARTITION ... REBUILD UNUSABLE LOCAL INDEXES

    This statement finds all of the unusable indexes for the given table partition or subpartition and rebuilds them. It only rebuilds an index partition if it has been marked UNUSABLE.

Using ALTER INDEX to Rebuild a Partition The ALTER INDEX ... REBUILD PARTITION statement rebuilds one partition of an index. It cannot be used for composite-partitioned tables. Only real physical segments can be rebuilt with this command. When you re- create the index, you can also choose to move the partition to a new tablespace or change attributes.

For composite -partitioned tables, use ALTER INDEX ... REBUILD SUBPARTITION to rebuild a subpartition of an index. You can move the subpartition to another tablespace or specify a parallel clause. The following statement rebuilds a subpartition of a local index on a table and moves the index subpartition is another tablespace.


Using ALTER TABLE to Rebuild an Index Partition The REBUILD UNUSABLE LOCAL INDEXES clause of ALTER TABLE ... MODIFY PARTITION does not allow you to specify any new attributes for the rebuilt index partition. The following example finds and rebuilds any unusable local index partitions for table scubagear, partition p1.


There is a corresponding ALTER TABLE ... MODIFY SUBPARTITION clause for rebuilding unusable local index subpartitions.

Renaming Partitions

It is possible to rename partitions and subpartitions of both tables and indexes. One reason for renaming a partition might be to assign a meaningful name, as opposed to a default system name that was assigned to the partition in another maintenance operation.

Renaming a Table Partition

Rename a range, hash, or list partition, using the ALTER TABLE ... RENAME PARTITION statement. For example:
ALTER TABLE scubagear RENAME PARTITION sys_p636 TO tanks;

Renaming a Table Subpartition

Likewise, you can assign new names to subpartitions of a table. In this case you would use the ALTER TABLE ... RENAME PARTITION syntax.

Renaming Index Partitions

Index partitions and subpartitions can be renamed in similar fashion, but the ALTER INDEX syntax is used.

Renaming an Index Partition Use the ALTER INDEX ... RENAME PARTITION statement to rename an index partition.

Renaming an Index Subpartition This next statement simply shows how to rename a subpartition that has a system generated name that was a consequence of adding a partition to an underlying table:

ALTER INDEX scuba RENAME SUBPARTITION sys_subp3254 TO bcd_types;

Splitting Partitions

The SPLIT PARTITION clause of the ALTER TABLE or ALTER INDEX statement is used to redistribute the contents of a partition into two new partitions. Consider doing this when a partition becomes too large and causes backup, recovery, or maintenance operations to take a long time to complete. You can also use the SPLIT PARTITION clause to redistribute the I/O load. This clause cannot be used for hash partitions or subpartitions. If the partition you are splitting contains any data, indexes may be marked UNUSABLE as explained in the following table:

Splitting Partitions

Splitting a Partition of a Range- Partitioned Table

You split a range partition using the ALTER TABLE ... SPLIT PARTITION statement. You specify a value of the partitioning key column within the range of the partition at which to split the partition. The first of the resulting two new partitions includes all rows in the original partition whose partitioning key column values map lower that the specified value. The second partition contains all rows whose partitioning

key column values map greater than or equal to the specified value. You can optionally specify new attributes for the two partitions resulting from the split. If there are local indexes defined on the table, this statement also splits the matching partition in each local index.

In the following example fee_katy is a partition in the table vet_cats, which has a local index, jaf1. There is also a global index, vet on the table. vet contains two partitions, vet_parta, and vet_partb. To split the partition fee_katy, and rebuild the index partitions, issue the following statements:


Table Type Index Behavior

Regular (Heap) Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

  • The database marks UNUSABLE the new partitions (there are two) in each local index.
  • Any global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.

Index-organized _ The database marks UNUSABLE the new partitions (there are two) in each local index.

  • All global indexes remain usable.

Splitting a Partition of a List-Partitioned Table

You split a list partition using the ALTER TABLE ... SPLIT PARTITION statement. The SPLIT PARTITION clause lets you specify a value list of literal values that define a partition into which rows with corresponding partitioning key values are inserted. The remaining rows of the original partition are inserted into a second partition whole value list is the remaining values from the original partition.

You can optionally specify new attributes for the two partitions resulting from the split. The following statement splits the partition region_east into two partitions:


The literal-value list for the original region_east partition was specified as:

PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')

The two new partitions are:

  • region_east_1 with a literal-value list of ('CT','VA','MD')
  • region_east_2 inheriting the remaining literal-value list of ('NY','NH','ME','VA','PA','NJ')

The individual partitions have new physical attributes specified at the partition level. The operation is executed with parallelism of degree 5. You can split a default list partition just like you split any other list partition. This is also the only means of adding a partition to list-partitioned table that contains a default partition.

When you split the default partition, you create a new partition defined by the values that you specify, and a second partition that remains the default partition. The following example splits the default partition of sales _by _region, thereby creating a new partition.

ALTE

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics