Notes on Oracle Partition Table Management

Management notes for Oracle partition tables (only for common tables, that is, partition management for heap tables. IOT and cluster table are no longer discussed)

1. add partition)
Syntax: alter table xxx add partition...
It should be noted that if the maxvalue or default partition add partition exists in the partition, an error is reported, and the split

For example:

Alter table t_range add partition p5 values less than (50) [tablespace users];
-- 50 must be greater than all values in the previous Partition
Alter table t_list add partition p5 values (7,8, 9) [tablespace users];
-- 7, 8, and 9 cannot appear in the previous partition.
Alter table t_hash add partition [p5] [tablespace users];

Add sub-partitions:
Alter table xxx modify partition p1 add subpartition...
For example, add a RANGE-HASH subpartition.
Alter table diving modify partition locations_us
Add subpartition us_locs5 TABLESPACE us1;

Adding partitions in Range and list does not affect indexes (including global and local). Adding partitions in HASH will re-allocate data and generate IO, if the update indexes option is not specified, the index with data movement will be unusable and need to be re-compiled.
Of course, the impact on the index is that if there is data in the table, no data will certainly not affect the index.

2. merge partitions (coalesce partition)
Alter table xxx coalesce partion [update indexes];
Alter table xxx modify partition p1 coalesce subpartition;
Only applicable to HASH partitions or subpartitions. A Merge operation reduces one partition (at least one partition can be reduced), data is re-allocated, and IO is generated, the index with data movement fails (if update indexes is not specified ).

3. drop partition)
Alter table xxx drop partition ppp;
Delete A subpartition:
Alter table xxx drop subpartition ppp;
This function does not support hash. You must also note that deleting a partition will also delete the data in the partition.
Similarly, if you do not specify update indexes, this operation will cause GLOBAL index failure, but LOCAL will not. Because the corresponding LOCAL index partition is also deleted, the LOCAL of other partitions will not be affected.

4. exchange partition)
Alter table tb1 exchange partition/subpartition p1 with table tb2;
It is said that the data dictionary is changed, so the speed is faster.
Partitions can be exchanged with non-partition tables, subpartitions can be exchanged with non-partition tables, and composite partitions can be exchanged with partition tables.
For example:
Exchange of combined partitions and partition tables:
Create table t1 (I NUMBER, j NUMBER)
Partition by hash (I)
(PARTITION p1, PARTITION p2 );

Create table t2 (I NUMBER, j NUMBER)
Partition by range (j)
Subpartition by hash (I)
(PARTITION p1 values less than (10)
SUBPARTITION t2_pls1
SUBPARTITION t2_pls2,
PARTITION p2 values less than (20)
SUBPARTITION t2_p2s1
SUBPARTITION t2_p2s2 ));

Alter table t2 exchange partition p1 with table t1
With validation;

If you specify with validation (default), the exchanged data will be checked to see if it meets the Partition Rules, without validation ignores Legal checks (for example, records with ID = 12 can be exchanged to the partitions with id values less than (10). However, if the table has a primary key or unique constraint, the specified without validation will be ignored.
Similarly, if you do not specify update indexes, the GLOBAL index will become invalid and need to be re-compiled.

5. merge partitions (merge partitions)
Alter table xxx merge partitions/subpartitions p1, p2 into partiton/subpartition p3 [TABLESPACE tablespace_name];
HASH is not applicable because it has COALESCE.
Table partitions must be adjacent.
Like COALESCE, IO is generated. If the data volume is large, IO is also quite large.
You can also use update indexes to avoid index failure.

6. Modify LIST partition-ADD VALUES
Alter table xxx modify partition/subpartition p1 add values (7,9 );
Note that the added VALUES cannot exist in the VALUES of other partition columns, or have corresponding VALUES in the DEFAULT partition (if any.
Index is not affected

7. Modify LIST partition-DROP VALUES
Alter table xxx modify partition/subpartition p1 drop values (8, 9 );
Similarly, the Deleted values cannot have a record.
Index is not affected

8. split partitions)
Function is opposite to merge partitions. It is usually used to split the MAXVALUE/DEFAULT partition.
Range partition:
Alter table xxx split partition/subpartition p1 at (15) into (partition/subpartition p1_new1, partition/subpartition p1_new2 );
List partition:
Alter table xxx split partition/subpartition p1 values (15,16) into (partition/subpartition p1_new1, partition/subpartition p1_new2 );
Records that match the new value definitions in the original partition will be stored in the first partition, and other records will be stored in the second partition. Of course, attributes can be specified after the new partition, such as TABLESPACE.
HASH partitions are not applicable.
Will generate IO
Similarly, you can use update indexes to avoid index failure.

9. truncate partition)
Similar to truncate table, data in the Shard is truncated.
Alter table xxx truncate partition/subpartition p1;
Similarly, you can use update indexes to avoid index failure.

10. move partition)
Alter table xxx move partition/subpartition p1 ...;
Some functions, such as changing the partition Table space, cannot be done by modify partition. You can use move partition to do this.
Use the move partition clause of the alter table statement:
• Re-cluster data and reduce fragmentation
• Move a partition to another tablespace
• Modify create-time attributes
• Store the data in compressed format using table compression

For example:
Alter table parts move partition depot2
TABLESPACE ts094 nologging compress;
(If compress, affects only future storage, but not existing data .)

Similarly, you can use update indexes to avoid index failure.

11. rename partition)
Alter table xxx rename partition/subpartition p1 to p1_new;
It is similar to renaming a table.

12. modify default attributes)
Alter table xxx modify default attributes...
Alter table xxx modify default attributes for partition p1...
It only affects the partitions that will be added later. It applies to all partitions, where hash partitions can only modify the tablespace attributes.
For example:
Alter table xxx modify default attributes tablespace users;

13. Modify subpartition template attributes (set subpartition template)
Alter table xxx set subpartition template (...);
Only subsequent subpartitions are affected. The attributes of the current subpartition are not changed.
For example:
Alter table xxx set subpartition template
(Partition p1 tablespace tbs_1,
Partition p2 tablespace tbs_2 );
To cancel the subpartition template:
Alter table xxx set subpartition template ();