Partition Interaction Technology for Oracle partition tables for fast data transfer

The partition Interaction Technology of Oracle Partitioned Tables enables rapid data transfer. This requires that records from a business table before a certain time point be transferred to its historical table. If the current business table is not set based on the partition table at this business time point, you can only perform insert and delete operations. This method of transferring data is very low-Basic. It often appears in the programs of junior database administrators and developers. This is not to say that this method is not good. This method is quite useful when the number of transferred records is dozens or hundreds, and the transfer frequency is high and the transfer time is random. However, if the transferred data volume is millions at a time, this method will become inefficient. Therefore, in Oracle database development, the transfer of such big data can be achieved using the Partition Table exchange technology. 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. This completes the fast data transfer in the partition of the table. Www.2cto.com will perform a test on this operation process. Step 1: Prepare the environment to create a test table SALE. Its partition field is DOTIME, Which is partitioned by quarter. SQL code CREATE TABLE SALE (DOTIME DATE DEFAULT sysdate, BILLID VARCHAR2 (20 BYTE) NOT NULL, FROMARREAR NUMBER (16,4) DEFAULT 0) PARTITION BY RANGE (DOTIME) (PARTITION PY11Q3 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN') LOGGING, PARTITION PY11Q4 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIA N ') LOGGING, PARTITION PY12Q1 values less than (TO_DATE ('00:00:00', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN ')) LOGGING, PARTITION PY12Q2 values less than (TO_DATE ('2012-07-01 00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN') LOGGING, PARTITION P_MAX values less than (MAXVALUE) LOGGING); Create an exchange table. The field structure of the table is exactly the same as that of the PARTITION table. SQL code create table SALE_exchange (DOTIME DATE DEFAULT sysdate, BILLID VARCHAR2 (20 BYTE) NOT NULL, FROMARREAR NUMBER (16,4) DEFAULT 0); note, the globally unique index to which the primary key of a partitioned table belongs should not be changed to the local index on SALE (BILLID, DOTIME), which can also ensure data consistency. The original primary key field billid must be placed in front to prevent the original direct query operation based on billid from performance degradation too much. Create unique index PK_SALE on SALE (BILLID, DOTIME) local; the local partition index is created. Step 2: Check the data records. Suppose we want to transfer the records in the PY11Q3 partition. Select count (*) from SALE partition (PY11Q3); select count (*) from SALE_exchange; Step 3: Transfer Data alter table SALE exchange partition PY11Q3 with table SALE_exchange; a command, by replacing the spatial pointers of the partition and table segments, the business data of the partition table is transferred. Step 4: Check the index status partition table. If the partition changes, such as exchange, move, and split, the local index partition on the partition will become invalid, at the same time, the global index on the entire partition table will also become invalid. This is why I first mentioned that the global index needs to be revoked. Imagine if a global index exists at this time, it will become invalid, which will affect other business operations on the partition table. In any case, you must check the index status. Www.2cto.com SQL code select index_name, partition_name, status from user_ind_partitions where status = 'unusable' union all select index_name, ''as partition_name, status from user_indexes where status = 'unusable'; here, the index partition of the local partition index must be invalid. The indexes in the original exchange table will also become invalid. They all need to be rebuilt. Rebuilding the partition index: alter index PK_SALE rebuild partition PY11Q3; alter index PK_SALE_EXCHANGE rebuild; after such an operation, the historical data is removed from the current business table, but it is in a temporary isolated table. In real business, such data needs to move the history table. Of course, if you have a conversion operation, you can continue to use the insert method for transfer. If not, you can also use the partition exchange technology to exchange this data to the History Table. In short, Oracle Partition Table technology is often recommended for big data table operations. Many of its features help us develop efficient applications.