Partition Table Study Notes (2) -- partition management

Partition Table Study Notes (2) -- partition management main content: 1. Add new partition 2. split partition 3. Merge partition Merge 4. Move partition 5. Truncate partition 6. drop partition -------------------------------------------------------- 1. Add new partition with new partition 2 medium: (1) the boundary in the original partition is maxvalue or default. In this case, we need to drop the boundary partition, add the new partition, and then add the new partition. Or use split to split the border partition. (2) Non-boundary partition. In this case, you can directly add partitions. Example of adding a new partition using a boundary partition: (1) Partition table and index information: SQL> create table custaddr (id varchar2 (15 byte) not null, areacode varchar2 (4 byte )) partition by list (areacode) (partition t_list556 values ('000000') tablespace data01, partition p_other values (default) tablespace data01); the table has been created. SQL> create index ix_custaddr_id on custaddr (id) local (partition t_list556 tablespace data01, partition p_other tablespace data01); the index has been created. (2) insert several test data records: SQL> insert into custaddr values ('1', '123'); 1 row has been created. SQL> insert into custaddr values ('2', '000000'); 1 row has been created. SQL> insert into custaddr values ('3', '123'); 1 row has been created. SQL> commit; submitted completely. SQL> select * from custaddr; id area ------------- ---- 1 556 2 551 3 555 SQL> select * from custaddr partition (t_list556); id area ------------- ---- 1 556 SQL> (3) delete the default partition SQL> alter table custaddr drop partition p_other; the table has been changed. SQL> select table_name, partition_name from user_tab_partitions where table_name = 'custaddr '; table_name partition_name ---------------------------- convert CUSTADDR t_list556 (4) add a new partition SQL> alter table custaddr add partition t_list551 values ('20170101') tablespace data01; the table has been changed. SQL> select table_name, partition_name from user_tab_partitions where table_name = 'custaddr '; TABLE_NAME PARTITION_NAME -------------------------- CUSTADDR T_LIST556 CUSTADDR T_LIST551 (5) add the default partition SQL> alter table custaddr add partition p_other values (default) tablespace data01; the table has been changed. SQL> select table_name, partition_name from user_tab_partitions where table_name = 'custaddr '; TABLE_NAME PARTITION_NAME partition CUSTADDR T_LIST556 CUSTADDR T_LIST551 CUSTADDR P_OTHER (6) for local indexes, oracle automatically adds a local partition index. Verify the following: SQL> select owner, index_name, table_name, partitioning_type from region where index_name = 'ix _ custaddr_id '; owner index_name table_name region ---------------- icd region custaddr SQL> select index_owner, index_name, partition_name from dba_ind_partitions where index_name = 'ix _ custaddr_id '; index_owner index_name partition_name ---- -------------------------- Partition ------------------ icd ix_custaddr_id p_other icd ix_custaddr_id t_list551 icd ix_custaddr_id t_list556 partition index is automatically created. 2. split partition splitting in the previous section, we show that you can use the split method to add partitions. Here we use the split method to continue the above experiment. SQL> alter table custaddr split partition p_other values ('000000') into (partition t_list552 tablespace icd_service, partition p_other tablespace icd_service); the table has been changed. -- Pay attention to the red area. If it is of the Range type, use at and List to use Values. SQL> select table_name, partition_name from region where table_name = 'custaddr '; TABLE_NAME PARTITION_NAME partition into CUSTADDR T_LIST556 into T_LIST551 CUSTADDR T_LIST552 CUSTADDR P_OTHER SQL> select index_owner, index_name, partition_name from dba_ind_partitions where index_name = 'ix _ CUSTADDR_ID '; index_owner index_name partition_name Partition partition ------------------ icd ix_custaddr_id p_other icd ix_custaddr_id t_list551 icd ix_custaddr_id t_list552 icd ix_custaddr_id t_list556 Note: partition tables automatically maintain partial partition indexes. The global index is invalid and requires rebuild. 3. Merge partitions adjacent to the merge partition into a single partition. The bottom boundary of the new partition is the partition with a lower original boundary value, and the upper boundary is the partition with a higher original boundary value, the original local indexes will also be merged, and the global indexes will become invalid and need to be rebuilt. SQL> alter table custaddr merge partitions t_list552, p_other into partition p_other; the table has been changed. SQL> select index_owner, index_name, partition_name from region where index_name = 'ix _ CUSTADDR_ID '; index_owner index_name partition_name partition icd limit p_other icd limit t_list551 icd limit t_list556 SQL> select table_name, partition_name from user_tab_partitions where table_name = 'custaddr '; table _ Name partition_name partition ---------------------------- custaddr t_list556 custaddr t_list551 custaddr p_other 4. move partition SQL> alter table custaddr move partition P_OTHER tablespace system; table changed. SQL> alter table custaddr move partition P_OTHER tablespace DATA01; the table has been changed. Note: partition movement automatically maintains local partition indexes, and oracle does not automatically maintain global indexes. Therefore, we need to rebuild the partition indexes again. You can determine which indexes need to be rebuilt through dba_part_indexes and dba_ind_partitions. SQL> Select index_name, status From user_indexes Where table_name = 'custaddr '; INDEX_NAME STATUS certificate -------- IX_CUSTADDR_ID N/A certificate -------------------- whether the status is normal, the STATUS of the experiment is as follows: SQL> select index_owner, index_name, partition_name, STATUS from dba_ind_partitions where 2 index_name = 'ix _ CUSTADDR_ID '; INDEX_OWN ER INDEX_NAME PARTITION_NAME STATUS already existed -------- SYS IX_CUSTADDR_ID P_OTHER UNUSABLE -- another operation may be performed to delete the partition. After re-adding, SYS IX_CUSTADDR_ID T_LIST556 USABLE is normal. SQL> Select index_name, status From user_indexes Where table_name = 'custaddr '; INDEX_NAME STATUS certificate -------- IX_CUSTADDR_ID N/A partition 5, Truncate partition SQL> select * from CUSTADDR partition (T_LIST556 ); id area --------------- ---- 1 556 SQL> alter table custaddr truncate partition (T_LIST556 ); The table is truncated. SQL> select * from custaddr partition (T_LIST556); unselected rows: The Truncate operation is fast compared to the delete operation, and the batch data loading of a large amount of data in the data warehouse may be used; partition truncation will also automatically maintain the local partition index and make the global index unusable. re-build 6. Drop partition SQL> alter table custaddr drop partition T_LIST551; the table has been changed. SQL> select table_name, partition_name from user_tab_partitions where table_name = 'custaddr '; TABLE_NAME PARTITION_NAME partition CUSTADDR T_LIST556 CUSTADDR P_OTHER automatically maintains local partition indexes, and the global index unusable must be rebuilt