Databases Reference
In-Depth Information
9 (SELECT index_name from user_indexes
10 WHERE table_name = 'EMPLOYEES_PARTTEST')
11 ORDER BY 1,2,3;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
EMPLOYEES_PARTTEST_GI1 MANAGER_100 USABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_500 USABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_900 USABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_MAX USABLE
EMPLOYEES_PARTTEST_I1 VALID
EMPLOYEES_PART_LI1 P2010 USABLE
EMPLOYEES_PART_LI1 PI1990 USABLE
EMPLOYEES_PART_LI1 PI1995 USABLE
EMPLOYEES_PART_LI1 PI2000 USABLE
Truncating a Partition
Truncating a partition is a simple way to remove all the data for a table partition. And, for tables with
locally partitioned indexes, truncating a partition has no impact on the underlying index partitions,
including the truncated partition. However, if you have either non-partitioned indexes or globally
partitioned indexes, truncating a partition makes it impossible for Oracle to be able to know which index
entries have been affected by the truncate operation. Therefore, Oracle has no choice but to simply mark
the entire index
UNUSABLE
. For example,
SQL> ALTER TABLE employees_parttest truncate partition p1995;
Table truncated.
INDEX_NAME PARTITION_NAME 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 PI1995 USABLE
EMPLOYEES_PART_LI1 PI2000 USABLE
Moving a Partition
There are various reasons to move a table partition. You may need to move some tables or partitions to a
different tablespace, you may decide to compress a partition's data, or you may need to reorganize the
table's partition because rows have become migrated due to heavy update activity.
When doing partition move operations, you again see that locally partitioned indexes are the least
impacted. A key difference for a locally partitioned index over a partition-level truncate operation is that
the index for the partition being moved has been marked
UNUSABLE
. This needs to be done by Oracle
because by moving each row in the partition, each row now has a different
ROWID
value; thus the index
