| Lesson 8 | Modifying partitions |
| Objective | Modify characteristics of partitions. |
Modifying Table Partitions in Oracle
Up until now, we have been concentrating on creating tables and indexes with partitions. This lesson introduces some ways you can modify the partitions in a table.
ALTER TABLE
The basic syntax for changing the partitioning in a table is the ALTER TABLE command.
You can modify the number of partitions in a table using standard SQL syntax which is used to perform CRUD operations.
There are four basic modifications you can make with the ALTER TABLE command, as the following table illustrates.
| COMMANDS USED |
ADVANTAGES |
|
| Add a partition |
ALTER TABLE
ADD PARTITION partition_name
VALUES LESS THAN value storage_parameters
|
You may want to do this as the range of values for the partition key gets progressively higher. Keep in mind that you can create a partition table with only one partition in order to add partitions later with this command. You cannot add a partition to a table if the upper bound of
the partition is MAXVALUE.
|
| Move a partition |
ALTER TABLE
MOVE PARTITION tablespace
|
Moving a partition automatically creates a new segment for the partition and moves the data, even if it is in the same tablespace as the original partition. When you move a partition, all the indexes for the partition are marked as unusable and must be rebuilt.
|
| Rename a partition |
ALTER TABLE RENAME PARTITION original_partition_name TO new_partition_name |
|
| Modify a partition |
ALTER TABLE MODIFY PARTITION |
You can mark a local index as unusable, rebuild local indexes to make them usable, or to modify the storage attributes of a partition. |
These four modifications are not the only ways to alter partitions--the next two lessons detail additional ways to change existing partitions.
Database Systems
Managing Partitioned Tables
You can use the alter table command to add, drop, exchange, move, modify, rename, split, and truncate partitions. These alter table command options allow you to alter the existing partition structure, as may be required after a partitioned table has been used heavily. For example, the distribution of the CategoryName values within the partitioned table may have changed, or the maximum value may have increased.
During an insert into the partitioned table, Oracle uses the definitions of partitions to determine which partition the record should be inserted into. Thus, you can use a partitioned table as if it were a single table, and rely on Oracle to manage the internal separation of the data. One common use of partitions allows you to minimize downtime in applications. Assume you have a table that is batch-loaded and partitioned by day. When a set of data arrives that is equal to one day comes in, you create a new table to store that data. Structure the new table to look like a partition of the existing partitioned table, load the data into the new table, then index it. This avoids the performance penalties associated with having the index in place during the data load.
Next, analyze the new table. In the partitioned table, create a new partition for today’s data, then use the alter table exchange partition command to exchange that empty partition for the newly loaded table. By following this process you will be able to prevent the data load from adversely impacting the user access to the partitioned table while you load, index, and analyze it.
The next lesson shows how to drop a partition.
