13
Managing Partitioned Tables and Indexes

This chapter describes various aspects of managing partitioned tables and indexes, and includes the following sections:

What Are Partitioned Tables and Indexes?

Note:
Before attempting to create a partitioned table or index or perform maintenance operations on any partition, review the information about partitioning in Oracle8i Concepts.

Today's enterprises frequently run mission-critical databases containing upwards of several hundred gigabytes and, in many cases, several terabytes of data. These enterprises are challenged by the support and maintenance requirements of very large databases (VLDB), and must devise methods to meet those challenges.

One way to meet VLDB demands is to create and use partitioned tables and indexes. Partitioned tables or indexes can be divided into a number of pieces, called subpartitions, which have the same logical attributes. For example, all partitions (or subpartitions) in a table share the same column and constraint definitions, and all partitions (or subpartitions) in an index share the same index options. Each partition (or subpartition) is stored in a separate segment and can have different physical attributes (such as PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, and STORAGE).

Although you are not required to keep each table or index partition in a separate tablespace, it is to your advantage to do so. Storing partitions in separate tablespaces enables you to:

reduce the possibility of data corruption in multiple partitions
back up and recover each partition independently
control the mapping of partitions to disk drives (important for balancing I/O load)
improve manageability, availability and performance

See Also: For more detailed information on partitioning concepts and benefits, see Oracle8i Concepts.

Partitioning Methods

There are three partitioning methods:

Range Partitioning
Hash Partitioning
Composite Partitioning

This section describes how to use each of these methods.

Using the Range Partitioning Method

You can use range partitioning to map rows to partitions based on ranges of column values. Range partitioning is defined by the partitioning specification for a table or index, and by the partitioning specifications for each individual partition.

The following example shows a table of four partitions (one for each quarter's sales); a row with SALE_YEAR=1998, SALE_MONTH=8 and SALE_DAY=18 has partitioning key (1998, 8, 18), belongs in the third partition, and is stored in tablespace TSC. A row with SALE_YEAR=1998, SALE_MONTH=8 and SALE_DAY=1 has partitioning key (1998, 8, 1), and also belongs in the third partition, stored in tablespace TSC.

CREATE TABLE sales
    ( invoice_no NUMBER, 
      sale_year  INT NOT NULL,
      sale_month INT NOT NULL,
      sale_day   INT NOT NULL )
  PARTITION BY RANGE (sale_year, sale_month, sale_day)
    ( PARTITION sales_q1 VALUES LESS THAN (1998, 04, 01) 
        TABLESPACE tsa,
      PARTITION sales_q2 VALUES LESS THAN (1998, 07, 01) 
        TABLESPACE tsb,
      PARTITION sales_q3 VALUES LESS THAN (1998, 10, 01) 
        TABLESPACE tsc,
      PARTITION sales_q4 VALUES LESS THAN (1999, 01, 01) 
        TABLESPACE tsd );

Maintaining Range Partitions

The only maintenance operation to perform on partitions created using the range partitioning method is the merging of partitions. You can use the ALTER TABLE...MERGE PARTITIONS command to merge the contents of two adjacent range partitions into one partition. You might want to do this to keep historical data online in larger partitions. For example, you might want to have daily partitions, with the oldest partition rolled up into weekly partitions, which can then be rolled up into monthly partitions, and so on.

See Also: For more details about range partitioning, see Oracle8i Concepts.

For more details about CREATE TABLE...PARTITION syntax, see the Oracle8i SQL Reference.

Using the Hash Partitioning Method

Hash partitioning controls the physical placement of data across a fixed number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key. Creating and using hash partitions gives you a highly tunable method of data placement.

The following example shows how to specify all storage attributes for partitions at the table level:

CREATE TABLE scubagear(
   id NUMBER,
   name VARCHAR2 (60))
TABLESPACE ocean
STORAGE (INITIAL 19k)
   PARTITION BY HASH (id)
   PARTITIONS 4;

You can store hash partitions in specific tablespaces, as shown in the following statement:

CREATE TABLE scubagear (...)
   STORAGE (INITIAL 10k)
   PARTITION BY HASH (id) PARTITIONS 16
   STORE IN (h1to4, h4to8, h8to12, h12to16);

Or, you can name and store each hash partition in a specific tablespace:

CREATE TABLE product(...)
   STORAGE (INITIAL 10k)
   PARTITION BY HASH (id)
    (PARTITION p1 TABLESPACE h1,
     PARTITION p2 TABLESPACE h2);

You can also specify partition-level tablespaces for hash-partitioned indexes:

CREATE INDEX bcd_type ON scubagear(id) LOCAL
PARTITIONS 4 STORE IN (ix1, ix2);

CREATE INDEX bcd_type ON scubagear(id) LOCAL
(PARTITION p1 TABLESPACE ix1, PARTITION p2 TABLESPACE ix2,
 PARTITION p3 TABLESPACEix3, PARTITION p4 TABLESPACE ix4);

Maintaining Hash Partitions

All current range partition maintenance operations are supported for hash partitions, except for the following:

ALTER TABLE...SPLIT PARTITION
ALTER TABLE...DROP PARTITION
ALTER TABLE...MERGE PARTITIONS

Additionally, there are two maintenance operations specifically for partitions created using the has partitioning method:

Coalescing Hash Partitions

To remove a single hash partition and redistribute the data, use the following statement:

ALTER TABLE scubagear COALESCE PARTITION;

Note that the partition being coalesced is determined by the hash function. Also, when you coalesce a hash partition and redistribute the data, local indexes are not maintained. You can coalesce the hash partition in parallel.Local index partitions corresponding to partitions that absorbed rows must be rebuilt from existing partitions.

Adding Hash Partitions

To add a single hash partition and redistribute the data, use one of the following statements:

ALTER TABLE scubagear ADD PARTITION;
ALTER TABLE scubagear
   ADD PARTITION p3 TABLESPACE t3;

Local indexes are not maintained when you add a hash partition. You can also add the hash partition in parallel.

See Also: For detailed syntax information about the CREATE TABLE PARTITION...BY HASH and ALTER TABLE statements, see the Oracle8i SQL Reference.

For more details about hash partitioning, see Oracle8i Concepts.

Using the Composite Partitioning Method

Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.

When creating a composite partition, you specify the following:

partitioning method (range)
partitioning key
partition descriptions (including partition bounds)
subpartitioning method (hash)
subpartitioning columns
number of subpartitions per partition or descriptions of subpartitions

You may also wish to use the STORE IN clause to specify tablespaces across which each table partition's subpartitions will be spread.

The following statement creates a composite-partitioned table:

CREATE TABLE scubagear (equipno NUMBER, equipname VARCHAR(32), price NUMBER)
   PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)
   SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)
   (PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION p3 VALUES LESS THAN (MAXVALUE));

The following statement shows you can specify subpartition names and names of tablespaces in which subpartitions should be placed.

CREATE TABLE scubagear (equipno NUMBER, equipname VARCHAR(32), price NUMBER)
   PARTITION BY RANGE (equipno) SUBPARTITION BY HASH (equipname)
   SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)
   (PARTITION p1 VALUES LESS THAN (1000) PCTFREE 40,
    PARTITION p2 VALUES LESS THAN (2000) STORE IN (ts2, ts4, ts6, ts8),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
      (SUBPARTITION p3_s1 TABLESPACE ts4,
       SUBPARTITION p3_s2 TABLESPACE ts5));

Maintaining Composite Partitions

You can perform all range partition maintenance operations on a composite partitioned table or index, and modify default attributes for table partitions.

Maintaining Composite Subpartitions

This section describes how to accomplish specific subpartition maintenance operations, including:

Modifying Subpartitions (tables and indexes)
Rebuilding Subpartitions (indexes only)
Renaming Subpartitions (tables and indexes)
Exchanging Subpartitions (tables only)
Adding Subpartitions (tables only)
Coalescing Subpartitions (tables only)
Moving Subpartitions (tables only)
Truncating Subpartitions (tables only)

Modifying Subpartitions

You can mark a subpartition of a local index on a partitioned table marked unusable as follows.

ALTER INDEX scuba 
   MODIFY SUBPARTITION bcd_types UNUSABLE;

You can also allocate or deallocate storage for a subpartition of a table or index using the MODIFY SUBPARTITION clause.

Rebuilding Subpartitions

You can rebuild a subpartition to regenerate the data in an index subpartition. The following statement rebuilds a subpartition of a local index on a table:

ALTER INDEX scuba
   REBUILD SUBPARTITION bcd_types
   TABLESPACE tbs23 PARALLEL (DEGREE 2);

Note that in this example, the index is rebuilt in a different tablespace.

Renaming Subpartitions

You can assign new names to subpartitions of a table or index. The following statement shows how to assign a new name to a subpartition of a local index on a table:

ALTER INDEX scuba RENAME SUBPARTITION bcd_types TO bcd_brands;

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;

You can also assign a new name to a subpartition of a table:

ALTER TABLE diving RENAME SUBPARTITION locations_us
   TO us_monterey;

Exchanging Subpartitions

The following statement shows how to convert a subpartition of a table into a nonpartitioned table:

ALTER TABLE diving
   EXCHANGE SUBPARTITION locations_us
   WITH TABLE us_ca INCLUDING INDEXES;

Adding Subpartitions

The following statement shows how to add a subpartition to a partition of a table. The newly added subpartition is populated with rows rehashed from other subpartitions of the same partition as determined by the hash function:

ALTER TABLE diving MODIFY PARTITION locations_us
   ADD SUBPARTITION us_monterey TABLESPACE us1;

Coalescing Subpartitions

The following statement shows how to distribute contents of a subpartition (selected by the RDBMS) of the specified partition of a table into one or more remaining subpartitions (determined by the hash function) of the same partition, and then destroy the selected subpartition. Basically, this operation is the inverse of the ALTER TABLE MODIFY PARTITION ADD SUBPARTITION statement:

ALTER TABLE diving MODIFY PARTITION us_locations
   COALESCE PARTITION;

Moving Subpartitions

The following statement shows how to move data in a subpartition of a table:

ALTER TABLE scuba_gear MOVE SUBPARTITION bcd_types 
   TABLESPACE tbs23 PARALLEL (DEGREE 2);

Truncating Subpartitions

The following statement shows how to truncate data in a subpartition of a table:

ALTER TABLE diving
   TRUNCATE SUBPARTITION us_locations
      DROP STORAGE;

See Also: For more details about the syntax of statements in this section, see the Oracle8i SQL Reference.

Creating Partitions

Creating a partitioned table is very similar to creating a table or index: you must use the CREATE TABLE statement with the PARTITION by clause. Also, you must specify the tablespace name for each partition.

The following example shows a CREATE TABLE statement that contains four partitions, one for each quarter's worth of sales. A row with SALE_YEAR=1998, SALE_MONTH=7, and SALE_DAY=18 has the partitioning key (1998, 7, 18), and is in the third partition, in the tablespace TSC. A row with SALE_YEAR=1998, SALE_MONTH=7, and SALE_DAY=1 has the partitioning key (1998, 7, 1), and also is in the third partition.

CREATE TABLE sales 
   ( invoice_no NUMBER,
     sale_year  INT NOT NULL,
     sale_month INT NOT NULL,
     sale_day   INT NOT NULL )
PARTITION BY RANGE ( sale_year, sale_month, sale_day)
    ( PARTITION sales_q1 VALUES LESS THAN ( 1998, 04, 01 )
      TABLESPACE tsa,
    PARTITION sales_q2 VALUES LESS THAN ( 1998, 07, 01 )
      TABLESPACE tsb,
    PARTITION sales_q3 VALUES LESS THAN ( 1998, 10, 01 )
      TABLESPACE tsc,
    PARTITION sales q4 VALUES LESS THAN ( 1999, 01, 01 )
      TABLESPACE tsd);

See Also: For more information about the CREATE TABLE statement and PARTITION clause, see Oracle8i SQL Reference.

For information about partition keys, partition names, bounds, and equipartitioned tables and indexes, see Oracle8i Concepts.

Maintaining Partitions

This section describes how to perform the following specific partition maintenance operations:

See Also: For information about the SQL syntax for DDL statements, see Oracle8i SQL Reference.

For information about the catalog views that describe partitioned tables and indexes, and the partitions of a partitioned table or index, see Oracle8i Reference.

For information about Import, Export and partitions, see Oracle8i Utilities.

For general information about partitioning, see Oracle8i Concepts.

Moving Partitions

You can 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

Typically, you can change the physical storage attributes of a partition in a single step via a ALTER TABLE/INDEX...MODIFY PARTITION statement. However, there are some physical attributes, such as TABLESPACE, that you cannot modify via MODIFY PARTITION. In these cases you can use the MOVE PARTITION clause.

Moving Table Partitions

You can use the MOVE PARTITION clause to move a partition. For example, a DBA wishes to move the most active partition to a tablespace that resides on its own disk (in order to balance I/O). The DBA can issue the following statement:

ALTER TABLE parts MOVE PARTITION depot2
      TABLESPACE ts094 NOLOGGING;

This statement always drops the partition's old segment and creates a new segment, even if you don't specify a new tablespace.

When the partition you are moving contains data, MOVE PARTITION marks the matching partition in each local index, and all global index partitions as unusable. You must rebuild these index partitions after issuing MOVE PARTITION.

Moving Index Partitions

Some operations, such as MOVE PARTITION and DROP TABLE PARTITION, mark 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.

Adding Partitions

This section describes how to add new partitions to a partitioned table and how partitions are added to local indexes.

Adding Table Partitions

You can use the ALTER TABLE...ADD PARTITION statement to add a new partition to the "high" end (the point after the last existing partition). If you wish to add a partition at the beginning or in the middle of a table, or if the partition bound on the highest partition is MAXVALUE, you should instead use the SPLIT PARTITION statement.

When the partition bound of the highest partition is anything other than MAXVALUE, you can add a partition using the ALTER TABLE...ADD PARTITION statement.

For example, a DBA has a table, SALES, which contains data for the current month in addition to the previous 12 months. On January 1, 1999, the DBA adds a partition for January:

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

When there are local indexes defined on the table and you issue the ALTER TABLE...ADD PARTITION statement, a matching partition is also added to each local index. Since Oracle assigns names and default physical storage attributes to the new index partitions, you may wish to rename or alter them after the ADD operation is complete.

Adding Index Partitions

You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table.

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

Dropping Partitions

This section describes how to use the ALTER TABLE DROP PARTITION statement to drop table and index partitions and their data.

Dropping Table Partitions

You can use the ALTER TABLE DROP PARTITION statement to drop table partitions.

If there are local indexes defined for the table, ALTER TABLE DROP PARTITION also drops the matching partition from each local index.

Note:
You cannot drop the only partition in a table.

Dropping Table Partitions Containing Data and Global Indexes

If, however, the partition contains data and one or more global indexes are defined on the table, use either of the following methods to drop the table partition:

Leave the global indexes in place during the ALTER TABLE...DROP PARTITION statement. In this situation DROP PARTITION marks all global index partitions unusable, so you must rebuild them afterwards.

Note:
The ALTER TABLE...DROP PARTITION statement not only marks all global index partitions as unusable, it also renders all nonpartitioned indexes unusable. You cannot rebuild the entire partitioned index in a single statement. If you wish to rebuild a partitioned index, you must write a separate REBUILD statement for each partition in the partitioned index. Here, sal1 is a nonpartitioned index.

      ALTER TABLE sales DROP PARTITION dec94;
      ALTER INDEX sales_area_ix REBUILD sal1;

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

Issue the DELETE command to delete all rows from the partition before you issue the ALTER TABLE...DROP PARTITION statement. The DELETE command updates the global indexes, and also fires triggers and generates redo and undo logs.

Note:
You can substantially reduce the amount of logging by setting the NOLOGGING attribute (using ALTER TABLE...MODIFY PARTITION...NOLOGGING) for the partition before deleting all of its rows.

For example, a DBA wishes to drop the first partition, which has a partition bound of 10000. The DBA issues the following statements:

      DELETE FROM sales WHERE 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 Table Partitions 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:

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 dec94;
    ALTER TABLE sales
       ENABLE CONSTRAINT dname_sales1;
```
This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table.
Issue the DELETE command to delete all rows from the partition before you issue the ALTER TABLE...DROP PARTITION statement. The DELETE command enforces referential integrity constraints, and also fires triggers and generates redo and undo log.
```
    DELETE FROM sales WHERE 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

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.

If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. For example, a DBA wishes to drop the index partition P1 and P2 is the next highest partition. The DBA must issue the following statements:

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

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

Coalescing Partitions

You can distribute contents of a partition (selected by the RDBMS) of a table partitioned using the hash method into one or more partitions determined by the hash function, and then destroy the selected partition.

The following statement reduces by one the number of partitions in a table by coalescing its last partition:

ALTER TABLE ouu1
   COALESCE PARTITION;

Modifying Partition Default Attributes

You can modify default attributes of a partition of a local index on tables created using the composite method (or a partition of a composite table).

The following statement changes the (partition-level default) PCTFREE attribute of a partition of a local index on a partitioned table:

ALTER INDEX scuba_1
   MODIFY DEFAULT ATTRIBUTES FOR PARTITION bcd_1998
      PCTFREE 25;

Truncating Partitions

Use the ALTER TABLE...TRUNCATE PARTITION statement when you wish to remove all rows from a table partition. You cannot truncate an index partition; however, the ALTER TABLE TRUNCATE PARTITION statement truncates the matching partition in each local index.

Truncating Partitioned Tables

You can use the ALTER TABLE...TRUNCATE PARTITION statement to remove all rows from a table partition with or without reclaiming space. If there are local indexes defined for this table, ALTER TABLE...TRUNCATE PARTITION also truncates the matching partition from each local index.

Truncating Table Partitions Containing Data and Global Indexes

If, however, the partition contains data and global indexes, use either of the following methods to truncate the table partition:

Leave the global indexes in place during the ALTER TABLE TRUNCATE PARTITION statement.

Note:
The ALTER TABLE...TRUNCATE PARTITION statement not only marks all global index partitions as unusable, it also renders all nonpartitioned indexes unusable. You cannot rebuild the entire partitioned index in a single statement. If you wish to rebuild a partitioned index, you must write a separate REBUILD statement for each partition in the partitioned index. Here, sal1 is a nonpartitioned index.

     ALTER TABLE sales TRUNCATE PARTITION dec94;
     ALTER INDEX sales_area_ix REBUILD sal1;

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

Issue the DELETE command to delete all rows from the partition before you issue the ALTER TABLE...TRUNCATE PARTITION statement. The DELETE command updates the global indexes, 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 truncated contains a small percentage of the total data in the table.

Truncating Table Partitions Containing Data and Referential Integrity Constraints

If a partition contains data and has referential integrity constraints, choose either of the following methods to truncate the table partition:

Disable the integrity constraints, issue the ALTER TABLE...TRUNCATE PARTITION statement, then re-enable the integrity constraints:
```
     ALTER TABLE sales
        DISABLE CONSTRAINT dname_sales1;
     ALTER TABLE sales TRUNCATE PARTITTION dec94;
     ALTER TABLE sales
        ENABLE CONSTRAINT dname_sales1;
```
This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table.

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

Note:
You can substantially reduce the amount of logging by setting the NOLOGGING attribute (using ALTER TABLE...MODIFY PARTITION...NOLOGGING) for the partition before deleting all of its rows.

     DELETE FROM sales WHERE TRANSID < 10000;
     ALTER TABLE sales TRUNCATE PARTITION dec94;

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

Splitting Partitions

This form of ALTER TABLE/INDEX divides a partition into two partitions. You can use the SPLIT PARTITION clause when a partition becomes too large and causes backup, recovery or maintenance operations to take a long time. You can also use the SPLIT PARTITION clause to redistribute the I/O load; note that you cannot use this clause for hash partitions.

Splitting Table Partitions

You can split a table partition by issuing the ALTER TABLE...SPLIT PARTITION statement. If there are local indexes defined on the table, this statement also splits the matching partition in each local index. Because Oracle assigns system-generated names and default storage attributes to the new index partitions, you may wish to rename or alter these index partitions after splitting them.

If the partition you are splitting contains data, the ALTER TABLE...SPLIT PARTITION statement marks the matching partitions (there are two) in each local index, as well as all global index partitions, as unusable. You must rebuild these index partitions after issuing the ALTER TABLE...SPLIT PARTITION statement.

Splitting a Table Partition: Scenario

In this scenario "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, the DBA issues the following statements:

ALTER TABLE vet_cats SPLIT PARTITION 
      fee_katy at (100) INTO ( PARTITION
      fee_katy1 ..., PARTITION fee_katy2 ...);
ALTER INDEX JAF1 REBUILD PARTITION SYS_P00067;
ALTER INDEX JAF1 REBUILD PARTITION SYS_P00068;
ALTER INDEX VET REBUILD PARTITION VET_parta;
ALTER INDEX VET REBUILD PARTITION VET_partb;

Note:
You must examine the data dictionary to locate the names assigned to the new local index partitions. In this particular scenario, they are SYS_P00067 and SYS_P00068. If you wish, you can rename them. Also, unless JAF1 already contained partitions fee_katy1 and fee_katy2, names assigned to local index partitions produced by this split will match those of corresponding base table partitions.

Splitting Index Partitions

You cannot explicitly split a partition in a local index. A local index partition is split only when you split a partition in the underlying table.

The following statement splits the global index partition containing data, QUON1:

ALTER INDEX quon1 SPLIT 
    PARTITION canada AT VALUES LESS THAN ( 100 ) INTO 
    PARTITION canada1 ..., PARTITION canada2 ...);
ALTER INDEX quon1 REBUILD PARTITION canada1;
ALTER INDEX quon1 REBUILD PARTITION canada2;

You only need to rebuild if the index partition that you split was unusable.

Merging Partitions

You can merge the contents of two adjacent partitions of a range or composite partitioned table into one. The resulting partition inherits the higher upper bound of the two merged partitions.

The following statement merges two adjacent partitions of a range partitioned table:

ALTER TABLE diving
   MERGE PARTITIONS bcd1, bcd2 INTO PARTITION bcd1bcd2;

Exchanging Table Partitions

You can convert a partition into a nonpartitioned table, and a table into a partition of a partitioned table by exchanging their data (and index) segments. Exchanging table partitions is most useful when you have an application using nonpartitioned tables which you want to convert to partitions of a partitioned table. For example, you may already have partition views that you wish to migrate into partitioned tables.

Converting a Partition View into a Partitioned Table: Scenario

This scenario describes how to convert a partition view (also called "manual partition") into a partitioned table. The partition view is defined as follows:

CREATE VIEW accounts
      SELECT * FROM accounts_jan98
      UNION ALL
      SELECT * FROM accounts_feb98
      UNION ALL
      ...
SELECT * FROM accounts_dec98;

To Incrementally Migrate the Partition View to a Partitioned Table

Initially, only the two most recent partitions, ACCOUNTS_NOV98 and ACCOUNTS_DEC98, will be migrated from the view to the table by creating the partitioned table. Each partition gets a segment of 2 blocks (as a placeholder).

     
     CREATE TABLE accounts_new (...)
         TABLESPACE ts_temp STORAGE (INITIAL 2)
         PARTITION BY RANGE (opening_date)
         (PARTITION jan98 VALUES LESS THAN ('01-FEB-1998'),
          ...
         PARTITION dec98 VALUES LESS THAN ('01-FEB-1998'));

Use the EXCHANGE command to migrate the tables to the corresponding partitions.

     ALTER TABLE accounts_new
        EXCHANGE PARTITION nov98 WITH TABLE 
        accounts_nov98 WITH VALIDATION;

     ALTER TABLE accounts_new
        EXCHANGE PARTITION dec98 WITH TABLE
        accounts_dec98 WITH VALIDATION;

So now the placeholder data segments associated with the NOV98 and DEC98 partitions have been exchanged with the data segments associated with the ACCOUNTS_NOV98and ACCOUNTS_DEC98 tables.

Redefine the ACCOUNTS view.

     CREATE OR REPLACE VIEW accounts
           SELECT * FROM accounts_jan98
           UNION ALL
           SELECT * FROM accounts_feb_98
         UNION ALL
         ...
         UNION ALL
           SELECT * FROM accounts_new PARTITION (nov98)
         UNION ALL
           SELECT * FROM accounts_new PARTITION (dec98);

Drop the ACCOUNTS_NOV98 and ACCOUNTS_DEC98 tables, which own the placeholder segments that were originally attached to the NOV98 and DEC98 partitions.
After all the tables in the UNIONALL view are converted into partitions, drop the view and rename the partitioned to the name of the view being dropped.
```
     DROP VIEW accounts;
     RENAME accounts_new TO accounts;
```

See Also: For more information about the syntax and usage of the statements in this section, see Oracle8i SQL Reference.

Rebuilding Index Partitions

Some operations, such as ALTER TABLE...DROP PARTITION, mark all partitions of a global index unusable. 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 index and re-create it.

Note:
This method is more efficient because the table is scanned only once.

Moving the Time Window in a Historical Table

A historical table describes the business transactions of an enterprise over intervals of time. Historical tables can be base tables, which contain base information; for example, sales, checks, orders. Historical tables can also be rollup tables, which contain summary information derived from the base information via operations such as GROUP BY, AVERAGE, or COUNT.

The time interval in a historical table is a rolling window; DBAs periodically delete sets of rows that describe the oldest transaction, and in turn allocate space for sets of rows that describe the most recent transaction. For example, at the close of business on April 30, 1995 the DBA deletes the rows (and supporting index entries) that describe transactions from April 1994, and allocates space for the April 1995 transactions.

To Move the Time Window in a Historical Table

Now consider a specific example. You have a table, ORDER, which contains 13 months of transactions: a year of historical data in addition to orders for the current month. There is one partition for each month; the partitions are named ORDER_yymm, as are the tablespaces in which they reside.

The ORDER table contains two local indexes, ORDER_IX_ONUM, which is a local, prefixed, unique index on the order number, and ORDER_IX_SUPP, which is a local, non-prefixed index on the supplier number. The local index partitions are named with suffixes that match the underlying table. There is also a global unique index, ORDER_IX_CUST, for the customer name. ORDER_IX_CUST contains three partitions, one for each third of the alphabet. So on October 31, 1994, change the time window on ORDER as follows:

Back up the data for the oldest time interval.

     ALTER TABLESPACE ORDER_9310 BEGIN BACKUP;
     ALTER TABLESPACE ORDER_9310 END BACKUP;

Drop the partition for the oldest time interval.

     ALTER TABLE ORDER DROP PARTITION ORDER_9310;

Add the partition to the most recent time interval.

     ALTER TABLE ORDER ADD PARTITION ORDER_9411;

Drop and re-create the global indexes.

     ALTER INDEX ORDER DROP PARTITION ORDER_IX_CUST;
     ALTER INDEX REBUILD PARTITION ORDER_IX_CUST;

Quiescing Applications During a Multi-Step Maintenance Operation

Ordinarily, Oracle acquires sufficient locks to ensure that no operation (DML, DDL, utility) interferes with an individual DDL statement, such as ALTER TABLE...DROP PARTITION. However, if the partition maintenance operation requires several steps, it is the DBA's responsibility to ensure that applications (or other maintenance operations) do not interfere with the multi-step operation in progress.

For example, there are referential integrity constraints on the table ORDER, and you do not wish to disable them to drop the partition. Instead, you can replace Step 2 from the previous section with the following:

DELETE FROM ORDER WHERE ODATE < TO_DATE( 01-NOV-93 );
ALTER TABLE ORDER DROP PARTITION ORDER_9310;

You can ensure that no one inserts new rows into ORDER between the DELETE step and the DROP PARTITION steps by revoking access privileges from an APPLICATION role, which is used in all applications. You can also bring down all user-level applications during a well-defined batch window each night or weekend.

13 Managing Partitioned Tables and Indexes

What Are Partitioned Tables and Indexes? Note: Before attempting to create a partitioned table or index or perform maintenance operations on any partition, review the information about partitioning in Oracle8i Concepts.