Oracle partition Switching

In Oracle database development, you can use the Partition Table exchange technology to transfer this type of big data. Even if you transfer hundreds of millions or even billions of data at a time, the transfer time is still within milliseconds. The general process of this method is as follows: first, you need to change the current table to a partition table and it is critical to find the partition field. Second, the index of this partition table is created as a local index, the global index is not needed. The reason is described later. Create a temporary non-partition table with the same structure as this one. Use alter table table_name exchange partition Partition_name with table table_name_exchange, swap the actual physical storage space segments of the data owned by the table partition, which is a pointer-level operation.
Example:

[SYS @ orcl #09-3-10] SQL> create table t (id number primary key, time date );

The table has been created.

[SYS @ orcl #09-3-10] SQL> insert into t select rownum, created from dba_objects;

You have created 50362 rows.

[SYS @ orcl #09-3-10] SQL> create table t_n (id number primary key, time date)
2 partition by range (time)
3 (partition p1 values less than (maxvalue ));

The table has been created.

[SYS @ orcl #09-3-10] SQL> alter table t_n exchange partition p1 with table t;

The table has been changed.

[SYS @ orcl #09-3-10] SQL> select count (*) from t_n;

COUNT (*)
----------
50362

[SYS @ orcl #09-3-10] SQL> alter table t_n split partition p1 at (to_date ('2017-4-1 ', 'yyyy-mm-dd ')
2) into (partition p1, partition p2 );

The table has been changed.

[SYS @ orcl #09-3-10] SQL> select count (*) from t_n;

COUNT (*)
----------
50362

[SYS @ orcl #09-3-10] SQL> select count (*) from t;

COUNT (*)
----------
0

Keywords:

Note:

The table structures of the two tables involved in the exchange must be consistent unless the with validation clause is attached;

If the data is exchanged from a non-partition table to a partition table, the data in the non-partition table must comply with the rules of the specified partition in the Partition Table, unless the without validation clause is attached;

If you exchange data from a partition table to a partition table, the data in the exchanged partition must comply with the Partition Rules, unless the without validation clause is attached;

Global indexes or global index partitions that involve data changes are set to unusable unless the update indexes clause is attached.

Tip:

Once the without validation clause is attached, it indicates that the data validity is no longer verified. Therefore, be careful when specifying the clause.

For example:

JSSWEB> insert into t_partition_range_tmp values (8, 'G ');

One row has been created.

JSSWEB> alter tablet_partition_range exchange partition t_range_p2

2 with tablet_partition_range_tmp without validation;

The table has been changed.

JSSWEB> insert into t_partition_range_tmp values (8, 'G ');

One row has been created.

JSSWEB> alter table t_partition_range exchange partition t_range_p2

2 with tablet_partition_range_tmp without validation;

The table has been changed.

JSSWEB> select * from t_partition_range partition (t_range_p2 );

ID NAME

------------------------------------------------------------

11

12 B

13 c

8 GB

Although the newly inserted record does not conform to the range value of the t_range_p2 partition, after without validation is specified, the data is still converted successfully.