For some of the more common maintenance operations, I??™ll give you some examples of how they
are used, extending some of the examples I have presented earlier in this chapter.
Maintaining Table Partitions To maintain one or more table partitions or subpartitions, you use
the alter table command just as you would on a nonpartitioned table. In Table 16-5 are the types
of partitioned table operations and the keywords you would use to perform them. The format of
the alter table command is as follows:
alter table
;
Table 16-6 contains the subpartition table operations.
CAUTION
Using the add partition clause only works if there are no existing
entries for new partitions in the default partition.
In many cases, partitioned table maintenance operations invalidate the underlying index;
while you can always rebuild the index manually, you can specify update indexes in the table
partition maintenance command. Although the table maintenance operation will take longer, the
most significant benefit of using update indexes is to keep the index available during the partition
maintenance operation.
Chapter 16: Managing Large Databases 577
Partition
Operation
Range & Composite
Range-*
Interval &
Composite
Interval-*
Hash List &
Composite
List-*
Reference
Add a partition ADD PARTITION
ADD PARTITION ADD
PARTITION
ADD
PARTITION
N/A
Coalesce a
partition
N/A N/A COALESCE
PARTITION
N/A N/A
Drop a partition DROP PARTITION DROP
PARTITION
DROP
PARTITION
N/A N/A
Exchange a
partition
EXCHANGE
PARTITION
EXCHANGE
PARTITION
EXCHANGE
PARTITION
EXCHANGE
PARTITION
EXCHANGE
PARTITION
Merge partitions MERGE PARTITIONS MERGE
PARTITIONS
N/A MERGE
PARTITIONS
N/A
Modify default
attributes
MODIFY DEFAULT
ATTRIBUTES
MODIFY
DEFAULT
ATTRIBUTES
MODIFY
DEFAULT
ATTRIBUTES
MODIFY
DEFAULT
ATTRIBUTES
MODIFY
DEFAULT
ATTRIBUTES
Modify real
attributes
MODIFY PARTITION MODIFY
PARTITION
MODIFY
PARTITION
MODIFY
PARTITION
MODIFY
PARTITION
Modify list
partitions: Add
values
N/A N/A N/A MODIFY
PARTITION
??¦ ADD
VALUES
N/A
Modify list
partitions:Drop
values
N/A N/A N/A MODIFY
PARTITION
??¦ DROP
VALUES
N/A
Move a partition MOVE PARTITION MOVE
PARTITION
MOVE
PARTITION
MOVE
PARTITION
MOVE
PARTITION
Rename a
partition
RENAME PARTITION RENAME
PARTITION
RENAME
PARTITION
RENAME
PARTITION
RENAME
PARTITION
Split a partition SPLIT PARTITION SPLIT
PARTITION
N/A SPLIT
PARTITION
N/A
Truncate a
partition
TRUNCATE
PARTITION
TRUNCATE
PARTITION
TRUNCATE
PARTITION
TRUNCATE
PARTITION
TRUNCATE
PARTITION
TABLE 16-5 Maintenance Operations for Partitioned Tables
Splitting, Adding, and Dropping Partitions In many environments, a ???rolling window???
partitioned table will contain the latest four quarters??™ worth of rows.
Pages:
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857