| |
Modify existing partition a table or index properties is possible.
You can not change the TABLESPACE attribute. Use ALTER TABLE ... MOVE PARTITION / SUBPARTITION to a partition or sub-partition to the new table space.
Modify existing range or list partition properties
Use ALTER TABLE ... MODIFY PARTITION to modify the properties of an existing partition range or list partition. You can modify segment attributes (except TABLESPACE), or you can allocate and release area, identified as the local index partitions UNUSABLE, or rebuild local indexes identified as UNUSABLE.
If this is a composite hash-partitioned table range partitioning, please note the following:
If you assign or release a zone designated for each sub-partition partition will perform the operation;
Similarly, modify any other attribute causes the relevant properties of all sub-partition the partition is modified. Partition-level default attributes will be modified. In order to avoid modifying the properties of an existing sub-district, you can use the MODIFY DEFAULT ATTRIBUTES statement FOR PARTITION clause.
The following is a partition to modify some examples of real property.
This example modifies the sales range partitioned table sales_q1 MAXEXTENTS storage properties:
ALTER TABLE sales MODIFY PARTITION sales_q1
STORAGE (MAXEXTENTS 10);
In the following examples, in a range - hash partitioned table scubagear of partition ts1, all local index sub-partition is identified as UNUSABLE:
ALTER TABLE scubagear MODIFY PARTITION ts1 UNUSABLE LOCAL INDEXES;
For the interval partitioned table, the partition, or you can modify the scope of the existing real property partition interval.
To modify an existing hash partition properties
You can also use the ALTER TABLE ... MODIFY PARTITION statement to modify the properties of a hash partition. However, due to the physical properties of all of the individual hash partitions must be the same (except TABLESPACE), you are only limited to:
Assign a new area
The release of an unused area
Identifies a local index partition UNUSABLE
Reconstruction is identified as the local index partitions UNUSABLE
The following example rebuild the dept table and hash-partitioned P1 all relevant local index partitions UNUSABLE:
ALTER TABLE dept MODIFY PARTITION p1
REBUILD UNUSABLE LOCAL INDEXES;
To modify an existing sub-district property
Use the ALTER TABLE MODIFY SUBPARTITION clause, you can perform the same operations previously listed only in the specified table sub-partition-level composite partition. E.g:
ALTER TABLE emp MODIFY SUBPARTITION p3_s1
REBUILD UNUSABLE LOCAL INDEXES;
Modify existing index partition properties
Use the ALTER INDEX MODIFY PARTITION clause, you can modify the actual property index partitions or sub-partitions. Rules and modify the table partition properties very similar, but for the ALTER INDEX statement MODIFY PARTITION clause is different, there is no rebuilding unusable index partition clause, but combined index partitions or sub-partitions clause. In this case, the merger means the combined index fast to release their re-use.
You can also use the MODIFY PARTITION to allocate or free local index sub-partition, or identified as UNUSABLE. |
|
|
|