11
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 Oracle8 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. A partitioned table or index has been divided into a number of pieces, or partitions, which have the same logical attributes. For example, all partitions in a table share the same column and constraint definitions, and all partitions in an index share the same index options. Each partition 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 can:

reduce the possibility of data corruption in multiple partitions
make it possible to back up and recover each partition independently
make it possible to control the mapping of partitions to disk drives (important for balancing I/O load)

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

Creating Partitions

This section describes how to create table and index partitions.

Creating partitions is very similar to creating a table or index: you must use the CREATE TABLE statement with the PARTITION CLAUSE. Also, you must specify the tablespace name for each partition when you have partitions in different tablespaces.

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

CREATE TABLE sales 
Splitting PartitionsSplitting Partitions   ( 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 ( 1994, 04, 01 )
      TABLESPACE tsa,
    PARTITION sales_q2 VALUES LESS THAN ( 1994, 07, 01 )
      TABLESPACE tsb,
    PARTITION sales_q3 VALUES LESS THAN ( 1994, 10, 01 )
      TABLESPACE tsc,
    PARTITION sales q4 VALUES LESS THAN ( 1995, 01, 01 )
      TABLESPACE tsd);

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

For information about partition keys, partition names, bounds, and equi-partitioned tables and indexes, see Oracle8 Concepts.

Maintaining Partitions

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

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

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

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

For general information about partitioning, see Oracle8 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 on 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, 1996, the DBA adds a partition for January:

ALTER TABLE sales 
      ADD PARTITION jan96 VALUES LESS THAN ( '960201' )
      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.

Dropping Table Partitions Containing Data and Global Indexes

If, however, the partition contains data and global indexes, 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 non-partitioned indexes unusable. Because the entire partitioned index cannot be rebuilt using one statement, sal1 in the following statement is a non-partitioned 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 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.

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 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 from 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.

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 non-partitioned indexes unusable. Because the entire partitioned index cannot be rebuilt using one statement, sal1 in the following statement is a non-partitioned 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.

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.

You can issue the ALTER INDEX SPLIT PARTITION statement to split a partition in a global index if the partition is empty.

The following statement splits the 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;

Merging Partitions

While there is no explicit MERGE statement, you can merge a partition using either the DROP PARTITION or EXCHANGE PARTITION clauses.

Merging Table Partitions

You can use either of the following strategies to merge table partitions.

If you have data in partition OSU1 and no global indexes or referential integrity constraints on the table, OH, you can merge table partition OSU1 into the next highest partition, OSU2.

To merge partition OSU1 into partition OSU2:

Export the data from OSU1.
Issue the following statement:

    ALTER TABLE OH DROP PARTITION OSU1;


Import the data from Step 1 into partition OSU2. 





Note:



The corresponding local index partitions are also merged.

Another way to merge partition OSU1 into partition OSU2:

Exchange partition OSU1 of table OH with "dummy" table COLS.
Issue the following statement:

    ALTER TABLE OH DROP PARTITION OSU1;


Insert as SELECT from the "dummy" table to move the data from OSU1 back into OSU2.

Merging Partitioned Indexes

The only way to merge partitions in a local index is to merge partitions in the underlying table.

If the index partition BUCKS is empty, you can merge global index partition BUCKS into the next highest partition, GOOSU, by issuing the following statement:

ALTER INDEX BUCKEYES DROP PARTITION BUCKS;

If the index partition BUCKS contains data, issue the following statements:

ALTER INDEX BUCKEYES DROP PARTITION BUCKS;
ALTER INDEX BUCKEYES REBUILD PARTITION GOOSU;

While the first statement marks partition GOOSU unusable, the second makes it valid again.

Exchanging Table Partitions

You can convert a partition into a non-partitioned 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 non-partitioned 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.

Merging Adjacent Table Partitions: Scenario

This scenario describes how merge two adjacent table partitions. Suppose you have to merge two partitions, FEB95 and MAR95, of the SALES table by moving the data from the FEB95 partition into the MAR95 partition.

To Merge the 2 Table Partitions:

Create a temporary table to hold the FEB95 partition data.

     CREATE TABLE sales_feb95 (...)
        TABLESPACE ts_temp STORAGE (INITIAL 2);


Exchange the FEB95 partition segment into the table SALES_FEB95.
     ALTER TABLE sales
        EXCHANGE PARTITION feb95 WITH TABLE 
         sales_feb95 WITHOUT VALIDATION;




Now the SALES_FEB95 table placeholder segment is attached to the FEB95 partition.


Drop the FEB95 partition; this frees the segment originally owned by the SALES_FEB95 table.
    ALTER TABLE sales DROP PARTITION feb95;


Move the data from the SALES_FEB95 table into the MAR95 partition via an INSERT statement.
     INSERT INTO sales PARTITION (mar95)
        SELECT * FROM sales_feb95;




Using the extended table name here is more efficient. Instead of attempting to compute the partition to which a row belongs, Oracle verifies that it belongs to the specified partition.


Drop the SALES_FEB95 table to free the segment originally associated with the FEB95 partition.
    DROP TABLE sales_feb95;


(Optional) Rename the MAR95 partition 
    ALTER TABLE sales RENAME PARTITION mar95 TO
       feb_mar95;

See Also: For more information about deferring index maintenance, see the ALTER SESSION SET SKIP_UNUSABLE_INDEXES statement in Oracle8 SQL Reference.

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_jan95
      UNION ALL
      SELECT * FROM accounts_feb95
      UNION ALL
      ...
SELECT * FROM accounts_dec95;

To Incrementally Migrate the Partition View to a Partitioned Table

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

     
     CREATE TABLE accounts_new (...)
         TABLESPACE ts_temp STORAGE (INITIAL 2)
         PARTITION BY RANGE (opening_date)
         (PARTITION jan95 VALUES LESS THAN ('950201'),
          ...
         PARTITION dec95 VALUES LESS THAN ('960101'));


Use the EXCHANGE command to migrate the tables to the corresponding partitions.
     ALTER TABLE accounts_new
        EXCHANGE PARTITION nov95 WITH TABLE 
        accounts_95 WITH VALIDATION;

     ALTER TABLE accounts_new
        EXCHANGE PARTITION dec95 WITH TABLE
        accounts_dec95 WITH VALIDATION;




So now the placeholder data segments associated with the NOV95 and DEC95 partitions have been exchanged with the data segments associated with the ACCOUNTS_NOV95 and ACCOUNTS_DEC95 tables.


Redefine the ACCOUNTS view.
     CREATE OR REPLACE VIEW accounts
           SELECT * FROM accounts_jan95
           UNION ALL
           SELECT * FROM accounts_feb_95
         UNION ALL
         ...
         UNION ALL
           SELECT * FROM accounts_new PARTITION (nov95)
         UNION ALL
           SELECT * FROM accounts_new PARTITION (dec95);


Drop the ACCOUNTS_NOV95 and ACCOUNTS_DEC95 tables, which own the placeholder segments that were originally attached to the NOV95 and DEC95 partitions.

After all the tables in the UNIONALL view are converted into partitions, drop the view and the partitioned table that was renamed as the view.
     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 Oracle8 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

An 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 an 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 an 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.

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:

Backup 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.

11 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 Oracle8 Concepts.