Oracle Exchange Partitioning method and considerations

1. Check that the partition has no data

Select /*  * /Count (1 from zj2_gridlevsta_cell_all_571 partition (sys_p16805) A--where   a.p_day=to_date ('20171231','yyyymmdd') ;

2. If the partition table has a primary key, increment the column, delete

ALTER TABLE zj2_gridlevsta_all_571 drop primary key;drop index Pk_zj2_gridlevsta_all_571_oid;alter table Zj2_gridlevsta _cell_all_571 Modify OID drop identity;

3. Create table, guarantee index, non-null constraint is consistent with partition table

CREATE TABLE Zj2_gridlevsta_cell_all_571_b as SELECT * from zj2_gridlevsta_cell_all_571 where 1=2;alter table Zj2_gridlev Sta_cell_all_571_b Modify OID not null;create index b_eci_index on Zj2_gridlevsta_cell_all_571_b (ECI);

4. Partitioned tables and non-partitioned table interchange partitions

ALTER TABLE zj2_gridlevsta_cell_all_571 Exchange partition  sys_p16805 with  table zj2_gridlevsta_cell_all_571 _b;

　　

Note: The partition table to be swapped cannot contain the self-increment column, the primary key, or the partition may not be swapped

Select/*+ Parallel (a) */count (1) From this method can be multi-process to view the data at the same time, more efficient than the normal viewing effect, but not in the program to write, because it will monopolize the process, more people can see at the same time than the ordinary methods
It's going to be slow.

Oracle Exchange Partitioning method and considerations