16 Managing Partitioned Tables and Indexes

Adding Partitions

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

Adding a Partition to a Range-Partitioned Table

Use the ALTER TABLE ... ADD PARTITION statem ent 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.

ALTE
R TABLE sales 
      ADD PARTITION jan96 VALUES LESS THAN ( '01-FEB-1999' )
      TABLESPACE tsx;

Local and global indexe s 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.

Th e 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 statemen t also adds a new hash partition, but that partition is explicitly named p_named and is created in tablespace gear 5.

ALTER TABLE scubagear ADD PARTITION;

ALTER TABLE scubagear
      ADD PARTITION p_named TABLE
SPACE gear5;

Indexes may be marked UNUSABLE as explained in the fol lowing table:

Table Typ e	Index Behavio r
Regular (Heap)	Unless you specify UPDATE INDEXES as part of the < code>ALTER TABLE statement: The local indexes for the new partition, and for the existi ng partition from which rows were redistributed, are marked UNUSABLE and must be rebuilt. A ll 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 a Partition to a List-Partitioned Table

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

ALTER TABLE q1_sales_by_region 
   ADD PARTITION q1_nonma
inland VALUES ('HI', 'PR')
      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
      NOLOGGING;

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.

Adding Partitions to a Range-Hash Composite-Partitioned Table

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

Adding a Partition to a Range-Hash Composite-Partitioned Table

Adding a new range partition to a range-has h 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 subpa rtitions.

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

ALTER TABLE sales ADD PARTITION q1_2000
      VALUES LESS TH
AN (2000, 04, 01) COMPRESS
      SUBPARTITIONS 8 STORE IN tbs5;

Ad ding a Subpartition to a Range-Hash Partitioned Table

You use the MODIF Y PARTITION ... ADD SUBPARTITION clause of the ALTER TABLE statement to add a hash subpartition to a range-hash partitioned table. The newly added subpar tition is populated with rows rehashed from other subpartitions of the same partition as determined by the hash function.

In t he following example, a new hash subpartition us_loc5, stored in tablespace us1, is added to range partitio n locations_us in table diving.

ALTER TABLE diving MODIFY PARTITION locations
_us
      ADD SUBPARTITION us_locs5 TABLESPACE us1;

Index subpartitions corresponding to the added and rehashed subpartit ions 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 t he list subpartition level.

Adding a Partition to a Range-List Partitioned Table

Adding a new range partition to a range-li st 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 n ew partition to the quarterly_regional_sales table that is partitioned by the range-list method. Some new physical attri butes are specified for this new partition while table-level defaults are inherited for those that are not specified.

ALTER TABLE quarterly_regional_sales 
   ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))
      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE ts3 NOLOGGING
         (
          SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA')
,
          SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'N
J'),
          SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),
         SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')
         );

Adding a Subpartition to a Ran ge-List Partitioned Table

You use the MODIFY PARTITION ... ADD SUBPARTITION clause of the ALTER TA BLE statement to add a list subpartition to a range-list partitioned table.

The following statement adds a new subparti tion to the existing set of subpartitions in range-list partitioned table quarterly_regional_sales. The new subpartition is created in tablespace ts2.

ALTER TABLE quarterly_regional_sales
   MODIFY PARTITION q1
_1999 
      ADD SUBPARTITION q1_1999_south
         VALUES ('AR','MS','AL') tablespace ts2;

< font face="arial, helvetica, sans-serif" color="#330099">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 pa rtition 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 PA RTITION 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 n ew partition q1_nonmainland, as shown in "Adding a Partition to a List-Partitioned Table", you h ad issued the following statement, then the corresponding index partition would be created in tablespace tbs_4.

ALTER INDEX q1_sales_by_region_locix
   MODIFY DEFAULT ATTRIBUTES TABLESPACE tbs_4;

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

ALTER INDEX q1_sales_by_region_locix 
   REBUILD PARTITION q1_nonmainland TABLESPACE tbs_4;
 

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;

<
/pre>

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 o f partitions in a hash-partitioned table or index, or the number of subpartitions in a range-hash partitioned table. When a hash part ition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specifi c partition that is coalesced is selected by the database, and is dropped after its contents have been redistributed.

Index pa rtitions may be marked UNUSABLE as explained in the following table:

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 re built. All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.
In dex-organized	Some local indexes are marked UNUSABLE as noted for heap indexes. All global indexes remain usable.

Coalescing a Partition in a Hash-Partitioned Table

The ALTER T ABLE ... 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 pa rtition.

ALTER TABLE ouu1
     COALESCE PARTITION;

Coalescing a Subpartitio n 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".

ALTER
TABLE diving MODIFY PARTITION us_locations
     COALESCE SUBPARTITION;

Coalescing Hash-partitioned Global Indexes

You can instruct the database to reduce by one the number of index partitions in a hash-partitioned global in dex 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 partition s in the hgidx index, created in "Creating a Hash-Partitioned Global Index":

ALTER INDEX hgidx COALESCE PARTITION;

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 mu st perform a coalesce operation instead.

Dropping a Table Partition

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

• ALTER TABLE ... < /a>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 parti tion 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 speci fy UPDATE INDEXES (cannot be specified for index-organized tables)

• The partiti on being dropped or its subpartitions are empty

  
  
  

  The following sections contain some scenarios for dropping table parti tions.

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

  If the partition contains data and one or mo re 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 < code>TABLE ... DROP PARTITION statement. Afterward, you must rebuild any global indexes (whether par titioned or not) because the index (or index partitions) will have been marked UNUSABLE. The following statements provid e an example of dropping partition dec98 from the sales table, then rebuilding its global nonpartitioned index.

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

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

  ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix;
  ALTER INDEX sales_area_ix
  REBUILD PARTITION feb99_ix;
  ALTER INDEX sales_area_ix REBUILD PARTITION mar99_ix;
  ...
  ALTER INDEX sales_area_ix REBUILD PARTITION dec
  99_ix;
  
  

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

  
  Method 2
  

  Iss ue 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, is sue the following statements:

  DELETE FROM sales WHERE TRANSID < 10000;
  ALTER TABLE sales DROP PARTIT
  ION dec98;
  
  

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

  
  Method 3
  

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

  ALTER TABLE sales DROP PARTITIO
  N dec98
       UPDATE INDEXES;
  

  < font face="arial, helvetica, sans-serif" color="#330099">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 method s to drop the table partition. This table has a local index only, so it is not necessary to rebuild any indexes.

  
  < font face="arial, helvetica, sans-serif">Method 1
  

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

  ALTER TABLE sales
     DISABLE CONSTRAINT dname_sales1;
  ALTER TABLE sales DROP PARTITTION dec98;
  ALTER TABLE sales
  ENABLE CONSTRAINT dname_sales1;
  
  

  This method is most appropriate for large tables where the partition being dropped contai ns 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< /code> TABLE ... DROP PARTITION statement. The DELETE statement enforces referent ial integrity constraints, and also fires triggers and generates redo and undo log.

  DELETE FROM sales W
  HERE TRANSID < 10000;
  ALTER TABLE sales DROP PARTITION dec94;
  
  

  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< /font>

  You cannot explicitly drop a partition of a local index. Instead, local index partitions are dropped only when you dro p 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 da ta, 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 s tatements:

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

  
  

  Note: You cannot drop the only partition in a table. Instead, you must drop the table.

  Note: You cannot drop the highest partit ion in a global index.

  


< !-- class="sect2" -->

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 ca n convert a list-partitioned table into a partition of a range-list partitioned table, or convert the partition of the range-list par titioned table into a list-partitioned table

Exchanging table partitions is most useful when you have an application using non partitioned tables that you want to convert to partitions of a partitioned table. For example, in data warehousing environments excha nging 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 p urged from the partitioned table without actually being deleted and can be archived separately afterwards.

When you exchange p artitions, 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 clau se).

Note: When you speci fy WITHOUT VALIDATION for the exchange partition operation, this is normally a fast operation because it in volves only data dictionary updates. However, if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation will be performed as if WITH VALIDATION were spec ified in order to maintain the integrity of the constraints. To avoid the overhead of this validation activity, issue the followi ng statement for each constraint before doing the exchange partition operation: ALTER TABLE table_n ame DISABLE CONSTRAINT constraint_name KEEP INDEX Then, enable the constraints after the exchange.

Unless you specify UPDATE INDEXES (this cannot be specified for index-organized tables), the database marks UNUSABL E the global indexes, or all global index partitions, on the table whose partition is being exchanged. Global indexes, or glob al index partitions, on the table being exchanged remain invalidated.

See Also:

• "Viewing Information About Partitioned Tables and Indexes"

• "Using Transportable Tablespaces: Sce narios" for information about transportable tablespaces

Exchanging a Range, Hash, or List Partition

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

ALTER TABLE stoc
ks
    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 tabl e, 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:

CREATE TABLE t1 (i
 NUMBER, j NUMBER)
     PARTITION BY HASH(i)
       (PARTITION p1, PARTITION p2);

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

CREATE TABLE t2 (i NUMBER, j NUMBER)
     PARTITION BY RANGE(j)
SUBPARTITION BY HASH(i)
        (PARTITION p1 VALUES LESS THAN (10)
            SUBPARTITION t2_pls1
            SUBPARTITION t2_pls2
,
         PARTITION p2 VALUES LESS THAN (20)
            SUBPARTITION t2_p2s1
            SUBPARTITION t2_p2s2));

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

To migrate the data in t1 to t2, and validate the rows, use the following statement:

ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1
     WITH VALIDATION;

Exchanging a Subpartition of a Range-Hash Partitioned Table

Us e 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 followin g 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.

ALTER T
ABLE sales EXCHANGE SUBPARTITION q3_1999_s1
      WITH TABLE q3_1999 INCLUDING INDEXES;

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 < code>CREATE TABLE statements would only need to be modified to create a list-partitioned table and a range-list partitioned ta ble, 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".