Partitioning interactive technology for Oracle partitioned tables to realize fast data transfer

There is a need to transfer a record of a business table before a point in time to its history table. If the current business table is not based on the partition table settings for this business point in time, it can only insert and delete operations. This method of transferring data is very, very low base. Often occurs in the primary database administrator and developer programs. Not that this method is not good, for the transfer of the number of records in a few Baisi, and the transfer frequency is high, transfer time point random situation, this method is very useful. But this approach is inefficient if the transfer of data is measured in millions of dollars at a time.

Therefore, in the development of Oracle database, the transfer of this kind of large data can be realized by using partitioned table Exchange technology. Even if you are transferring hundreds of millions of or even billions of of the data, the transfer time is still millisecond. The overall process of this approach is this:

First, you need to modify the current table to a partitioned table, it is critical to find the partition field; second, the index of the partitioned table is indexed in cost, the global index is not, the reason is introduced; again, create a corresponding temporary partitioned table, the table structure is the same as this; last use ALTER TABLE Table_ Name Exchange partition Partition_name with table Table_name_exchange an operation that swaps the actual physical storage space segments of the data owned by the table partition, which is a pointer-level operation.

This completes the rapid transfer of the table partition data.

Do a test on this procedure.

(Miki West Tour @mikixiyou original link: http://mikixiyou.iteye.com/blog/1773659)

The first step is to prepare the environment

Create a test table sale, and its partition field is Dotime, partitioned by quarter.

CREATE TABLE SALE  
(  
  dotime          DATE                          DEFAULT sysdate,  
  billid          VARCHAR2 (BYTE) not             NULL,  
  Fromarrear      Number (16,4)                  DEFAULT 0  
)  
PARTITION by RANGE (dotime)  
(   
  PARTITION py11q3 VALUES less THAN (to_ DATE (' 2011-10-01 00:00:00 ', ' syyyy-mm-dd 

HH24:MI:SS ', ' Nls_calendar=gregorian '))  
    LOGGING,   
  PARTITION py11q4 VALUES Less THAN (to_date (' 2012-01-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), 

' nls_calendar= Gregorian '))  
    LOGGING,   
  PARTITION py12q1 VALUES less THAN (to_date (' 2012-04-01 00:00:00 ', 

' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')  
    LOGGING,   
  PARTITION py12q2 VALUES less THAN (' 2012-07-01 00:00:00 ', ' syyyy-mm-dd 

HH24:MI:SS ', ' Nls_calendar=gregorian ')  
    LOGGING, PARTITION p_max   
  VALUES Less THAN (MAXVALUE)  
    LOGGING  
)  
;

Then create an exchange table, the table's field structure and partition table exactly the same.

CREATE TABLE Sale_exchange  
(  
  dotime          DATE                          DEFAULT sysdate,  
  billid          VARCHAR2 (BYTE)             not NULL,  
  fromarrear number      (16,4)                  DEFAULT 0  
);

Note that the primary key on the partition table does not have a global unique index and is changed to a local index on sale (billid,dotime), which also guarantees consistency of the data. The original primary key field billid must be put in front to prevent the original based on the billID Direct query operation of the performance drop too much.

Create unique index Pk_sale on SALE (billid,dotime) local;

The local partition index was created.

Second step,

Check the data records. Let's say we want to transfer the records in the PY11Q3 partition.

Select COUNT (*) from SALE partition (PY11Q3);

Select COUNT (*) from Sale_exchange;