Databases Reference
In-Depth Information
SQL> ALTER TABLE employees_parttest DROP PARTITION p1995;
Table altered.
INDEX_NAME NULL STATUS
------------------------------ ------------------------------ --------
EMPLOYEES_PARTTEST_GI1 MANAGER_100 UNUSABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_500 UNUSABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_900 UNUSABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_MAX UNUSABLE
EMPLOYEES_PARTTEST_I1 UNUSABLE
EMPLOYEES_PART_LI1 PI1990 USABLE
EMPLOYEES_PART_LI1 PIMAX USABLE
Merging a Partition
The partition merge operation is essentially the opposite of a partition split, and like a partition split, is
performed to meet some application requirement or maintenance reasons.
The following example merges the
P1995
and
PMAX
partitions into a single
PMAX
partition. In this
scenario, all the rows for the two partitions are combined into one essentially new partition. For your
locally partitioned index, there is a matching
PMAX
index partition. Since the row makeup changed
because of the merge, the locally partitioned
PMAX
index partition has been marked
UNUSABLE
.
Because again, the globally partitioned index and non-partitioned indexes have no knowledge of the
table partition make-up, the entire indexes have been marked
UNUSABLE
.
SQL> ALTER TABLE employees_parttest MERGE PARTITIONS p1995 , pmax
2 into PARTITION pmax;
Table altered.
INDEX_NAME NULL STATUS
------------------------------ ------------------------------ --------
EMPLOYEES_PARTTEST_GI1 MANAGER_100 UNUSABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_500 UNUSABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_900 UNUSABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_MAX UNUSABLE
EMPLOYEES_PARTTEST_I1 UNUSABLE
EMPLOYEES_PART_LI1 PI1990 USABLE
EMPLOYEES_PART_LI1 PIMAX UNUSABLE
■
Tip
Most partition-level table operations will not affect any underlying indexes if the affected partition(s) are
empty.
